Show the code for a stored procedure 3

[xxing]

Hi

I created the following code to show the code in my stored procedures:

set serveroutput ON
set feedback off
set linesize 200
set ver off

col name format A100 Heading 'List of Stored Procedures'

PROMPT

-- Show the list of stored procedures for the user name
SELECT distinct name
FROM User_Source
WHERE type = 'PROCEDURE'
Order by name;

col text format A200 Heading 'Code'

PROMPT
PROMPT

--Show the code for the stored procedure chosen
select
Text
from user_source
where
name = upper('&Stored_Procedure')
and type = 'PROCEDURE'
order by
Line;

PROMPT

-----------------------------------------------------------------

The above code shows a list of stored procedures. Then it prompts for the stored procedure name. Then it will show the code for that stored procedure.

I would like to improve this. I would like to include a row number when showing the list of stored procedures. Then choose the stored procedure by entering a number.

e.g

List of Stored Procedures
-----------------------------
1 SP1
2 SP2
S SP3

Enter value for Stored Procedure: 1

Code
----------------------------------
select column
from Table

Does any one know how to do this?

Regards
Mark

 

[Dagon]

I think you'd have to do this by creating a table containing the names of the procedures you want with their menu number. I can't see otherwise how you could associate the procedure name with an arbitrary number like 1,2,3.

This is what you'd need:

drop table show_choices
/

create table show_choices
(procedure_Name varchar2(30),
 menu_choice number)
/

create or replace procedure sp1 is
begin
null;
end;
/

create or replace procedure sp2 is
begin
null;
end;
/

create or replace procedure sp3 is
begin
null;
end;
/

insert into show_choices values ('SP1', 1);
insert into show_choices values ('SP2', 2);
insert into show_choices values ('SP3', 3);

set serveroutput ON
set feedback off
set linesize 200
set ver off
set pagesize 5000
set heading off
col menu_choice new_value choice

select menu_choice, procedure_name
from show_choices
/

accept choice prompt 'Make your selection: '

select u.text
from user_source u, show_choices s
where u.type = 'PROCEDURE'
and   u.name = s.procedure_name
and   s.menu_choice = &choice
order by u.line
/

For Oracle-related work, contact me through Linked-In.

 

[taupirho]

Could you not concatanate the rownumber to the procedure name when listing your procedures then decode using the like operator or (more likely) use wildcard matching to identify the procedure name from the number chosen by the user, then display the chosen procedure text.




In order to understand recursion, you must first understand recursion.

 

[taupirho]

Something like (simplistically)

SQL> SELECT distinct name
2 FROM User_Source
3 WHERE type = 'PROCEDURE'
4 and name like '%TEST%'
5 Order by 1
6
SQL> /

NAME
------------------------------
TEST
TOMTEST1

SQL> select
2 Text
3 from user_source
4 where
5 name =
6 (
7 select substr(list_of_procs,3,20)
8 from
9 (
10 select rownum || ' ' || name list_of_procs from
11 (
12 SELECT distinct name
13 FROM User_Source
14 WHERE type = 'PROCEDURE'
15 and name like '%TEST%'
16 Order by 1
17 )
18 )
19 where list_of_procs like '&&1' || ' %'
20 )
21 and type = 'PROCEDURE'
22
SQL> /
Enter value for 1: 1
old 19: where list_of_procs like '&&1' || ' %'
new 19: where list_of_procs like '1' || ' %'

TEXT
--------------------------------------------------------------------------------
procedure test(temp_cur OUT sys_refcursor)
is
begin
with
tom1 as (select * from tom where f1 = 1),
tom3 as (select * from tom where f1 = 3),
tom as (select * from tom),
open temp_cur for
select * from tom1;
end;

10 rows selected.

Enter value for 1: 2
old 19: where list_of_procs like '&&1' || ' %'
new 19: where list_of_procs like '2' || ' %'

TEXT
--------------------------------------------------------------------------------
procedure tomtest1 as
type mycur is ref cursor;
i_cursor mycur;
my_num integer;
begin
execute immediate 'create global temporary table x(y integer)';
execute immediate 'insert into x values(99)';
execute immediate 'insert into x values(999)';
execute immediate 'insert into x values(9999)';
execute immediate 'insert into x values(99999)';
open i_cursor for 'select y from x';

TEXT
--------------------------------------------------------------------------------
loop
fetch i_cursor into my_num;
exit when i_cursor%notfound;
dbms_output.put_line( my_num );
end loop;
close i_cursor;
end;

18 rows selected.





In order to understand recursion, you must first understand recursion.

 

[SantaMufasa]

Here is a code alternative that one can run as straight SQL, without creating additional objects in the database. I started out with your code, then modified it a bit. For the sake of performance/efficiency, I queried USER_OBJECTS instead of USER_SOURCE. Also, I used the Oracle DDL-creating procedure DBMS_METADATA.GET_DDL instead of querying USER_SOURCE. (Since the code ACCEPTs keyboard input via SQL*Plus, the code needs to run from an invoked script name. In my case, I used the script name, "TEMP.sql".) :

(Contents of temp.sql)

set serveroutput ON
set feedback off
set linesize 200
set ver off
set long 55000

col Procs format A40 Heading 'List of Stored Procedures'
PROMPT

-- Show the list of stored procedures for the user name
select to_char(rownum,'999')||' - '||object_name Procs
  from (SELECT object_name 
          FROM User_Objects
         WHERE object_type = 'PROCEDURE'
         Order by object_name)
/

accept which prompt "Enter the number of the Procedure which code you wish to see: "

prompt
prompt
col x format a32767
select 'Procedure Name: '||object_name||chr(10)||chr(10)||
       dbms_metadata.get_ddl('PROCEDURE',object_name)||chr(10)||'/' x
  from (select rownum rn,object_name
          from (SELECT object_name 
                  FROM User_Objects
                 WHERE object_type = 'PROCEDURE'
                 Order by object_name)
       )
 where rn = &which;

PROMPT 
PROMPT

Here is a sample invocation and execution of temp.sql:

SQL> @temp

   1 - BOGUS
   2 - DELIMITED_TO_ROWS
   3 - GETTRIBEINFO
   4 - GET_DATA
   5 - INDEXLIMITEDCREATES
   6 - PREP_USER_CONSTRAINTS
   7 - SPLIT
Enter the number of the Procedure which code you wish to see: 1


Procedure Name: BOGUS


  CREATE OR REPLACE PROCEDURE "SCHEMA_NAME"."BOGUS" (x varchar2) is
begin
    dbms_output.put_line(x);
end;

/

I also designed the output to preface the code with the Procedure Name, then show the code in a way that you can simply copy and paste the results if you wish to re-create/re-complile the procedure's code.

Let us know if this resolves your need.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[xxing]

Hi

Thank you all for your help

Regards
Mark

 

[stefanhei]

Hi,

When not using a table like in Dagons solution I would order by creation time (user_objects.created). That way the output may be less userfriendly, but a procedure created in the time between listing all stored procedures and displaying the content would not make the script produce wrong results.

Stefan

 

[SantaMufasa]

Good suggestion, Stefan.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”