Re: Acessing stored procedures using sqlplus

In article <82jlnt$mm8$1_at_nnrp1.deja.com>,   payal_at_corvia.com wrote:
> Hi
>
> I need to know how to access my stored procedures or packages
> using sqlplus in order to compile them and access them. Is there
> a table in sql that has a list of all the stored procedures and
> packages. Where are they stored??
>
> I can put a tored procedure in sql, using a file and then run it.
> Is there a command like 'show procedure some_procedure' or
> 'edit procedure some_procedure'. run just compiles a procedure.
>
> Thanks
> payal
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Accessing Input Parameters:

SQL> DESC STOREDPROCNAME; Getting Code of SP:

SQL> SELECT TEXT
FROM USER_SOURCE
WHERE NAME = 'STOREDPROCNAME'
AND TYPE = 'PROCEDURE'
ORDER BY LINE; Just change TYPE to 'PACKAGE' for package code.

To run a SP from SQL* Plus:

SQL> EXEC STOREDPROCNAME(P1, P2,...Px);

To Recompile an object you can use DBMS_DDL.ALTER_COMPILE or ALTER:

SQL> EXEC DBMS_DDL.ALTER_COMPILE('PROCEDURE','SCHEMA','STOREDPROCNAME'); SQL> ALTER PROCEDURE STOREDPROCNAME COMPILE; To edit SP's, I use simple Cut and Paste into/out of Oracle Schema Manager or SQL* Plus. Lots of ways to do that.

Hope this helps.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Dec 10 1999 - 08:19:44 CST