ansaurus

Question

Dump the body of a function or procedure in sqlplus

Answer 1

+1 A:

select
    text
from
    user_source
where
    type = 'PROCEDURE'
and
    name='YOURPROCEDURENAME'
order by
    line;

dacracot 2009-12-11 21:54:11

Likewise type='FUNCTION' and name='YOURFUNCTIONNAME'.

dacracot 2009-12-11 21:55:05

We have several different schema set up. Do I to do anything special to deal with those?

David Oneill 2009-12-11 21:56:37

You can either log into the specific schema, and user_source will only contain that schema's source. Or you can log in as a DBA and the dba_source table will also have an owner column which you can include in your where clause to specify which schema you are interested in.

dacracot 2009-12-11 21:59:19

Actually, you want "select text". The LINE column has the line number.

Pop 2009-12-12 01:20:20

Edited solution to select the right column

APC 2009-12-12 17:11:52

Lol, thanks for the edit.

dacracot 2009-12-14 16:33:29

Answer 2

+2 A:

Use:

SELECT us.name,
       us.type,
       us.text
  FROM USER_SOURCE us
 WHERE us.type IN ('PROCEDURE', 'FUNCTION')
ORDER BY name, line

OMG Ponies 2009-12-11 21:56:09

Answer 3

+2 A:

Another solution is to use the *dbms_metadata* api

set line 200
set long 10000
select dbms_metadata.ddl('PACKAGE','Package Name') from dual;

You can use this for all metadata including tables, indexes and constraints.

Dinesh Bhat 2009-12-11 23:09:20

Upvoting because those 'set line/set long' will be needed for the other mechanisms as well I think.

monojohnny 2010-01-05 21:01:50

ansaurus

tags:

views:

answers:

Dump the body of a function or procedure in sqlplus

related questions