Skip to Main Content
  • Questions

  • Generate report for recreating Procedures

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Umesh.

Asked: July 19, 2000 - 4:46 pm UTC

Last updated: April 17, 2009 - 10:49 am UTC

Version: ORACLE 8.1.5

Viewed 1000+ times

You Asked

I want to recreate all procedures in a database in a differnet database on a different machine and compile them . I am trying to use this script but, I am not succesful and getting a pile of error messages. Where could be the problem and how to fix it.
The scriot was taken from Ault's book.

Thanks.

Umesh
set termout off verify off feedback off lines 80 pages 0 heading off

set recsep off space 0

column text format a79 word_wrap

column line noprint

select 'create or replace '||text,line

from

dba_source

where

owner = upper('&&1') and

type = upper('&&2') and

name = upper('&&3') and

line = 1;

select text,line

from

dba_OBJECTS s1,

dba_source s2

where

s1.OBJECT_type = upper('&&2') and

s1.owner = upper('&&1') and

s1.object_name = upper('&&3') and

s1.OBJECT_type = s2.type and

s1.owner = s2.owner and

s1.OBJECT_NAME = s2.name and

line > 1

order by

2;

select '/' from dual;



Example script to call fprc_rct.sql for all objects of a specific type for a specific user:

REM

REM NAME : RUN_FPRC.SQL

REM FUNCTION : Generate and execute the exe_fprc.sql procedure

REM USE : Document the procedures and packages and functions

REM for a user or users

REM Limitations : Must have access to dba_source and dba_objects.

rem The FPRC_RCT.SQL procedure must be in same directory

REM

set termout on

column dbname new_value db noprint

pause Use % for a wildcard - Press enter to continue

accept owner prompt 'Enter object owner:'

accept type prompt 'Enter object type:'

accept name prompt 'Enter object name:'

prompt Working....

set echo off heading off verify off feedback off

select value dbname from v$parameter where name='db_name';

spool rep_out\&db\do_fprc.sql

select unique('start fprc_rct '||a.owner||' '||'"'||a.type||'"'||' '||a.name)

from

dba_source a, dba_objects b

where

a.owner like upper('&owner') and

a.type like upper('&type') and

a.name like upper('&name')

and a.owner=b.owner and

a.type=b.object_type and

a.name=b.object_name;

spool off

set termout off

spool rep_out\&db\exe_fprc.sql

start rep_out\&db\do_fprc.sql

spool off

undef owner

undef type

undef name

clear columns

set echo on heading on verify on feedback on





and Tom said...

Well, instead of me debugging that, how about if I tell you how I do it.

I use a script called "getcode" that is simply:

---------------- getcode.sql ----------------------------
set feedback off
set heading off
set termout off
set linesize 1000
set trimspool on
set verify off
spool &1..sql
prompt set define off
select decode( type||'-'||to_char(line,'fm99999'),
'PACKAGE BODY-1', '/'||chr(10),
null) ||
decode(line,1,'create or replace ', '' ) ||
text text
from user_source
where name = upper('&&1')
order by type, line;
prompt /
prompt set define on
spool off
set feedback on
set heading on
set termout on
set linesize 100
------------------- eof --------------------------------

it extracts one procedure, function or package to a file. Thats it. If you wanted to get all of the procedures in a schema extracted to the current working directory, you would run a script:


--------------- getallcode ---------------------------
set termout off
set heading off
set feedback off
set linesize 50
spool xtmpx.sql
select '@getcode ' || object_name
from user_objects
where object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE' )
/
spool off
spool getallcode_INSTALL.sql
select '@' || object_name
from user_objects
where object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE' )
/
spool off
set heading on
set feedback on
set linesize 130
set termout on
@xtmpx.sql
---------------- eof ---------------------------------

You can see how to filter on that one by adding to the where clause if you want.

Just run @getallcode_INSTALL to run all of the scripts...




Rating

  (15 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

really helpful

Arun Kumar, April 11, 2002 - 2:31 pm UTC

tom U are great. Simple effective approach.

just what I needed

Damien Cobbs, June 19, 2002 - 3:46 pm UTC

Tom, this is EXACTLY what I was looking for. Thanks.

getcode

reddy, February 13, 2003 - 3:23 pm UTC

I am trying to use your code ,it gives an err ,unable to open the file at the last .sql when executed.
spool off
set heading on
set feedback on
set linesize 130
set termout on
@xtmpx.sql ...............

SP2-0310: unable to open file "getcode.sql"

Tom Kyte
February 13, 2003 - 5:29 pm UTC 

well, you are not getting the error when executing the last line of the script.

You see, my script is named getcode - you obviously do NOT have a script called getcode.

I edited a file called getcode.sql.  In that i put the above statements.  That way -- I can simply

SQL> @getcode <name>


the script itself doesn't reference getcode.sql, it IS getcode.sql.  name it such.

What about extracting pieces of the source

David, March 31, 2004 - 8:13 am UTC

Hi, Tom.

Our requirement is to extract just the UPDATE statements from a package body. The package is a series of updates wrapped in pl/sql that logs start and end times and captures the sql%rowcounts. For example:

------------------------------------------------------
begin -- step 4
gv_cur_step := gv_cur_step + 1;
scrubs.set_step(<args>);
scrubs.start_step(<args>);

update <table>
set <..>
where <where clause>;

gv_rows_updated := sql%rowcount;
scrubs.write_transaction(<args>);
scrubs.end_step(<args>);

end; -- step 4
----------------------------------------------------------

I need to loop through the package body in dba_source and extract each instance of "update.......;". The goal is to get each UPDATE statement in a single row (1 row for each statement) so they can be exported into a single Excel cell (1 cell per UPDATE). Using windows (Word, notepad, textpad) and cutting and pasting isn't efficient because the newlines inside the statements cause each line to be dumped into a different row.

I think I can set up a nested loop from 1 to max(line) and build a string appending the source starting at each "update" and ending at each ";", but there has to be a better way. We could also dump the whole package body into a text file and use grep/sed, but my experience with those tools is limited.

Thanks as always.

Tom Kyte
March 31, 2004 - 9:43 am UTC

analytics never cease to amaze me :)

this gets some false positives, but the concept is there -- you can refine it as needed:

ask_tom@ASKUS> column line format 9999
ask_tom@ASKUS> column name format a15
ask_tom@ASKUS> column text format a90
ask_tom@ASKUS> column marker format a6
ask_tom@ASKUS> set linesize 200
ask_tom@ASKUS>
ask_tom@ASKUS> select max_rn, name, line, text
2 from (
3 select name, line, replace( text, chr(10), ' ' ) text, max_rn,
4 case when first_value(upper(text)) over (partition by name, max_rn order by line) like '%UPDATE %' then 'X' end x
5 from (
6 select name, line, text, rn,
7 max(rn) over (partition by name order by line) max_rn
8 from (
9 select name, text, line,
10 case when upper(text) like '%UPDATE %' OR
11 trim( rtrim(lag(text) over (partition by name order by line),chr(10)) ) like '%;'
12 then row_number() over (partition by name order by line) end rn
13 from user_source
14 where type in ( 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION' )
15 order by name, line
16 )
17 )
18 )
19 where x = 'X'
20 /

MAX_RN NAME LINE TEXT
---------- --------------- ----- ------------------------------------------------------------------------------------------
41 ASK_TOM_PKG 41 <br>Please update your bookmarks...
41 ASK_TOM_PKG 42 </BODY>
41 ASK_TOM_PKG 43 </HTML>
41 ASK_TOM_PKG 44 ');
4 P 4 update t set x = x;
245 WWC_ASK_API 245 update wwc_ask_application_controls$
245 WWC_ASK_API 246 set enabled = 'Y'
245 WWC_ASK_API 247 where application_code = 'NONEW';
337 WWC_ASK_API 337 update wwc_ask_submitted_questions$
337 WWC_ASK_API 338 set email = p_email,
337 WWC_ASK_API 339 fname = p_fname,
337 WWC_ASK_API 340 lname = p_lname,
337 WWC_ASK_API 341 location = p_location,
337 WWC_ASK_API 342 title = p_job_title,
337 WWC_ASK_API 343 org = p_org,
337 WWC_ASK_API 344 category = p_category,
337 WWC_ASK_API 345 version = p_version,
337 WWC_ASK_API 346 years_exp = p_years,
337 WWC_ASK_API 347 subject = p_subject,
337 WWC_ASK_API 348 remote_addr = l_remote_addr,
337 WWC_ASK_API 349 http_user_agent = l_http_user_agent,
337 WWC_ASK_API 350 http_referer = l_http_referer,
337 WWC_ASK_API 351 status = decode( status, g_NEWNEVERREAD, g_NEWNEVERREAD, g_READAWAITINGINFO, g_
READMOREINFOSUPPLIED ),

337 WWC_ASK_API 352 question = empty_clob()
337 WWC_ASK_API 353 where id = p_id
337 WWC_ASK_API 354 and status in (g_NEWNEVERREAD, g_READAWAITINGINFO )
337 WWC_ASK_API 355 returning question into l_question;


and if we add a user defined aggregate:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:15637744429336 <code>

we can:

ask_tom@ASKUS> select name, up_date
2 from (
3 select max_rn, name, line,
4 stragg( trim(text) || ' ') over (partition by name, max_rn order by line) up_date,
5 last_value(line) over (partition by name, max_rn) last_line
6 from (
7 select name, line, replace( text, chr(10), ' ' ) text, max_rn,
8 case when first_value(upper(text)) over (partition by name, max_rn order by line) like '%UPDATE %' then 'X' end x
9 from (
10 select name, line, text, rn,
11 max(rn) over (partition by name order by line) max_rn
12 from (
13 select name, text, line,
14 case when upper(text) like '%UPDATE %' OR
15 trim( rtrim(lag(text) over (partition by name order by line),chr(10)) ) like '%;'
16 then row_number() over (partition by name order by line) end rn
17 from user_source
18 where type in ( 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION' )
19 order by name, line
20 )
21 )
22 )
23 where x = 'X'
24 )
25 where line = last_line
26 /

NAME
---------------
UP_DATE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ASK_TOM_PKG
<br>Please update your bookmarks... ,</BODY> ,</HTML> ,');

P
update t set x = x;

WWC_ASK_API
update wwc_ask_application_controls$ ,set enabled = 'Y' ,where application_code = 'NONEW';

WWC_ASK_API
update wwc_ask_submitted_questions$ ,set email = p_email, ,fname = p_fname, ,lname = p_lname, ,location = p_location, ,title = p_job_title, ,org = p_org, ,category = p_category, ,version = p_version,
,years_exp = p_years, ,subject = p_subject, ,remote_addr = l_remote_addr, ,http_user_agent = l_http_user_agent, ,http_referer = l_http_referer, ,status = decode( status, g_NEWNEVERREAD, g_NEWNEVERREAD
, g_READAWAITINGINFO, g_READMOREINFOSUPPLIED ), ,question = empty_clob() ,where id = p_id ,and status in (g_NEWNEVERREAD, g_READAWAITINGINFO ) ,returning question into l_question;
.....


I guess I should have specified the version

David, March 31, 2004 - 10:04 am UTC

My apologies, we're running 8.0.6.

We have a 9i box (and this requirement is part of a 9i upgrade), so I can move this source code over to the 9i box and try your solution.

Without much effort, do you have a solution that would work on 8.0.6?

Thanks, Tom.

Tom Kyte
March 31, 2004 - 10:39 am UTC

doh -- nope, it would be procedural code in 8.0 - pretty each, a big cursor for loop that looked for %UPDATE % and then utl_file'ed the data until you found a ';' (utl_file so you can write a single line of text upto 32k)

Is there a way to know this..

Ajeet, June 07, 2004 - 7:44 am UTC

Tom,
I am working on a re-architecture project -- so old tables are being re-designed and so the number of packages will need to be changed too.I want to get a list of all tables being used (referred by) in a procedure/function and then the package in which I have these procedures and fucntions are written.
so something like this..

Package body -> Procedure 1 -> Tabl1
Package body -> Procedure 2 -> Tabl2
Package body -> procedure 3 -> Tabl1
..and so on.

I tried all_Dependencies /user_dependencies to get this information --but I can get a package body and tables which is used /refered by that package ,but could not get procedures/function used in that package.

As codes are very large --just wanted to know if there is an easy way to get this ..may be a query something like which you wrote above .

Thanks for help as always.

Tom Kyte
June 07, 2004 - 10:11 am UTC

nothing tracks at the procedure/function level within a package (heck, you can have procedure XXXX 5 times in there, and function XXXX in there as well). It is the package that uses/references these objects.


You could modify the above query to look for "table names" -- you'll have the same false positive -- but the concept is there.

too much spare time on my hand ...

Gabe, June 08, 2004 - 2:34 pm UTC

Regarding the extraction of static update statements from pl/sql code ... I was getting too many false positive and misses ... so, expanding on your approach, here is some scary looking sql improving the odds. BTW, by the time one begins to think about how to deal with static strings, the procedural parsing approach becomes very, very appealing.

select name,line,psrn stmt_id,text
from (
select name,line,text,text3,sf,st,srn,psrn,tk
,case when first_value(sf) over (partition by name,psrn order by line) is not null then 0 end sb
,case when max(tk) over (partition by name,psrn) > 0 then 0 else 1 end tb
from (
select name,line,text,text3,sf,st,srn,tk
,nvl(max(srn) over (partition by name order by line),1) psrn
from (
select name,line,text,text3
,case when text3 like '% update %'
then row_number() over (partition by name order by line)
end sf
,case when text3 like '%;%'
then row_number() over (partition by name order by line)
end st
,case when text3 like '% update %' or
lag(text3) over (partition by name order by line) like '%;%'
then row_number() over (partition by name order by line)
end srn
,case when text3 like '% set %' then 1
else 0
end tk
from (
select name,line,text,text1, lower(' '||text2||' ') text3
from ( /* comments removed */
select name,line,text,cf,rn,prn,cb,text1
,case when instr(text1,'--',1,1) > 0 then trim(substr(text1,1,instr(text1,'--',1,1)-1))
else trim(rtrim(text1,chr(10)))
end text2
from (
select name,line,text,cf,rn,prn,cb
,case when cb is null then text
when cb = 0 then case when cf is not null and ct is not null -- one line comment
then substr(text,1,instr(text,'/*',1,1)-1)||substr(text,instr(text,'*/',1,1)+2)
when cf is not null and ct is null -- first of multi-line comment
then substr(text,1,instr(text,'/*',1,1)-1)
when cf is null and ct is not null -- last of multi-line comment
then substr(text,instr(text,'*/',1,1)+2)
when cf is null and ct is null -- intra-line of multi-line comment
then null
end
end text1
from (
select name,line,text,cf,ct,rn,prn
,case when first_value(cf) over (partition by name,prn order by line) is not null then 0 end cb
from (
select name,line,text,cf,ct,rn,nvl(max(rn) over (partition by name order by line),1) prn
from (
select name,line,text
,case when text like '%/*%'
then row_number() over (partition by name order by line)
end cf
,case when text like '%*/%'
then row_number() over (partition by name order by line)
end ct
,case when text like '%/*%' or
lag(text) over (partition by name order by line) like '%*/%'
then row_number() over (partition by name order by line)
end rn
from user_source s
where exists ( select null from user_objects o
where s.name = o.object_name
and s.type = o.object_type
and o.status = 'VALID'
)
and type in ('PROCEDURE','FUNCTION','PACKAGE BODY')
order by name,line
) ) ) ) ) ) ) ) )
where sb = 0
and tb = 0
order by name,line

Thanks

Tom Kyte
June 08, 2004 - 3:40 pm UTC

analytics rock and roll though don't they :)

had to include " AND s.TYPE NOT LIKE 'JAVA%' " though :)

pasko, June 09, 2004 - 4:27 am UTC

To Tom and the Last Commenter..

Greeeeat Code !!!
i wonder how long did it take to nest all those selects :)

I tried the code on my Test Database and i immediately got the infamous internal error:
---
ORA-00600: internal error code, arguments: [kkqwrm_noref: COLFDNF set], [], [],
[], [], [], [], []
---
but,
i realized that i have a bunch of Java Classes in my Schema , so the query worked fine after i added the condition to exclude Java Classes from user_objects .

This goes straight to my List of Great SQLs.


Best Regards.



Reader

A reader, April 11, 2005 - 7:06 am UTC

There is a rquirement to get functional points from the
application code in the database. I think these are the
functions and procedures in a package.

Do you by any chance in your archive of pre-developed
PL/SQL code for getting number of functions+procedure in
PL/SQL packages in the database. It will be very
helpful

Thanks


Tom Kyte
April 11, 2005 - 9:05 am UTC

query user_arguments, dba_arguments



steve feuerstein's newest pl/sql puzzle

Matthias Rogel, April 11, 2005 - 10:52 am UTC

hi tom,

great !!

seems to be the key observation needed to solve
steve feuerstein's newest pl/sql-puzzle
"Just Your Type"
[
</code> http://www.oracle.com/technology/pub/columns/plsql/index.html <code>
->
Puzzlers
]

solely using sql

select distinct
owner,
package_name ||
case when package_name is null then null else '.' end ||
object_name as NAME,
case min(position) over (partition by owner, package_name, object_name, overload)
when 0 then 'FUNCTION' else 'PROCEDURE' end as TYPE
from all_arguments


(didn't know the ..._arguments-dictionary-views beforehand)

thanks!

WRAPPED Packages

Matt, August 25, 2005 - 4:50 am UTC

Are the arguments available for wrapped packages? Is there any way to get them? (Other than DESC!)

Tom Kyte
August 25, 2005 - 8:28 am UTC

the code is purposely obscured, there shouldn't be much of anything to see in them until you load them.

Synonyms?

Ben, January 18, 2006 - 10:03 am UTC

Tom,

Say User A has a synonym pointing to a package owned by User B, and has been granted execution privileges on that package. Is there a way, using user_source, all_source, or some other view, to get the code for the package body? All I've been able to get is the package specification.

Thanks!

Tom Kyte
January 19, 2006 - 7:46 am UTC

Not via the ALL_SOURCE view, that explicitly limits the package bodies you can see to

a) your own code
b) others IF and only IF you have "execute any procedure" (don't grant that)


You would either need access to DBA_SOURCE, or the owner of the package could write your a procedure that returns a ref cursor that shows you the package body...

Even execute any procedure does not show package body?

Albert Nelson A, January 19, 2006 - 10:28 am UTC 

Hi Tom,

Even granting execute any procedure does not seem to show package body. 

SQL> @test.sql
SQL> set echo on
SQL> connect / as sysdba
Connected.
SQL> drop user test cascade;

User dropped.

SQL> create user test identified by test;

User created.

SQL> grant connect, resource to test;

Grant succeeded.

SQL> connect test/test;
Connected.
SQL> create or replace package test is
  2   procedure test;
  3   end;
  4  /

Package created.

SQL> create or replace package body test is
  2    procedure test is
  3     begin
  4      null;
  5       end;
  6      end;
  7  /

Package body created.

SQL> connect / as sysdba
Connected.
SQL> drop user test2 cascade;

User dropped.

SQL> create user test2 identified by test2;

User created.

SQL> grant connect, resource to test2;

Grant succeeded.

SQL> grant execute any procedure to test2;

Grant succeeded.

SQL> connect test2/test2
Connected.
SQL> select text from all_source where owner = 'TEST' and name = 'TEST' order by type, line;

TEXT
--------------------------------------------------------------------------------
package test is
 procedure test;
 end;

SQL> 
SQL> 
SQL> 
SQL> 
SQL> 


Have I done any thing wrong?

Tom Kyte
January 19, 2006 - 1:47 pm UTC

ahh, sorry, it was:

......
or
(
/* package body */
o.type# = 11 and
(
privilege# = -141 /* CREATE ANY PROCEDURE */
or
privilege# = -241 /* DEBUG ANY PROCEDURE */
)
)
....

but please - begging you here - don't grant ANY privileges without thinking about it for a day or two. They come with certain "powerful powers"

dba_arguments is missing

Volker, April 17, 2009 - 9:48 am UTC

Hi Tom,

in your "Followup April 11, 2005 - 9am US/Eastern"
you wrote
<
query user_arguments, dba_arguments
>

Having ORA-9.2.0.8.0 I cannot find dba_arguments:
as sys

select view_name from user_views
where view_name like '%ARGUMENT%'
/

gives me
ALL_ARGUMENTS
ALL_JAVA_ARGUMENTS
ALL_OPARGUMENTS
DBA_JAVA_ARGUMENTS
DBA_OPARGUMENTS
USER_ARGUMENTS
USER_JAVA_ARGUMENTS
USER_OPARGUMENTS

Is dba_arguments new in 10/11g?


Tom Kyte
April 17, 2009 - 10:49 am UTC

sorry, should have been all_arguments

A reader, April 17, 2009 - 10:03 pm UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library