Listing procedure contents in SQL*Plus?
jawa
SQL*Plus. DESCRIBE only lists the procedure's parameters. Have searched
documentation until exhausted. Anyone have a way of doing this? Thanks.
--
Jim Wadas
Information Technology Solutions and Services (ITSS)
Motorola Systems Solutions Group (SSG)
Scottsdale, AZ 85257
(480) 441-8196
Jim....@motorola.com
KM
jawa <Jim....@motorola.com> wrote in message
news:7tdc2u$da7$1...@schbbs.mot.com...
Kenneth G. Markus
I have found that the procedure_name is typically in all caps, you can verify
this by typing SELECT DISTINCT name FROM user_source...
Christopher Beck
>Another dumb question. I want to list the contents of a procedure in
>SQL*Plus. DESCRIBE only lists the procedure's parameters. Have searched
>documentation until exhausted. Anyone have a way of doing this? Thanks.
try something like:
select text
from all_source
where owner = '<OWNER_NAME>'
and name = '<PROCEDURE/FUNCTION/PACKAGE/TYPE NAME>'
and type = 'PROCEDURE/FUNCTION/PACKAGE/PACKAGE BODY/TYPE/TYPE BODY'
order by line
hope this helps.
chris.
--
Christopher Beck
Oracle Corporation
clb...@us.oracle.com
Reston, VA.
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Christopher Beck
wrote:
>SELECT text FROM user_source WHERE name = '[procedure_name]'
>
>I have found that the procedure_name is typically in all caps, you can verify
>this by typing SELECT DISTINCT name FROM user_source...
Jim,
FYI. By default, Oracle will create your objects ( tables, views, procs... )
with names that are uppercased.
You can though create objects in mixed case and with spaces. As long as
you put "" around the name, you can do pretty much what you want.
eg.
clbeck@8i> create or replace
2 procedure "MixEd CaseD Proc NAmE" is
3 begin
4 dbms_output.put_line( 'Hi' );
5 end;
6 /
Procedure created.
clbeck@8i> exec "MixEd CaseD Proc NAmE"
Hi
PL/SQL procedure successfully completed.
1 select text
2 from user_source
3 where name = 'MixEd CaseD Proc NAmE'
4* order by line
clbeck@8i> /
TEXT
------------------------------------
procedure "MixEd CaseD Proc NAmE" is
begin
dbms_output.put_line( 'Hi' );
end;
or even more wacky...
clbeck@8i> create table "a$#%* 0)(*#@ ("
2 ( "kj*&(SD* jsa d.,/?><" number );
Table created.
clbeck@8i> desc "a$#%* 0)(*#@ ("
Name Null? Type
----------------------------------------- -------- ----------------------------
kj*&(SD* jsa d.,/?>< NUMBER
clbeck@8i> insert into "a$#%* 0)(*#@ (" values ( 1 );
1 row created.
clbeck@8i> select "kj*&(SD* jsa d.,/?><"
2 from "a$#%* 0)(*#@ (";
kj*&(SD* jsa d.,/?><
--------------------
1
chris.
>
>jawa wrote:
>
>> Another dumb question. I want to list the contents of a procedure in
>> SQL*Plus. DESCRIBE only lists the procedure's parameters. Have searched
>> documentation until exhausted. Anyone have a way of doing this? Thanks.
>>
>> --
>> Jim Wadas
>> Information Technology Solutions and Services (ITSS)
>> Motorola Systems Solutions Group (SSG)
>> Scottsdale, AZ 85257
>> (480) 441-8196
>> Jim....@motorola.com
Hakan Eren
You can use the following procedure:
SQLWKS> set serveroutput on
Server Output ON
SQLWKS> begin get_source('get_source'); end;
2>
Statement processed.
CREATE OR REPLACE PROCEDURE get_source(p_object_name VARCHAR2,
p_output IN VARCHAR2 DEFAULT 'SCREEN',
p_dir VARCHAR2 DEFAULT 'c:\temp') IS
/*
|| Author:
|| Hakan Eren
|| Network Vision Consulting Inc.
||
|| This procedure extracts the source code of a stored database package/
|| procedure/function.
|| Usage:
|| In SQL/Plus or SQL Worksheet
|| BEGIN
|| get_source('<Package/Procedure/Function Name>', '<DirectoryName>');
|| END;
||
|| If directory is not specified, source will be directed to the screen.
||
|| Prerequisities: UTL_FILE package should be installed.
|| UTL_FILE_DIR parameter should be specified in
INIT<SID>.ora file.
|| Please remember, If Oracle Server is on a server, UTL_FILE
package
|| can write to network drives.
*/
--
CURSOR first_line_cur IS
SELECT DISTINCT name, type, SUBSTR(text, 1, LENGTH(text)-1) text
FROM user_source
WHERE line = 1
AND name = UPPER(NVL(p_object_name, '*'))
ORDER BY name ASC, type ASC;
--
CURSOR rest_cur(p_name VARCHAR2, p_type VARCHAR2) IS
SELECT SUBSTR(text, 1, LENGTH(text)-1) text
FROM user_source
WHERE name = UPPER(p_name)
AND type = p_type
AND line > 1
ORDER BY line;
--
out_file UTL_FILE.FILE_TYPE;
found VARCHAR(1) := 'N';
--
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
/*
|| Open the output file
*/
IF(p_output = 'FILE') THEN
out_file := UTL_FILE.FOPEN(p_dir, p_object_name || '.sql', 'W');
/*
|| First loop for getting first line, Second Loop for remaining lines
*/
FOR first_line_rec IN first_line_cur LOOP
UTL_FILE.PUT_LINE(out_file, 'CREATE OR REPLACE ' ||
first_line_rec.text);
FOR rest_rec IN rest_cur(first_line_rec.name, first_line_rec.type)
LOOP
UTL_FILE.PUT_LINE(out_file, RTRIM(rest_rec.text));
END LOOP;
UTL_FILE.PUT_LINE(out_file, '/');
found := 'Y';
END LOOP;
UTL_FILE.FCLOSE(out_file);
ELSE
FOR first_line_rec IN first_line_cur LOOP
DBMS_OUTPUT.PUT_LINE('CREATE OR REPLACE ' || first_line_rec.text);
FOR rest_rec IN rest_cur(first_line_rec.name, first_line_rec.type)
LOOP
DBMS_OUTPUT.PUT_LINE(RTRIM(rest_rec.text));
END LOOP;
DBMS_OUTPUT.PUT_LINE( '/');
found := 'Y';
END LOOP;
END IF;
IF(p_object_name IS NULL) THEN
DBMS_OUTPUT.PUT_LINE('The name of the object cannot be null');
ELSIF(found = 'N') THEN
DBMS_OUTPUT.PUT_LINE('Please check object name, no record found');
END IF;
END get_source;
/
Didier LENQUETTE
FROM user_sources
WHERE type = 'PROCEDURE'
AND name = procedure_name
ORDER BY line
/
A+
jawa <Jim....@motorola.com> a écrit dans le message :
7tdc2u$da7$1...@schbbs.mot.com...