Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Location
    Buenos Aires, Argentina
    Posts
    28

    Unanswered: Getting the SQL that generates the view

    Hi Guys!
    I'm pretty new at this. I have an Oracle running on an AIX server, and I'm using SQLPLUS to query the tables.
    I have one view from which I would like to see the SQL code that is generating it. How can I do that?
    Thank you,
    panchopp.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SQL> DESC ALL_VIEWS
    and then figure out the query to answer your question
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2004
    Location
    Buenos Aires, Argentina
    Posts
    28

    Thumbs up

    Quote Originally Posted by anacedent
    SQL> DESC ALL_VIEWS
    and then figure out the query to answer your question
    That's exactly what I needed. Thank you very much!

  4. #4
    Join Date
    Aug 2004
    Location
    Buenos Aires, Argentina
    Posts
    28
    Quote Originally Posted by panchopp
    That's exactly what I needed. Thank you very much!
    Found out one further issue.
    The ALL_VIEWS table has the following structure:


    Name Null? Type
    ------------------------- -------- --------------
    OWNER NOT NULL VARCHAR2(30)
    VIEW_NAME NOT NULL VARCHAR2(30)
    TEXT_LENGTH NUMBER
    TEXT LONG
    TYPE_TEXT_LENGTH NUMBER
    TYPE_TEXT VARCHAR2(4000)
    OID_TEXT_LENGTH NUMBER
    OID_TEXT VARCHAR2(4000)
    VIEW_TYPE_OWNER VARCHAR2(30)
    VIEW_TYPE VARCHAR2(30)



    I found that the SQL text to be in the TEXT field (TYPE_TEXT is empty, as well as OID_TEXT). But it's not complete, it is cut in the middle:



    SELECT "SESSION_KEY","FINAL_CONNECT","HOST","PIN","NODE", "PORT","SESSION_END","C



    The text_length field has 689.
    Does anyone know why this is so, and from where can I get the full SQL text?
    Thank you!

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Issue a SET LONG 32000 before running the query. As an alternative you can also use dbms_metadata.get_ddl() package function to retrieve the source code

    Details can be found here:
    http://download-west.oracle.com/docs...d2.htm#1024701

  6. #6
    Join Date
    Aug 2004
    Location
    Buenos Aires, Argentina
    Posts
    28
    Thanks again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •