1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to view codes of created procedure/tigger/function

Discussion in 'SQL PL/SQL' started by Puru, Nov 15, 2010.

  1. Puru

    Puru Forum Advisor

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    90
    Dear All,

    I have created a procedure, now i want view the codes of that. Kindly let me know how to do this
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
  3. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    You can either go for using DBMS_METADATA.GET_DDL, but the return output is of CLOB type, which will be shown as a fraction in SQL* Plus.
    Secondly, you can query TEXT column USER_SOURCE dictionary view for the object NAME.

    SELECT LINE, TEXT
    FROM USER_SOURCE
    WHERE NAME='PROC NAME'
    AND TYPE = 'PROCEDURE'
    ORDER BY LINE
     
    kiran.marla likes this.
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Which is nice but you can return broken lines: dbms_metadata is the preferred method and the output CAN be formatted very nicely so that the entire source is returned if you use the code listed below:

    Code (SQL):
     
    BEGIN
    dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE );
    dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );
    END;
    /
    SET linesize 150
    SET long 90000
    SET longchunksize 9000
    SET pagesize 1000
    SELECT dbms_metadata.get_ddl(UPPER('&type'), UPPER('&name'), UPPER('&own')) FROM dual;

     
    Investigate all of the options a package offers.
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India