anyway to view contents(SQLs) created inside a Stored Procedure?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: anyway to view contents(SQLs) created inside a Stored Procedure?

  1. #1
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456
    is there anyway that i can see what are the procedure contents already created within a Stored Procedure?

    thanks in advance.

  2. #2
    Join Date
    Jan 2001
    Location
    Vancouver, Canada
    Posts
    87
    Please post more information if you want us to help you with this problem.

    Cheers!!!

    oraclemz@yahoo.com

  3. #3
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456
    ok situation is as follow:

    I run an sql script to create a procedure to perform a simple task say updating a table..
    so let's say i lost this script and another person wish to know what this procedure that was
    already created by me is doing...is there anyway that he can query the database to
    see what scripts that the procedure's contents as in the SQL statements?

    can help?

    ngwh,
    Singapore.

  4. #4
    Join Date
    Aug 2001
    Posts
    111
    have a look at v$source or dba_source

    Be careful with the format of the text column

    Have Fun
    Performance... Push the envelope!

  5. #5
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    select text from dba_source where name='MY_PROCEDURE'
    order by line
    /

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  6. #6
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456
    hey thanks! but the contents of the text wasn't wat i really expecting...
    wonder how can i format it into "readable" format ?? any oracle tools to do that?

    i got this script from OTN that list the contains of packages/procedures:

    SET PAGESIZE 0
    SET FEEDBACK OFF
    SET LINESIZE 2000
    SET TRIMSPOOL ON
    SET VERIFY OFF
    SET TERMOUT OFF
    SPOOL &1
    SELECT text FROM
    (SELECT name, type, line, text FROM user_source
    UNION
    SELECT DISTINCT name, type, 0,
    'CREATE OR REPLACE ' FROM user_source
    UNION
    SELECT DISTINCT name, type, 1000000000, '/' FROM user_source)
    ORDER BY name, type, line;
    /
    SPOOL OFF

    ....but doesn't perform wat i need.. :( help?

  7. #7
    Join Date
    Aug 2001
    Posts
    111
    here is a script i downloaded a while ago

    Orginal by Mark Lang, 1998
    modified slightly

    set heading off
    set long 32000
    set linesize 255
    set trimspool on

    column text format a255

    spool &filename
    select
    decode(line, 1, chr(10)||'create or replace '||chr(10), '')||text
    text
    from sys.dba_source s
    where s.owner like &owner and s.name like &name
    order by decode(s.type, 'PROCEDURE', 1, 'FUNCTION', 2, 'PACKAGE', 3, 4),
    s.owner, s.name, line
    ;
    select '/' from dual;
    spool off


    Performance... Push the envelope!

  8. #8
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456
    roobaron,


    some amendments to ur scripts...u shld include to SINGLE quotation as follow:
    ----
    where s.owner like '&owner' and s.name like '&name'
    ----

    Hey thanks!

Posting Permissions

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



Click Here to Expand Forum to Full Width