Getting Package Parameters via SQL Command 3

[tekdudedude]

Hello,

How can I get the parameters for an Oracle package of my choosing (say CTX_DDL for example) using a SQL command?

I saw a DBA some time ago use some SQL that returned all the parameters used in a Package. I am having a tough time finding the SQL to do this in the docs.


Thanks,

TD

 

[SantaMufasa]

TekDude[sup]2[/sup],

Oracle packages, per se, do not have "parameters" of their own...their global PROCEDUREs and FUNCTIONs have parameters (or arguments).

You can see the entire source code of any Oracle package header (available to you), including parameter/argument definitions, by running the following script:

SQL> @ShowPackageCode
Enter the name of the Package you wish to display: dbms_output

TEXT
---------------------------------------------------------------------------
package dbms_output as

-- DE-HEAD     <- tell SED where to cut when generating fixed package

  ------------
  --  OVERVIEW
  --
  --  These procedures accumulate information in a buffer (via "put" and
  --  "put_line") so that it can be retrieved out later (via "get_line" or
  --  "get_lines").  If this package is disabled then all
.
.
.
  procedure put_line(a varchar2);
  pragma restrict_references(put_line,WNDS,RNDS);
  procedure put_line(a number);
  pragma restrict_references(put_line,WNDS,RNDS);
.
.
.
***************************************************************************

Let us know if this is useful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Wouldn't it be nice if I included the contents of the "ShowPackageCode.sql" script <grin>:

set verify off
accept choice prompt "Enter the name of the Package you wish to display: "
select text
from all_source
 where name = upper('&choice')
  and type = 'PACKAGE'

Sorry,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[sandtek]

Hi tekdudedude,
I hope you've already considered of using DESC to show proc/func params within packages.

 

[tekdudedude]

Very nice Santa - thanks.

 

[CRoberts]

To see the arguments of each function or procedure for a package just query the dictionary tables. I have report (do not run as SYS) that lists all the packages and their arguments for a schema that can be modified for just object:

SELECT Obj.Object_Type,
Arg.Package_Name AS Parent_Name,
REPLACE(Arg.Object_Name||'('||Arg.Overload||')', '()', NULL) AS Object_Name,
DECODE(Arg.Position, 0, NULL, Arg.Position) AS Position_Nr,
Arg.Argument_Name,
DECODE(Arg.Data_Type, 'UNDEFINED', Arg.Type_Name, Arg.Data_Type) AS Data_Type,
DECODE(Arg.Position, 0, 'RETURN', Arg.In_Out) AS In_Out
FROM User_Objects Obj,
User_Arguments Arg
WHERE Arg.Object_Id = Obj.Object_Id
AND Obj.Object_Type IN ('PACKAGE', 'FUNCTION', 'PROCEDURE')
AND (
(Arg.Position IS NOT NULL AND Arg.Argument_Name IS NOT NULL)
OR
DECODE(Arg.Position, 0, 'RETURN', Arg.In_Out) = 'RETURN'
)
ORDER BY 1, 2, 3, NVL(Arg.Position, 0)
/

 

[SantaMufasa]

Excellent script, CR. Hava

!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]