Newbie Question

[arpan]

Suppose I have created a stored procedure in SQL Plus named spTry which retrieves all the records from a DB table. Now after exiting from the SQL Plus interface & then logging back in, how do I view the code of the stored procedure that I had created previously so that, if the need arises, I can make some changes in the stored procedure?

Also how do I execute the stored procedure so that all the records get retrieved & displayed in the SQL Plus interface similar to how the records get retrieved & displayed in the Query Analyzer using EXEC <ProcedureName> in SQL Server? EXEC <ProcedureName> in SQL Plus just displays PL/SQL procedure successfully completed!!

Thanks,

Arpan

 

[ocpguru]

Save this script (see below) in a file called 'grab_procedure.sql'. Call it from sql*plus (ie @grab_procedure <procedure-name> ). It will spool your procedure to a text file named '<procedure-name>.sql' along with grants for the proc. Other tools are much easier to use. I use TOAD (

http://www.toadsoft.com)

you can get a free version. For your second question.... you'd need to loop through the cursor (record set) you selected and do a 'dbms_output' statement to have each row show up in sql*plus.
----------- cut and paste script --------------
set echo off
set verify off
set term off
set linesize 140
col dummy noprint
col dummy2 noprint
col text format a139 word
set heading off
set pages 0
set feed off
spool &1..sql
select 1 dummy, NAME dummy2, 'create or replace '||rtrim(text)
from user_source WHERE name = upper('&1')
and line = 1
and type = 'PROCEDURE'
union
select line, NAME, rtrim(text)
from user_source
where line <> 1
and type = 'PROCEDURE'
AND name = upper('&1')
order by 2,1
/
select 'grant '||privilege||
' on '||user||'.'||TABLE_NAME||' to '||grantee||
decode(grantable,'YES',' with grant option','')||';' name
from all_TAB_privs
WHERE GRANTOR = user
and table_name = upper('&1')
AND GRANTEE != GRANTOR
order by TABLE_NAME, grantee
/
spool off
----------------------- end script -------------------

 

[arpan]

Hi,

Thanks for your input. I tried out your suggestion (the Grab_Procedure one). It did work & created a file named <ProcedureName>.sql but when I tried to view the contents of this new SQL file in a text editor, it was completely empty......not even a single line of code could be seen. Is that what should happen? I presume no!!!

Regards,

Arpan

 

[ocpguru]

Did you change <ProcedureName> to the real name of your stored procedure? Are you logging into oracle as the owner of the stored procedure? You can run this query to get the name of your procedure as its saved in oracle...

select object_name
from user_objects;

 

[arpan]

Actually I think the reason why <ProcedureName>.sql is blank is because I must not have saved your Grab_Procedure in the proper directory. I saved Grab_Procedure in the BIN directory under ORANT in the D: drive (Windows is in D. Should Grab_Procedure be saved in this BIN directory only or in some other directory? My procedure is named spTry.

When I issued the following command:

SELECT * FROM USER_OBJECTS;

then I was shown a list of all the tables & procedures including spTry which I had created but Grab_Procedure was not listed among them. Also how do I find out whether I am logging in Oracle as the owner of the stored procedure or no? Whenever I open SQL Plus, I enter the User Name as scott, Password as tiger & Host String as ArpanDB which is the database I had created when I was installing Oracle. So am I logging in Oracle as the owner of the stored procedure? I guess I am, if I am not mistaken!!!

Some of the questions I am asking maybe very simple but since I am a newbie in Oracle, I am asking these questions which you might find quite irritating. But please do bear with me. Thanks once again,

Regards,

Arpan