-
is there anyway that i can see what are the procedure contents already created within a Stored Procedure?
thanks in advance.
-
Please post more information if you want us to help you with this problem.
Cheers!!!
oraclemz@yahoo.com
-
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?
-
have a look at v$source or dba_source
Be careful with the format of the text column
Have Fun
-
select text from dba_source where name='MY_PROCEDURE'
order by line
/
-
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?
-
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
-
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!