1 package dbms_describe is
2
3 ------------
4 -- OVERVIEW
5 --
6 -- This package is used to describe the arguments of a stored
7 -- procedure. The client specifies an object name and describe returns
8 -- a set of indexed tables with the results. Full name
9 -- translation is performed and security checking is also
10 -- checked on the final object.
11
12 --------
13 -- USES
14 --
15 -- The primary client of this package is ODESSP (Oracle Call
16 -- Interface Describe PRocedure).
17
18 ---------------
19 -- LIMITATIONS
20 --
21 -- Currently describes of remote objects are not supported. It is
22 -- intended to support this at some point in the future.
23 --
24 -- Currently descibes of all procedures/functions within a package
25 -- is not supported. We could add a 'describe_package' procedure
26 -- that returns the names of all procedures/functions for the named
27 -- package and then the client could call 'describe_procedure' for
28 -- those in which it has an interest.
29
30 ------------
31 -- SECURITY
32 --
33 -- Describe is available to PUBLIC and performs it's own
34 -- security checking based on the object being described.
35
36 -----------------------
37 -- TYPES AND CONSTANTS
38 --
39 type varchar2_table is table of dbms_id index by binary_integer;
40 -- Indexed table type which is used to return the argument names.
41
42 type number_table is table of number index by binary_integer;
43 -- Indexed table type which is used to return various argument fields.
44
45 ------------
46 -- EXAMPLES
47 --
48 -- External service interface
49 ------------------------------
50 --
51 -- The ODESSP OCI call may be used to call this routine from a user
52 -- program. (See the Oracle Call Interface guide for a description)
53 -- Also, this routine could be called from other stored procedures
54 -- using the varchar2_table and number_table types.
55 --
56 -- EXAMPLE :
57 --
58 -- Client provides -
59 --
60 -- object_name - SCOTT.ACCOUNTING.ACCOUNT_UPDATE
61 -- total_elements - 100
62 --
63 -- ACCOUNT_UPDATE is an overloaded function in package ACCOUNTING
64 -- with specification :
65 --
66 -- type number_table is table of number index by binary_integer
67 -- table account (account_no number, person_id number,
68 -- balance number(7,2))
69 -- table person (person_id number(4), person_nm varchar2(10))
70 --
71 -- function ACCOUNT_UPDATE (account number,
72 -- person person%rowtype, amounts number_table,
73 -- trans_date date) return account.balance%type;
74 --
75 -- function ACCOUNT_UPDATE (account number,
76 -- person person%rowtype, amounts number_table,
77 -- trans_no number) return account.balance%type;
78 --
79 --
80 -- Values returned -
81 --
82 -- overload position argument level datatype length prec scale rad
83 -- -------------------------------------------------------------------
84 -- 1 0 0 NUMBER 7 2 0 0
85 -- 1 1 ACCOUNT 0 NUMBER 22 0 0 0
86 -- 1 2 PERSON 0 RECORD
87 -- 1 2 PERSON_ID 1 NUMBER 4 0 0 0
88 -- 1 2 PERSON_NM 1 VARCHAR2 10
89 -- 1 3 AMOUNTS 0 TABLE
90 -- 1 3 1 NUMBER 22 0 0 0
91 -- 1 4 TRANS_NO 0 NUMBER 22 0 0 0
92 --
93 -- 0 0 0 NUMBER 7 2 0 0
94 -- 0 1 ACCOUNT 0 NUMBER 22 0 0 0
95 -- 0 2 PERSON 0 RECORD
96 -- 0 2 PERSON_ID 1 NUMBER 4 0 0 0
97 -- 0 2 PERSON_NM 1 VARCHAR2 10
98 -- 0 3 AMOUNTS 0 TABLE
99 -- 0 3 1 NUMBER 22 0 0 0
100 -- 0 4 TRANS_DATE 0 DATE
101 --
102
103 ----------------------------
104 -- PROCEDURES AND FUNCTIONS
105 --
106 procedure describe_procedure (object_name in varchar2,
107 reserved1 in varchar2, reserved2 in varchar2,
108 overload out number_table, position out number_table,
109 level out number_table, argument_name out varchar2_table,
110 datatype out number_table, default_value out number_table,
111 in_out out number_table, length out number_table,
112 precision out number_table, scale out number_table,
113 radix out number_table, spare out number_table,
114 include_string_constraints boolean := FALSE);
115 --
116 -- Describe pl/sql object with the given name. Returns the arguments
117 -- ordered by overload, position. The name resolution follows the
118 -- rules for SQL. Top level procedures and functions, as well as
119 -- packaged procedures and functions, may be described. Procedures
120 -- and functions in package STANDARD must be prefixed by "STANDARD"
121 -- (e.g., 'standard.greatest' will describe function "GREATEST" in
122 -- package "STANDARD").
123 -- Input parameters:
124 -- object_name
125 -- The name of the procedure being described. The form is
126 -- [[part1.]part2.]part3[@dblink]
127 -- The syntax follows the rules for identifiers in SQL. The name may
128 -- be a synonym and may contain delimited identifiers (double quoted
129 -- strings). This parameter is required and may not be null.
130 -- The total length of the name is limited to 197 bytes.
131 -- reserved1, reserved2
132 -- Reserved for future use. Must be set to null or empty string.
133 -- include_string_constraints
134 -- Output the constraints on stringy formal types.
135 -- Output parameters:
136 -- overload
137 -- A unique number assigned to the procedure signature. If a
138 -- procedure is overloaded, this field will hold a different
139 -- value for each version of the procedure.
140 -- position
141 -- Position of the argument in the parameter list beginning with 1.
142 -- Position 0 indicates a function return value.
143 -- level
144 -- If the argument is a composite type (like record), this
145 -- parameter returns the level of datatype. See example
146 -- section for a usage example.
147 -- argument_name
148 -- The name of the argument.
149 -- datatype
150 -- Oracle datatype of the parameter. These are:
151 -- 0 - This row is a placeholder for a procedure with
152 -- no arguments.
153 -- 1 - VARCHAR2
154 -- 2 - NUMBER
155 -- 3 - NATIVE INTEGER (BINARY_INTEGER or PLS_INTEGER)
156 -- 8 - LONG
157 -- 11 - ROWID
158 -- 12 - DATE
159 -- 23 - RAW
160 -- 24 - LONG RAW
161 -- 58 - OPAQUE TYPE
162 -- 96 - CHAR
163 -- 100 - BINARY_FLOAT
164 -- 101 - BINARY_DOUBLE
165 -- 104 - UROWID
166 -- 106 - MLSLABEL
167 -- 112 - CLOB
168 -- 113 - BLOB
169 -- 114 - BFILE
170 -- 121 - OBJECT
171 -- 122 - NESTED TABLE
172 -- 123 - VARRAY
173 -- 178 - TIME
174 -- 179 - TIME WITH TIME ZONE
175 -- 180 - TIMESTAMP
176 -- 181 - TIMESTAMP WITH TIME ZONE
177 -- 182 - INTERVAL YEAR TO MONTH
178 -- 183 - INTERVAL DAY TO SECOND
179 -- 231 - TIMESTAMP WITH LOCAL TIME ZONE
180 -- 250 - PL/SQL RECORD (see "Notes:" below)
181 -- 251 - PL/SQL TABLE
182 -- 252 - PL/SQL BOOLEAN (see "Notes:" below)
183 -- default_value
184 -- 1 if the parameter has a default value. 0, otherwise.
185 -- in_out
186 -- 0 = IN param, 1 = OUT param, 2 = IN/OUT param
187 -- length
188 -- The data length of the argument.
189 -- For CHAR(N)/VARCHAR2(N), length is N, the number of bytes on the
190 -- server. For NCHAR(N)/NVARCHAR2(N), length is N, the number of
191 -- characters on the server (this is different from the number of
192 -- bytes required to store the string).
193 -- Starting with Oracle 9.0, NCHAR only supports UTF8 and AL16UTF16.
194 -- precision
195 -- Precision of the argument (if the datatype is number).
196 -- scale
197 -- Scale of the argument (if the datatype is number).
198 -- radix
199 -- Radix of the argument (if the datatype is number).
200 -- spare
201 -- Reserved for future functionality.
202 -- Exceptions:
203 -- ORA-20000 - A package was specified. Can only specify top-level
204 -- procedure/functions or procedures/functions within a package.
205 -- ORA-20001 - The procedure/function does not exist within the package.
206 -- ORA-20002 - The object is remote. This procedure cannot currently
207 -- describe remote objects.
208 -- ORA-20003 - The object is invalid. Invalid objects cannot be
209 -- described.
210 -- ORA-20004 - A syntax error in the specification of the object's name.
211 -- Notes:
212 -- There is currently no way from a 3gl to directly bind to an
213 -- argument of type 'record' or 'boolean'. For booleans, there are
214 -- the following work-arounds. Assume function F returns a boolean, G
215 -- is a procedure with one IN boolean argument, and H is a procedure
216 -- which has one OUT boolean argument.
217 -- Then you can execute these functions, binding in DTYINTs (native
218 -- integer) as follows, where 0=>FALSE and 1=>TRUE:
219 --
220 -- begin :dtyint_bind_var := to_number(f); end;
221 --
222 -- begin g(to_boolean(:dtyint_bind_var)); end;
223 --
224 -- declare b boolean; begin h(b); if b then :dtyint_bind_var := 1;
225 -- else :dtyint_bind_var := 0; end if; end;
226 --
227 -- Access to procedures with arguments of type 'record' would require
228 -- writing a wrapper similar to that in the 3rd example above (see
229 -- function H).
230 end;
[Home] [Help]