Systems Engineering and RDBMS

Back to the basics: Knowing parameters for Procedures, Packages and Functions

Posted by decipherinfosys on January 28, 2009

Database development requires a lot of packages, procedures and functions.  All these stored programs also take arguments as a parameter,  be it an input parameter or an output parameter.  It is almost impossible to remember the parameters or signature of these stored programs (packages, procedures, functions).

Easiest way to check the signature is to run the desc (DESCRIBE) command at the sql prompt.  Following command shows parameters of the test_proc stored procedure.

SQL> desc test_proc
PROCEDURE test_proc
Argument Name                  Type                    In/Out Default?
—————————— ———————– —— ——–
P_COL1                         VARCHAR2                IN
P_COL2                         DATE                    IN
P_COL3                         NUMBER                  IN

Other way to get to the same information is by querying the USER_ARGUMENTS view.  This view contains information about all the arguments for all the packages, procedures, and functions.  One can look at the oracle documentation for more details on the columns in USER_ARGUMENTS view.  In the following sql, we are showing the most relevant information we need to know about any stored programs like data type, default values, position etc.

COLUMN object_name format A10
COLUMN argument_name format A10
COLUMN data_Type format A10
COLUMN IN_OUT format A3
COLUMN position format 99

SELECT object_name,argument_name,data_Type,in_out,position
FROM user_arguments ua
WHERE object_name IN (‘TEST_PROC’,’TEST_FUNC’)
ORDER BY object_name,position;

OBJECT_NAM ARGUMENT_N DATA_TYPE  IN_ POSITION
---------- ---------- ---------- --- --------
TEST_FUNC             VARCHAR2   OUT        0
TEST_FUNC  SKUID      VARCHAR2   IN         1

TEST_PROC  P_COL1     VARCHAR2   IN         1
TEST_PROC  P_COL2     DATE       IN         2
TEST_PROC  P_COL3     NUMBER     IN         3

One thing to note is that for functions, position 0 is always return value of the function. So it shows the data type, length etc for returning value of the function. All the actual arguments start with position 1. If we describe the function, it will be shown like this.
SQL> desc test_func
FUNCTION test_func RETURNS VARCHAR2

Argument Name                  Type                    In/Out Default?
—————————— ———————– —— ——–
P_ITEM                         VARCHAR2                IN

Other than information shown above, we can also check for “TYPE” object parameter, default values of the arguments, default length, character set of the argument etc.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: