1 package dbms_sql AUTHID CURRENT_USER is
2
3 $if utl_ident.is_oracle_server <> TRUE and
4 utl_ident.is_timesten <> TRUE $then
5 $error 'dbms_sql is not supported in this environment' $end
6 $end
7
8 ------------
9 -- OVERVIEW
10 --
11 -- This package provides a means to use dynamic SQL to access the database.
12 --
13
14 -------------------------
15 -- RULES AND LIMITATIONS
16 --
17 -- Bind variables of a SQL statement are identified by their names.
18 -- When binding a value to a bind variable, the string identifying
19 -- the bind variable in the statement may optionally contain the
20 -- leading colon. For example, if the parsed SQL statement is
21 -- "SELECT ENAME FROM EMP WHERE SAL > :X", on binding the variable
22 -- to a value, it can be identified using either of the strings ':X'
23 -- and 'X'.
24 --
25 -- Columns of the row being selected in a SELECT statement are identified
26 -- by their relative positions (1, 2, 3, ...) as they appear on the select
27 -- list from left to right.
28 --
29 -- Privileges are associated with the caller of the procedures/functions
30 -- in this package as follows:
31 -- If the caller is an anonymous PL/SQL block, the procedures/functions
32 -- are run using the privileges of the current user.
33 -- If the caller is a stored procedure, the procedures/functions are run
34 -- using the privileges of the owner of the stored procedure.
35 --
36 -- WARNING: Using the package to dynamically execute DDL statements can
37 -- results in the program hanging. For example, a call to a procedure in a
38 -- package will result in the package being locked until the execution
39 -- returns to the user side. Any operation that results in a conflicting
40 -- lock, such as dynamically trying to drop the package, before the first
41 -- lock is released will result in a hang.
42 --
43 -- The flow of procedure calls will typically look like this:
44 --
45 -- -----------
46 -- | open_cursor |
47 -- -----------
48 -- |
49 -- |
50 -- v
51 -- -----
52 -- ------------>| parse |
53 -- | -----
54 -- | |
55 -- | |---------
56 -- | v |
57 -- | -------------- |
58 -- |-------->| bind_variable | |
59 -- | ^ ------------- |
60 -- | | | |
61 -- | -----------| |
62 -- | |<--------
63 -- | v
64 -- | query?---------- yes ---------
65 -- | | |
66 -- | no |
67 -- | | |
68 -- | v v
69 -- | ------- -------------
70 -- |----------->| execute | ->| define_column |
71 -- | ------- | -------------
72 -- | |------------ | |
73 -- | | | ----------|
74 -- | v | v
75 -- | -------------- | -------
76 -- | ->| variable_value | | ------>| execute |
77 -- | | -------------- | | -------
78 -- | | | | | |
79 -- | ----------| | | |
80 -- | | | | v
81 -- | | | | ----------
82 -- | |<----------- |----->| fetch_rows |
83 -- | | | ----------
84 -- | | | |
85 -- | | | v
86 -- | | | --------------------
87 -- | | | | column_value |
88 -- | | | | variable_value |
89 -- | | | ---------------------
90 -- | | | |
91 -- | |<--------------------------
92 -- | |
93 -- -----------------|
94 -- |
95 -- v
96 -- ------------
97 -- | close_cursor |
98 -- ------------
99 --
100 --
101 -- A SET ROLE statement has its effect during execute. SET ROLE is
102 -- only permitted if at the moments of the calls to both parse and
103 -- execute there are no definer's rights units nor SQL DML or
104 -- query statements on the callstack, and if the currently-enabled
105 -- roles at the moment of the call to execute are exactly the same
106 -- as they were at the moment of the call to parse.
107 --
108 --
109 ---------------
110
111 -------------
112 -- CONSTANTS
113 --
114 v6 constant integer := 0;
115 native constant integer := 1;
116 v7 constant integer := 2;
117 foreign_syntax constant integer := 4294967295;
118 --
119
120 -- TYPES
121 --
122 type varchar2a is table of varchar2(32767) index by binary_integer;
123 -- bug 2410688: for users who require larger than varchar2(256),
124 -- this type has been introduced together with parse overloads
125 -- that take this type.
126 type varchar2s is table of varchar2(256) index by binary_integer;
127 -- Note that with the introduction of varchar2a we will deprecate
128 -- this type, with phase out over a number of releases.
129 -- For DateTime types, the field col_scale is used to denote the
130 -- fractional seconds precision.
131 -- For Interval types, the field col_precision is used to denote
132 -- the leading field precision and the field col_scale is used to
133 -- denote the fractional seconds precision.
134 type desc_rec is record (
135 col_type binary_integer := 0,
136 col_max_len binary_integer := 0,
137 col_name varchar2(32) := '',
138 col_name_len binary_integer := 0,
139 col_schema_name varchar2(32) := '',
140 col_schema_name_len binary_integer := 0,
141 col_precision binary_integer := 0,
142 col_scale binary_integer := 0,
143 col_charsetid binary_integer := 0,
144 col_charsetform binary_integer := 0,
145 col_null_ok boolean := TRUE);
146 type desc_tab is table of desc_rec index by binary_integer;
147 -- bug 702903 reveals that col_name can be of any length, not just 32 which
148 -- can be resolved by changing the maximum size above from 32 to 32767.
149 -- However, this will affect the signature of the package and to avoid that
150 -- side effect, the current API describe_columns is left unchanged but a new
151 -- API describe_columns2 is added at the end of this package specification.
152 -- The new API relies on a table type desc_tab2 whose array element is a new
153 -- record type desc_rec2, and desc_rec2 contains the variable col_name with a
154 -- maximum size of 32,767.
155 -- If the original API describe_columns is used and col_name encounters an
156 -- overflow, an error will be raised.
157 type desc_rec2 is record (
158 col_type binary_integer := 0,
159 col_max_len binary_integer := 0,
160 col_name varchar2(32767) := '',
161 col_name_len binary_integer := 0,
162 col_schema_name varchar2(32) := '',
163 col_schema_name_len binary_integer := 0,
164 col_precision binary_integer := 0,
165 col_scale binary_integer := 0,
166 col_charsetid binary_integer := 0,
167 col_charsetform binary_integer := 0,
168 col_null_ok boolean := TRUE);
169 type desc_tab2 is table of desc_rec2 index by binary_integer;
170
171 type desc_rec3 is record (
172 col_type binary_integer := 0,
173 col_max_len binary_integer := 0,
174 col_name varchar2(32767) := '',
175 col_name_len binary_integer := 0,
176 col_schema_name varchar2(32) := '',
177 col_schema_name_len binary_integer := 0,
178 col_precision binary_integer := 0,
179 col_scale binary_integer := 0,
180 col_charsetid binary_integer := 0,
181 col_charsetform binary_integer := 0,
182 col_null_ok boolean := TRUE,
183 col_type_name varchar2(32) := '',
184 col_type_name_len binary_integer := 0);
185 type desc_tab3 is table of desc_rec3 index by binary_integer;
186
187 ------------
188 -- Bulk SQL Types
189 --
190 type Number_Table is table of number index by binary_integer;
191 type Varchar2_Table is table of varchar2(4000) index by binary_integer;
192 type Date_Table is table of date index by binary_integer;
193 type Blob_Table is table of Blob index by binary_integer;
194 type Clob_Table is table of Clob index by binary_integer;
195
196 $if utl_ident.is_oracle_server $then
197 type Bfile_Table is table of Bfile index by binary_integer;
198 $else
199 /* BFILE datatypes are not supported */
200 $end
201
202 $if utl_ident.is_oracle_server $then
203 TYPE Urowid_Table IS TABLE OF urowid INDEX BY binary_integer;
204 $else
205 /* urowid is not supported in this environment */
206 $end
207
208 TYPE time_Table IS TABLE OF time_unconstrained INDEX BY binary_integer;
209 TYPE timestamp_Table IS TABLE OF timestamp_unconstrained INDEX BY binary_integer;
210
211 $if utl_ident.is_oracle_server $then
212 TYPE time_with_time_zone_Table IS TABLE OF TIME_TZ_UNCONSTRAINED INDEX BY binary_integer;
213 TYPE timestamp_with_time_zone_Table IS TABLE OF
214 TIMESTAMP_TZ_UNCONSTRAINED INDEX BY binary_integer;
215 TYPE timestamp_with_ltz_Table IS TABLE OF
216 TIMESTAMP_LTZ_UNCONSTRAINED INDEX BY binary_integer;
217 $else
218 /* time zone features not supported in this environment */
219 $end
220
221 TYPE interval_year_to_MONTH_Table IS TABLE OF
222 yminterval_unconstrained INDEX BY binary_integer;
223 TYPE interval_day_to_second_Table IS TABLE OF
224 dsinterval_unconstrained INDEX BY binary_integer;
225
226 type Binary_Float_Table is table of binary_float index by binary_integer;
227 type Binary_Double_Table is table of binary_double index by binary_integer;
228
229 -- type Cfile_Table is table of Cfile index by binary_integer;
230 --------------
231 -- EXCEPTIONS
232 --
233 inconsistent_type exception;
234 pragma exception_init(inconsistent_type, -6562);
235 -- This exception is raised by procedure "column_value" or
236 -- "variable_value" if the type of the given out argument where
237 -- to put the requested value is different from the type of the value.
238
239 ----------------------------
240 -- PROCEDURES AND FUNCTIONS
241 --
242 function open_cursor return integer;
243 pragma restrict_references(open_cursor,RNDS,WNDS);
244 -- Open a new cursor.
245 -- When no longer needed, this cursor MUST BE CLOSED explicitly by
246 -- calling "close_cursor".
247 -- Return value:
248 -- Cursor id number of the new cursor.
249 --
250 function is_open(c in integer) return boolean;
251 pragma restrict_references(is_open,RNDS,WNDS);
252 -- Return TRUE is the given cursor is currently open.
253 -- Input parameters:
254 -- c
255 -- Cursor id number of the cursor to check.
256 -- Return value:
257 -- TRUE if the given cursor is open,
258 -- FALSE if it is not.
259 --
260 procedure close_cursor(c in out integer);
261 pragma restrict_references(close_cursor,RNDS,WNDS);
262 -- Close the given cursor.
263 -- Input parameters:
264 -- c
265 -- Cursor id number of the cursor to close.
266 -- Output parameters:
267 -- c
268 -- Will be nulled.
269 --
270
271 procedure parse(c in integer, statement in varchar2,
272 language_flag in integer);
273 procedure parse(c in integer, statement in varchar2a,
274 lb in integer, ub in integer,
275 lfflg in boolean, language_flag in integer);
276 procedure parse(c in integer, statement in varchar2s,
277 lb in integer, ub in integer,
278 lfflg in boolean, language_flag in integer);
279 -- Parse the given statement in the given cursor. NOTE THAT PARSING AND
280 -- EXECUTING DDL STATEMENTS CAN CAUSE HANGS!
281 -- Currently, the deferred parsing feature of the Oracle Call Interface
282 -- is not used. As a result, statements are parsed immediately. In addition,
283 -- DDL statements are executed immediately when parsed. However,
284 -- the behavior may change in the future so that the actual parsing
285 -- (and execution of DDL statement) do not occur until the cursor is
286 -- executed with "execute".
287 -- DO NOT RELY ON THE CURRENT TIMING OF THE ACTUAL PARSING!
288 -- Input parameters:
289 -- c
290 -- Cursor id number of the cursor in where to parse the statement.
291 --
292 -- statement
293 -- Statement to parse.
294 -- Supported statement types are: varchar2, clob, varchar2a, varchar2s
295 --
296 -- For varchar2a and varchar2s statement types, the statement is not in
297 -- one piece but resides in little pieces in the PL/SQL table
298 -- "statement".
299 -- Conceptually what happens is that the SQL string is put together as
300 -- follows:
301 -- String := statement(lb) || statement(lb + 1) || ... || statement(ub);
302 -- Then a regular parse follows.
303 -- If "lfflg" is TRUE then a newline is inserted after each piece.
304 --
305 -- lb
306 -- Lower bound for elements in the varchar2a/varchar2s statement type.
307 --
308 -- ub
309 -- Upper bound for elements in the varchar2a/varchar2s statement type.
310 --
311 -- lfflg
312 -- Line Feed flag for the varchar2a/varchar2s statement type.
313 -- If TRUE, then insert a linefeed after each element on concatenation.
314 --
315 -- language_flag
316 -- Specifies behavior for statement. Valid values are v6, v7 and NATIVE.
317 -- v6 and v7 specifies behavior according to Version 6 and ORACLE7,
318 -- respectively. NATIVE specifies behavior according to the version
319 -- of the database the program is connected to.
320 --
321 -- edition
322 -- Specifies the edition to run the statement in. Passing NULL
323 -- indicates the statement should to run in the caller's
324 -- current edition.
325 --
326 -- If the edition is specified with a non-NULL value, the user
327 -- with which the statement is to be executed must have USE
328 -- privilege on the named edition.
329 --
330 -- apply_crossedition_trigger
331 -- Specifies the unqualified name of a crossedition trigger
332 -- that is to be applied to the specified SQL. The name is
333 -- resolved using the edition and current_schema setting in
334 -- which the statement is to be executed. The trigger must be
335 -- owned by the user that will execute the statement.
336 --
337 -- If a non-NULL value is specified, the specified crossedition
338 -- trigger will be executed assuming fire_apply_trigger is
339 -- TRUE, the trigger is enabled, the trigger is defined on the
340 -- table which is the target of the statement, the type of the
341 -- statement matches the trigger's dml_event_clause, any
342 -- effective WHEN and UPDATE OF restrictions are satisfied,
343 -- etc.
344 --
348 -- as if the DML being parsed was issued from the body of the
345 -- Other triggers may also be executed, chosen according to
346 -- special rules that apply when DML is issued from within the
347 -- body of a crossedition trigger, in this circumstance chosen
349 -- trigger named in this parameter. For a forward crossedition
350 -- trigger, only other forward crossedition triggers that
351 -- directly and indirectly specify that they "FOLLOWS" the
352 -- specified trigger, or are installed as part of later
353 -- editions, will be executed. For a reverse crossedition
354 -- trigger, only other reverse crossedition triggers that
355 -- directly and indirectly specify that they "PRECEDES" the
356 -- specified trigger, or are installed as part of earlier
357 -- editions, will be executed. Non-crossedition triggers owned
358 -- by the same user as the crossedition trigger will not be
359 -- executed; an ORA-25034 will result if any non-crossedition
360 -- trigger owned by a user other than the owner of the
361 -- specified crossedition trigger exists for the target table.
362 --
363 -- It is expected that this parameter will be specified in
364 -- two situations:
365 --
366 -- 1) To apply a forward crossedition trigger to pre-existing
367 -- data, either through direct use of DBMS_SQL or via some
368 -- further package layer such as DBMS_PARALLEL_EXECUTE.
369 --
370 -- 2) When a crossedition trigger wishes to itself execute
371 -- a statement via DBMS_SQL, and desires that the special
372 -- trigger selection rules discussed above should be used.
373 -- These special trigger selection rules are used
374 -- automatically when statically-embedded or
375 -- native-dynamic SQL is issued directly from the body of
376 -- a crossedition trigger, but since DBMS_SQL is itself a
377 -- package outside the trigger's own body, these rules
378 -- will not apply for DBMS_SQL executions unless this
379 -- parameter is used to request them. For this usage, the
380 -- crossedition trigger should specify its own name in
381 -- this parameter to cause the selection of other
382 -- crossedition triggers according to the proper FOLLOWS
383 -- or PRECEDES relationships.
384 --
385 -- fire_apply_trigger
386 -- Indicates whether the specified apply_crossedition_trigger
387 -- is itself to be executed, or should only be a guide used in
388 -- selecting other triggers. This is typically set FALSE when
389 -- the statement is a replacement for the actions the
390 -- apply_crossedition_trigger would itself perform. If FALSE,
391 -- the specified trigger is not executed, but other triggers
392 -- are still selected for firing as if the specified trigger
393 -- was doing a DML to the table that is the target of the
394 -- statement.
395 --
396 -- The apply_crossedition_trigger and fire_apply_trigger
397 -- parameters are error checked but are not otherwise
398 -- meaningful when the statement being parsed is not a DML.
399 --
400 -- schema
401 -- Specifies the schema to parse the statement with.
402 --
403 -- container
404 -- Specifies the container to parse the statement with.
405 -- If null or unspecified, the calling container is the target container
406 -- and no container switch is performed. If a valid container name is
407 -- specified, the current user must be a common user with SET CONTAINER
408 -- privilege to switch to the target container.
409 --
410 -- If container switched, the default logon roles are enabled.
411 --
412 -- NOTE:
413 -- The contents of the edition, apply_crossedition_trigger, schema, and
414 -- container parameter values are processed as a SQL identifier;
415 -- double-quotes must surround the remainder of the string if special
416 -- characters or lower case characters are present in the parameter's
417 -- actual name, and if double-quotes are not used the contents will be
418 -- uppercased.
419 --
420
421 procedure bind_variable(c in integer, name in varchar2, value in number);
422 pragma restrict_references(bind_variable,WNDS);
423 procedure bind_variable(c in integer, name in varchar2,
424 value in varchar2 character set any_cs);
425 pragma restrict_references(bind_variable,WNDS);
426 procedure bind_variable(c in integer, name in varchar2,
427 value in varchar2 character set any_cs,
428 out_value_size in integer);
429 pragma restrict_references(bind_variable,WNDS);
430 procedure bind_variable(c in integer, name in varchar2, value in date);
431 pragma restrict_references(bind_variable,WNDS);
432
433 procedure bind_variable(c in integer, name in varchar2, value in blob);
434 pragma restrict_references(bind_variable,WNDS);
435 procedure bind_variable(c in integer, name in varchar2,
436 value in clob character set any_cs);
437 pragma restrict_references(bind_variable,WNDS);
438
439 $if utl_ident.is_oracle_server $then
440 procedure bind_variable(c in integer, name in varchar2, value in bfile);
441 pragma restrict_references(bind_variable,WNDS);
442 $else
443 /* BFILE overloads are not supported */
444 $end
445
446 procedure bind_variable_char(c in integer, name in varchar2,
450 value in char character set any_cs,
447 value in char character set any_cs);
448 pragma restrict_references(bind_variable_char,WNDS);
449 procedure bind_variable_char(c in integer, name in varchar2,
451 out_value_size in integer);
452 pragma restrict_references(bind_variable_char,WNDS);
453 procedure bind_variable_raw(c in integer, name in varchar2,
454 value in raw);
455 pragma restrict_references(bind_variable_raw,WNDS);
456 procedure bind_variable_raw(c in integer, name in varchar2,
457 value in raw, out_value_size in integer);
458 pragma restrict_references(bind_variable_raw,WNDS);
459 procedure bind_variable_rowid(c in integer, name in varchar2,
460 value in rowid);
461 pragma restrict_references(bind_variable_rowid,WNDS);
462 procedure bind_array(c in integer, name in varchar2,
463 n_tab in Number_Table);
464 pragma restrict_references(bind_array,WNDS);
465 procedure bind_array(c in integer, name in varchar2,
466 c_tab in Varchar2_Table);
467 pragma restrict_references(bind_array,WNDS);
468 procedure bind_array(c in integer, name in varchar2,
469 d_tab in Date_Table);
470 pragma restrict_references(bind_array,WNDS);
471
472 procedure bind_array(c in integer, name in varchar2,
473 bl_tab in Blob_Table);
474 pragma restrict_references(bind_array,WNDS);
475 procedure bind_array(c in integer, name in varchar2,
476 cl_tab in Clob_Table);
477 pragma restrict_references(bind_array,WNDS);
478
479 $if utl_ident.is_oracle_server $then
480 procedure bind_array(c in integer, name in varchar2,
481 bf_tab in Bfile_Table);
482 pragma restrict_references(bind_array,WNDS);
483 $else
484 /* BFILE overloads are not supported */
485 $end
486
487 procedure bind_array(c in integer, name in varchar2,
488 n_tab in Number_Table,
489 index1 in integer, index2 in integer);
490 pragma restrict_references(bind_array,WNDS);
491 procedure bind_array(c in integer, name in varchar2,
492 c_tab in Varchar2_Table,
493 index1 in integer, index2 in integer);
494 pragma restrict_references(bind_array,WNDS);
495 procedure bind_array(c in integer, name in varchar2,
496 d_tab in Date_Table,
497 index1 in integer, index2 in integer);
498 pragma restrict_references(bind_array,WNDS);
499
500 procedure bind_array(c in integer, name in varchar2,
501 bl_tab in Blob_Table,
502 index1 in integer, index2 in integer);
503 pragma restrict_references(bind_array,WNDS);
504 procedure bind_array(c in integer, name in varchar2,
505 cl_tab in Clob_Table,
506 index1 in integer, index2 in integer);
507 pragma restrict_references(bind_array,WNDS);
508
509 $if utl_ident.is_oracle_server $then
510 procedure bind_array(c in integer, name in varchar2,
511 bf_tab in Bfile_Table,
512 index1 in integer, index2 in integer);
513 pragma restrict_references(bind_array,WNDS);
514 $else
515 /* BFILE overloads are not supported */
516 $end
517
518 -- Bind the given value to the variable identified by its name
519 -- in the parsed statement in the given cursor.
520 -- If the variable is an in or in/out variable, the given bind value
521 -- should be a valid one. If the variable is an out variable, the
522 -- given bind value is ignored.
523 -- Input parameters:
524 -- c
525 -- Cursor id number of the cursor to bind.
526 -- name
527 -- Name of the variable in the statement.
528 -- value
529 -- Value to bind to the variable in the cursor.
530 -- If the variable is an out or in/out variable, its type is the same
531 -- as the type of the value being passed in for this parameter.
532 -- out_value_size
533 -- Maximum expected out value size in bytes for the varchar2
534 -- out or in/out variable. If it is not given for the varchar2
535 -- out or in/out variable, the size is the length of the current
536 -- "value".
537 -- n_tab, c_tab, d_tab, bl_tab, cl_tab, bf_tab
538 -- For array execute operations, where the user wishes to execute
539 -- the SQL statement multiple times without returning control to
540 -- the caller, a list of values can be bound to this variable. This
541 -- functionality is like the array execute feature of OCI, where a
542 -- list of values in a PLSQL index table can be inserted into a SQL
543 -- table with a single (parameterized) call to execute.
544 -- index1, index2
545 -- For array execute, instead of using the entire index table, the
546 -- user may chose to limit it to a range of values.
547 --
548
549 procedure define_column(c in integer, position in integer, column in number);
550 pragma restrict_references(define_column,RNDS,WNDS);
551 procedure define_column(c in integer, position in integer,
552 column in varchar2 character set any_cs,
553 column_size in integer);
554 pragma restrict_references(define_column,RNDS,WNDS);
555 procedure define_column(c in integer, position in integer, column in date);
559 pragma restrict_references(define_column,RNDS,WNDS);
556 pragma restrict_references(define_column,RNDS,WNDS);
557
558 procedure define_column(c in integer, position in integer, column in blob);
560 procedure define_column(c in integer, position in integer,
561 column in clob character set any_cs);
562 pragma restrict_references(define_column,RNDS,WNDS);
563
564 $if utl_ident.is_oracle_server $then
565 procedure define_column(c in integer, position in integer, column in bfile);
566 pragma restrict_references(define_column,RNDS,WNDS);
567 $else
568 /* BFILE overloads are not supported */
569 $end
570
571 procedure define_column_char(c in integer, position in integer,
572 column in char character set any_cs,
573 column_size in integer);
574 pragma restrict_references(define_column_char,RNDS,WNDS);
575 procedure define_column_raw(c in integer, position in integer,
576 column in raw,
577 column_size in integer);
578 pragma restrict_references(define_column_raw,RNDS,WNDS);
579 procedure define_column_rowid(c in integer, position in integer,
580 column in rowid);
581 pragma restrict_references(define_column_rowid,RNDS,WNDS);
582 procedure define_array(c in integer, position in integer,
583 n_tab in Number_Table,
584 cnt in integer, lower_bound in integer);
585 pragma restrict_references(define_array,RNDS,WNDS);
586 procedure define_array(c in integer, position in integer,
587 c_tab in Varchar2_Table,
588 cnt in integer, lower_bound in integer);
589 pragma restrict_references(define_array,RNDS,WNDS);
590 procedure define_array(c in integer, position in integer,
591 d_tab in Date_Table,
592 cnt in integer, lower_bound in integer);
593 pragma restrict_references(define_array,RNDS,WNDS);
594
595 procedure define_array(c in integer, position in integer,
596 bl_tab in Blob_Table,
597 cnt in integer, lower_bound in integer);
598 pragma restrict_references(define_array,RNDS,WNDS);
599 procedure define_array(c in integer, position in integer,
600 cl_tab in Clob_Table,
601 cnt in integer, lower_bound in integer);
602 pragma restrict_references(define_array,RNDS,WNDS);
603
604 $if utl_ident.is_oracle_server $then
605 procedure define_array(c in integer, position in integer,
606 bf_tab in Bfile_Table,
607 cnt in integer, lower_bound in integer);
608 pragma restrict_references(define_array,RNDS,WNDS);
609 $else
610 /* BFILE overloads are not supported */
611 $end
612
613 -- Define a column to be selected from the given cursor; so this
614 -- procedure is applicable only to SELECT cursors.
615 -- The column being defined is identified by its relative position as
616 -- it appears on the select list in the statement in the given cursor.
617 -- The type of the column to be defined is the type of the value
618 -- being passed in for parameter "column".
619 -- Input parameters:
620 -- c
621 -- Cursor id number of the cursor to define the row to be selected.
622 -- position
623 -- Position of the column in the row being defined.
624 -- column
625 -- Type of the value being passed in for this parameter is
626 -- the type of the column to be defined.
627 -- column_size
628 -- Maximum expected size of the value in bytes for the
629 -- varchar2 column.
630 --
631 function execute(c in integer) return integer;
632 -- Execute the given cursor and return the number of rows processed
633 -- (valid and meaningful only for INSERT, DELETE or UPDATE statements;
634 -- for other types of statements, the return value is undefined and
635 -- should be ignored).
636 -- Input parameters:
637 -- c
638 -- Cursor id number of the cursor to execute.
639 -- Return value:
640 -- Number of rows processed if the statement in the cursor is
641 -- either an INSERT, DELETE or UPDATE statement or undefined otherwise.
642 --
643 function fetch_rows(c in integer) return integer;
644 pragma restrict_references(fetch_rows,WNDS);
645 -- Fetch rows from the given cursor. The function tries to fetch a
646 -- row. As long as "fetch_rows" is able to fetch a
647 -- row, it can be called repeatedly to fetch additional rows. If no
648 -- row was actually fetched, "fetch_rows"
649 -- cannot be called to fetch additional rows.
650 -- Input parameters:
651 -- c
652 -- Cursor id number of the cursor to fetch.
653 -- Return value:
654 -- The number of rows actually fetched.
655 --
656 function execute_and_fetch(c in integer, exact in boolean default false)
657 return integer;
658 pragma restrict_references(execute_and_fetch,WNDS);
659 -- Execute the given cursor and fetch rows. Gives the same functionality
660 -- as a call to "execute"
661 -- followed by a call to "fetch_rows". However, this function can
662 -- potentially cut down on the number of message round-trips compared to
663 -- calling "execute" and "fetch_rows" separately.
664 -- Input parameters:
665 -- c
666 -- Cursor id number of the cursor to execute and fetch.
667 -- exact
671 -- The number of rows actually fetched.
668 -- Raise an exception if the number of rows matching the query
669 -- differs from one.
670 -- Return value:
672 --
673 procedure column_value(c in integer, position in integer, value out number);
674 pragma restrict_references(column_value,RNDS,WNDS);
675 procedure column_value(c in integer, position in integer,
676 value out varchar2 character set any_cs);
677 pragma restrict_references(column_value,RNDS,WNDS);
678 procedure column_value(c in integer, position in integer, value out date);
679 pragma restrict_references(column_value,RNDS,WNDS);
680
681 procedure column_value(c in integer, position in integer, value out blob);
682 pragma restrict_references(column_value,RNDS,WNDS);
683 procedure column_value(c in integer, position in integer,
684 value out clob character set any_cs);
685 pragma restrict_references(column_value,RNDS,WNDS);
686
687 $if utl_ident.is_oracle_server $then
688 procedure column_value(c in integer, position in integer, value out bfile);
689 pragma restrict_references(column_value,RNDS,WNDS);
690 $else
691 /* BFILE overloads are not supported */
692 $end
693
694 procedure column_value_char(c in integer, position in integer,
695 value out char character set any_cs);
696 pragma restrict_references(column_value_char,RNDS,WNDS);
697 procedure column_value_raw(c in integer, position in integer, value out raw);
698 pragma restrict_references(column_value_raw,RNDS,WNDS);
699 procedure column_value_rowid(c in integer, position in integer,
700 value out rowid);
701 pragma restrict_references(column_value_rowid,RNDS,WNDS);
702 procedure column_value(c in integer, position in integer, value out number,
703 column_error out number, actual_length out integer);
704 pragma restrict_references(column_value,RNDS,WNDS);
705 procedure column_value(c in integer, position in integer,
706 value out varchar2 character set any_cs,
707 column_error out number, actual_length out integer);
708 pragma restrict_references(column_value,RNDS,WNDS);
709 procedure column_value(c in integer, position in integer, value out date,
710 column_error out number, actual_length out integer);
711 pragma restrict_references(column_value,RNDS,WNDS);
712 procedure column_value_char(c in integer, position in integer,
713 value out char character set any_cs,
714 column_error out number,
715 actual_length out integer);
716 pragma restrict_references(column_value_char,RNDS,WNDS);
717 procedure column_value_raw(c in integer, position in integer, value out raw,
718 column_error out number,
719 actual_length out integer);
720 pragma restrict_references(column_value_raw,RNDS,WNDS);
721 procedure column_value_rowid(c in integer, position in integer,
722 value out rowid, column_error out number,
723 actual_length out integer);
724 pragma restrict_references(column_value_rowid,RNDS,WNDS);
725
726 procedure column_value(c in integer, position in integer,
727 n_tab in out nocopy Number_table);
728 pragma restrict_references(column_value,RNDS,WNDS);
729 procedure column_value(c in integer, position in integer,
730 c_tab in out nocopy Varchar2_table);
731 pragma restrict_references(column_value,RNDS,WNDS);
732 procedure column_value(c in integer, position in integer,
733 d_tab in out nocopy Date_table);
734 pragma restrict_references(column_value,RNDS,WNDS);
735
736 procedure column_value(c in integer, position in integer,
737 bl_tab in out nocopy Blob_table);
738 pragma restrict_references(column_value,RNDS,WNDS);
739 procedure column_value(c in integer, position in integer,
740 cl_tab in out nocopy Clob_table);
741 pragma restrict_references(column_value,RNDS,WNDS);
742
743 $if utl_ident.is_oracle_server $then
744 procedure column_value(c in integer, position in integer,
745 bf_tab in out nocopy Bfile_table);
746 pragma restrict_references(column_value,RNDS,WNDS);
747 $else
748 /* BFILE overloads are not supported */
749 $end
750
751 -- Get a value of the column identified by the given position
752 -- and the given cursor. This procedure is used to access the data
753 -- retrieved by "fetch_rows".
754 -- Input parameters:
755 -- c
756 -- Cursor id number of the cursor from which to get the value.
757 -- position
758 -- Position of the column of which to get the value.
759 -- Output parameters:
760 -- value
761 -- Value of the column.
762 -- column_error
763 -- Any column error code associated with "value".
764 -- actual_length
765 -- The actual length of "value" in the table before any truncation
766 -- during the fetch.
767 -- Exceptions:
768 -- inconsistent_type (ORA-06562)
769 -- Raised if the type of the given out parameter "value" is
770 -- different from the actual type of the value. This type was
771 -- the given type when the column was defined by calling procedure
772 -- "define_column".
773 -- NOTES:
777 -- if on the "define_array" a batch size (i.e. the "cnt" parameter)
774 -- value parameter is an "IN OUT NOCOPY" parameter for bulk operations.
775 -- For bulk operations, "column_value" appends the new elements
776 -- at the appropriate (implicitly maintained) index. For instance
778 -- of 10 rows was specified and a start index (lower_bound) of
779 -- 1 was specified, then the first call to "column_value" after
780 -- the "fetch_rows" will populate elements at index 1..10, and
781 -- the next call will populate elements 11..20, and so on.
782 --
783 procedure variable_value(c in integer, name in varchar2,
784 value out number);
785 pragma restrict_references(variable_value,RNDS,WNDS);
786 procedure variable_value(c in integer, name in varchar2,
787 value out varchar2 character set any_cs);
788 pragma restrict_references(variable_value,RNDS,WNDS);
789 procedure variable_value(c in integer, name in varchar2,
790 value out date);
791 pragma restrict_references(variable_value,RNDS,WNDS);
792
793 procedure variable_value(c in integer, name in varchar2, value out blob);
794 pragma restrict_references(variable_value,RNDS,WNDS);
795 procedure variable_value(c in integer, name in varchar2,
796 value out clob character set any_cs);
797 pragma restrict_references(variable_value,RNDS,WNDS);
798
799 $if utl_ident.is_oracle_server $then
800 procedure variable_value(c in integer, name in varchar2, value out bfile);
801 pragma restrict_references(variable_value,RNDS,WNDS);
802 $else
803 /* BFILE overloads are not supported */
804 $end
805
806 procedure variable_value(c in integer, name in varchar2,
807 value out nocopy Number_table);
808 pragma restrict_references(variable_value,RNDS,WNDS);
809 procedure variable_value(c in integer, name in varchar2,
810 value out nocopy Varchar2_table);
811 pragma restrict_references(variable_value,RNDS,WNDS);
812 procedure variable_value(c in integer, name in varchar2,
813 value out nocopy Date_table);
814 pragma restrict_references(variable_value,RNDS,WNDS);
815
816 procedure variable_value(c in integer, name in varchar2,
817 value out nocopy Blob_table);
818 pragma restrict_references(variable_value,RNDS,WNDS);
819 procedure variable_value(c in integer, name in varchar2,
820 value out nocopy Clob_table);
821 pragma restrict_references(variable_value,RNDS,WNDS);
822
823 $if utl_ident.is_oracle_server $then
824 procedure variable_value(c in integer, name in varchar2,
825 value out nocopy Bfile_table);
826 pragma restrict_references(variable_value,RNDS,WNDS);
827 $else
828 /* BFILE overloads are not supported */
829 $end
830
831 procedure variable_value_char(c in integer, name in varchar2,
832 value out char character set any_cs);
833 pragma restrict_references(variable_value_char,RNDS,WNDS);
834 procedure variable_value_raw(c in integer, name in varchar2,
835 value out raw);
836 pragma restrict_references(variable_value_raw,RNDS,WNDS);
837 procedure variable_value_rowid(c in integer, name in varchar2,
838 value out rowid);
839 pragma restrict_references(variable_value_rowid,RNDS,WNDS);
840
841 -- Get a value or values of the variable identified by the name
842 -- and the given cursor.
843 -- Input parameters:
844 -- c
845 -- Cursor id number of the cursor from which to get the value.
846 -- name
847 -- Name of the variable of which to get the value.
848 -- Output parameters:
849 -- value
850 -- Value of the variable.
851 -- Exceptions:
852 -- inconsistent_type (ORA-06562)
853 -- Raised if the type of the given out parameter "value" is
854 -- different from the actual type of the value. This type was
855 -- the given type when the variable was bound by calling procedure
856 -- "bind_variable".
857 -- NOTES:
858 -- For bulk operations, value parameter is an "OUT NOCOPY" parameter.
859 --
860 function last_error_position return integer;
861 pragma restrict_references(last_error_position,RNDS,WNDS);
862 function last_sql_function_code return integer;
863 pragma restrict_references(last_sql_function_code,RNDS,WNDS);
864 function last_row_count return integer;
865 pragma restrict_references(last_row_count,RNDS,WNDS);
866 function last_row_id return rowid;
867 pragma restrict_references(last_row_id,RNDS,WNDS);
868 -- Get various information for the last-operated cursor in the session.
869 -- To ensure that the information relates to a particular cursor,
870 -- the functions should be called after an operation on that cursor and
871 -- before any other operation on any other cursor.
872 -- Return value:
873 -- last_error_position
874 -- Relative position in the statement when the error occurs.
875 -- last_sql_function_code
876 -- SQL function code of the statement. See list in OCI manual.
877 -- last_row_count
878 -- Cumulative count of rows fetched.
879 -- last_row_id
880 -- Rowid of the last processed row.
881 --
882 ------------
883 -- EXAMPLES
884 --
885 -- create or replace procedure copy(source in varchar2,
886 -- destination in varchar2) is
890 -- -- - ID of type NUMBER,
887 -- -- This procedure copies rows from a given source table to
888 -- a given destination table assuming that both source and destination
889 -- -- tables have the following columns:
891 -- -- - NAME of type VARCHAR2(30),
892 -- -- - BIRTHDATE of type DATE.
893 -- id number;
894 -- name varchar2(30);
895 -- birthdate date;
896 -- source_cursor integer;
897 -- destination_cursor integer;
898 -- rows_processed integer;
899 -- begin
900 -- -- prepare a cursor to select from the source table
901 -- source_cursor := dbms_sql.open_cursor;
902 -- dbms_sql.parse(source_cursor,
903 -- 'select id, name, birthdate from ' || source,
904 -- dbms_sql.native);
905 -- dbms_sql.define_column(source_cursor, 1, id);
906 -- dbms_sql.define_column(source_cursor, 2, name, 30);
907 -- dbms_sql.define_column(source_cursor, 3, birthdate);
908 -- rows_processed := dbms_sql.execute(source_cursor);
909 --
910 -- -- prepare a cursor to insert into the destination table
911 -- destination_cursor := dbms_sql.open_cursor;
912 -- dbms_sql.parse(destination_cursor,
913 -- 'insert into ' || destination ||
914 -- ' values (:id, :name, :birthdate)',
915 -- dbms_sql.native);
916 --
917 -- -- fetch a row from the source table and
918 -- -- insert it into the destination table
919 -- loop
920 -- if dbms_sql.fetch_rows(source_cursor)>0 then
921 -- -- get column values of the row
922 -- dbms_sql.column_value(source_cursor, 1, id);
923 -- dbms_sql.column_value(source_cursor, 2, name);
924 -- dbms_sql.column_value(source_cursor, 3, birthdate);
925 -- -- bind the row into the cursor which insert
926 -- -- into the destination table
927 -- dbms_sql.bind_variable(destination_cursor, 'id', id);
928 -- dbms_sql.bind_variable(destination_cursor, 'name', name);
929 -- dbms_sql.bind_variable(destination_cursor, 'birthdate', birthdate);
930 -- rows_processed := dbms_sql.execute(destination_cursor);
931 -- else
932 -- -- no more row to copy
933 -- exit;
934 -- end if;
935 -- end loop;
936 --
937 -- -- commit and close all cursors
938 -- commit;
939 -- dbms_sql.close_cursor(source_cursor);
940 -- dbms_sql.close_cursor(destination_cursor);
941 -- exception
942 -- when others then
943 -- if dbms_sql.is_open(source_cursor) then
944 -- dbms_sql.close_cursor(source_cursor);
945 -- end if;
946 -- if dbms_sql.is_open(destination_cursor) then
947 -- dbms_sql.close_cursor(destination_cursor);
948 -- end if;
949 -- raise;
950 -- end;
951 --
952 procedure column_value_long(c in integer, position in integer,
953 length in integer, offset in integer,
954 value out varchar2, value_length out integer);
955 pragma restrict_references(column_value_long,RNDS,WNDS);
956 -- Get (part of) the value of a long column.
957 -- Input parameters:
958 -- c
959 -- Cursor id number of the cursor from which to get the value.
960 -- position
961 -- Position of the column of which to get the value.
962 -- length
963 -- Number of bytes of the long value to fetch.
964 -- offset
965 -- Offset into the long field for start of fetch.
966 -- Output parameters:
967 -- value
968 -- Value of the column as a varchar2.
969 -- value_length
970 -- The number of bytes actually returned in value.
971 --
972 procedure define_column_long(c in integer, position in integer);
973 pragma restrict_references(define_column_long,RNDS,WNDS);
974 -- Define a column to be selected from the given cursor; so this
975 -- procedure is applicable only to SELECT cursors.
976 -- The column being defined is identified by its relative position as
977 -- it appears on the select list in the statement in the given cursor.
978 -- The type of the column to be defined is the type LONG.
979 -- Input parameters:
980 -- c
981 -- Cursor id number of the cursor to define the row to be selected.
982 -- position
983 -- Position of the column in the row being defined.
984 --
985
986 procedure describe_columns(c in integer, col_cnt out integer,
987 desc_t out desc_tab);
988 pragma restrict_references(describe_columns,WNDS);
989 -- Get the description for the specified column.
990 -- Input parameters:
991 -- c
992 -- Cursor id number of the cursor from which to describe the column.
993 -- Output Parameters:
994 -- col_cnt
995 -- The number of columns in the select list of the query.
996 -- desc_tab
997 -- The describe table to fill in with the description of each of the
998 -- columns of the query. This table is indexed from one to the number
999 -- of elements in the select list of the query.
1000 --
1001 -- Urowid support
1002 $if utl_ident.is_oracle_server $then
1003 procedure bind_variable(c in integer, name in varchar2,
1004 value in urowid);
1005
1006 pragma restrict_references(bind_variable,WNDS);
1007
1008 procedure define_column(c in integer, position in integer,
1012 procedure column_value(c in integer, position in integer,
1009 column in urowid);
1010 pragma restrict_references(define_column,RNDS,WNDS);
1011
1013 value out urowid);
1014 pragma restrict_references(column_value,RNDS,WNDS);
1015
1016 procedure variable_value(c in integer, name in varchar2,
1017 value out urowid);
1018 pragma restrict_references(variable_value,RNDS,WNDS);
1019
1020 procedure bind_array(c in integer, name in varchar2,
1021 ur_tab in Urowid_Table);
1022 pragma restrict_references(bind_array,WNDS);
1023
1024 procedure bind_array(c in integer, name in varchar2,
1025 ur_tab in Urowid_Table,
1026 index1 in integer, index2 in integer);
1027 pragma restrict_references(bind_array,WNDS);
1028
1029 procedure define_array(c in integer, position in integer,
1030 ur_tab in Urowid_Table,
1031 cnt in integer, lower_bound in integer);
1032 pragma restrict_references(define_array,RNDS,WNDS);
1033
1034 procedure column_value(c in integer, position in integer,
1035 ur_tab in out nocopy Urowid_table);
1036 pragma restrict_references(column_value,RNDS,WNDS);
1037
1038 procedure variable_value(c in integer, name in varchar2,
1039 value out nocopy Urowid_Table);
1040 pragma restrict_references(variable_value,RNDS,WNDS);
1041 $else
1042 /* urowid is not supported in this environment */
1043 $end
1044
1045 -- Datetime support
1046 -- time
1047 procedure bind_variable(c in integer, name in varchar2,
1048 value in time_unconstrained);
1049
1050 pragma restrict_references(bind_variable,WNDS);
1051
1052 procedure define_column(c in integer, position in integer,
1053 column in time_unconstrained);
1054
1055 pragma restrict_references(define_column,RNDS,WNDS);
1056
1057 procedure column_value(c in integer, position in integer,
1058 value out time_unconstrained);
1059
1060 pragma restrict_references(column_value,RNDS,WNDS);
1061
1062 procedure variable_value(c in integer, name in varchar2,
1063 value out time_unconstrained);
1064
1065 pragma restrict_references(variable_value,RNDS,WNDS);
1066
1067 procedure bind_array(c in integer, name in varchar2,
1068 tm_tab in Time_Table);
1069 pragma restrict_references(bind_array,WNDS);
1070
1071 procedure bind_array(c in integer, name in varchar2,
1072 tm_tab in Time_Table,
1073 index1 in integer, index2 in integer);
1074 pragma restrict_references(bind_array,WNDS);
1075
1076 procedure define_array(c in integer, position in integer,
1077 tm_tab in Time_Table,
1078 cnt in integer, lower_bound in integer);
1079 pragma restrict_references(define_array,RNDS,WNDS);
1080
1081 procedure column_value(c in integer, position in integer,
1082 tm_tab in out nocopy Time_table);
1083 pragma restrict_references(column_value,RNDS,WNDS);
1084
1085 procedure variable_value(c in integer, name in varchar2,
1086 value out nocopy Time_Table);
1087 pragma restrict_references(variable_value,RNDS,WNDS);
1088
1089 -- timestamp_unconstrained
1090 procedure bind_variable(c in integer, name in varchar2,
1091 value in timestamp_unconstrained);
1092
1093 pragma restrict_references(bind_variable,WNDS);
1094
1095 procedure define_column(c in integer, position in integer,
1096 column in timestamp_unconstrained);
1097
1098 pragma restrict_references(define_column,RNDS,WNDS);
1099
1100 procedure column_value(c in integer, position in integer,
1101 value out timestamp_unconstrained);
1102
1103 pragma restrict_references(column_value,RNDS,WNDS);
1104
1105 procedure variable_value(c in integer, name in varchar2,
1106 value out timestamp_unconstrained);
1107
1108 pragma restrict_references(variable_value,RNDS,WNDS);
1109
1110 procedure bind_array(c in integer, name in varchar2,
1111 tms_tab in Timestamp_Table);
1112 pragma restrict_references(bind_array,WNDS);
1113
1114 procedure bind_array(c in integer, name in varchar2,
1115 tms_tab in Timestamp_Table,
1116 index1 in integer, index2 in integer);
1117 pragma restrict_references(bind_array,WNDS);
1118
1119 procedure define_array(c in integer, position in integer,
1120 tms_tab in Timestamp_Table,
1121 cnt in integer, lower_bound in integer);
1122 pragma restrict_references(define_array,RNDS,WNDS);
1123
1124 procedure column_value(c in integer, position in integer,
1125 tms_tab in out nocopy Timestamp_table);
1126 pragma restrict_references(column_value,RNDS,WNDS);
1127
1128 procedure variable_value(c in integer, name in varchar2,
1129 value out nocopy Timestamp_Table);
1130 pragma restrict_references(variable_value,RNDS,WNDS);
1131
1132 -- time with timezone
1133 $if utl_ident.is_oracle_server $then
1134 procedure bind_variable(c in integer, name in varchar2,
1135 value in TIME_TZ_UNCONSTRAINED);
1136
1137 pragma restrict_references(bind_variable,WNDS);
1138
1142 pragma restrict_references(define_column,RNDS,WNDS);
1139 procedure define_column(c in integer, position in integer,
1140 column in TIME_TZ_UNCONSTRAINED);
1141
1143
1144 procedure column_value(c in integer, position in integer,
1145 value out TIME_TZ_UNCONSTRAINED);
1146
1147 pragma restrict_references(column_value,RNDS,WNDS);
1148
1149 procedure variable_value(c in integer, name in varchar2,
1150 value out TIME_TZ_UNCONSTRAINED );
1151
1152 pragma restrict_references(variable_value,RNDS,WNDS);
1153
1154 procedure bind_array(c in integer, name in varchar2,
1155 ttz_tab in Time_With_Time_Zone_Table);
1156 pragma restrict_references(bind_array,WNDS);
1157
1158 procedure bind_array(c in integer, name in varchar2,
1159 ttz_tab in Time_With_Time_Zone_Table,
1160 index1 in integer, index2 in integer);
1161 pragma restrict_references(bind_array,WNDS);
1162
1163 procedure define_array(c in integer, position in integer,
1164 ttz_tab in Time_With_Time_Zone_Table,
1165 cnt in integer, lower_bound in integer);
1166 pragma restrict_references(define_array,RNDS,WNDS);
1167
1168 procedure column_value(c in integer, position in integer,
1169 ttz_tab in out nocopy time_with_time_zone_table);
1170 pragma restrict_references(column_value,RNDS,WNDS);
1171
1172 procedure variable_value(c in integer, name in varchar2,
1173 value out nocopy Time_With_Time_Zone_Table);
1174
1175 pragma restrict_references(variable_value,RNDS,WNDS);
1176
1177 -- timestamp with timezone
1178 procedure bind_variable(c in integer, name in varchar2,
1179 value in TIMESTAMP_TZ_UNCONSTRAINED);
1180
1181 pragma restrict_references(bind_variable,WNDS);
1182
1183 procedure define_column(c in integer, position in integer,
1184 column in TIMESTAMP_TZ_UNCONSTRAINED);
1185
1186 pragma restrict_references(define_column,RNDS,WNDS);
1187
1188 procedure column_value(c in integer, position in integer,
1189 value out TIMESTAMP_TZ_UNCONSTRAINED);
1190
1191 pragma restrict_references(column_value,RNDS,WNDS);
1192
1193 procedure variable_value(c in integer, name in varchar2,
1194 value out TIMESTAMP_TZ_UNCONSTRAINED);
1195
1196 pragma restrict_references(variable_value,RNDS,WNDS);
1197
1198 procedure bind_array(c in integer, name in varchar2,
1199 tstz_tab in Timestamp_With_Time_Zone_Table);
1200 pragma restrict_references(bind_array,WNDS);
1201
1202 procedure bind_array(c in integer, name in varchar2,
1203 tstz_tab in Timestamp_With_Time_Zone_Table,
1204 index1 in integer, index2 in integer);
1205 pragma restrict_references(bind_array,WNDS);
1206
1207 procedure define_array(c in integer, position in integer,
1208 tstz_tab in Timestamp_With_Time_Zone_Table,
1209 cnt in integer, lower_bound in integer);
1210 pragma restrict_references(define_array,RNDS,WNDS);
1211
1212 procedure column_value(c in integer, position in integer,
1213 tstz_tab in out nocopy timestamp_with_time_zone_table);
1214 pragma restrict_references(column_value,RNDS,WNDS);
1215
1216 procedure variable_value(c in integer, name in varchar2,
1217 value out nocopy Timestamp_With_Time_Zone_Table);
1218 pragma restrict_references(variable_value,RNDS,WNDS);
1219
1220 -- timestamp with local timezone
1221 procedure bind_variable(c in integer, name in varchar2,
1222 value in TIMESTAMP_LTZ_UNCONSTRAINED);
1223
1224 pragma restrict_references(bind_variable,WNDS);
1225
1226 procedure define_column(c in integer, position in integer,
1227 column in TIMESTAMP_LTZ_UNCONSTRAINED);
1228
1229 pragma restrict_references(define_column,RNDS,WNDS);
1230
1231 procedure column_value(c in integer, position in integer,
1232 value out TIMESTAMP_LTZ_UNCONSTRAINED);
1233
1234 pragma restrict_references(column_value,RNDS,WNDS);
1235
1236 procedure variable_value(c in integer, name in varchar2,
1237 value out TIMESTAMP_LTZ_UNCONSTRAINED);
1238
1239 pragma restrict_references(variable_value,RNDS,WNDS);
1240
1241 procedure bind_array(c in integer, name in varchar2,
1242 tstz_tab in timestamp_with_ltz_Table);
1243 pragma restrict_references(bind_array,WNDS);
1244
1245 procedure bind_array(c in integer, name in varchar2,
1246 tstz_tab in timestamp_with_ltz_Table,
1247 index1 in integer, index2 in integer);
1248 pragma restrict_references(bind_array,WNDS);
1249
1250 procedure define_array(c in integer, position in integer,
1251 tstz_tab in timestamp_with_ltz_Table,
1252 cnt in integer, lower_bound in integer);
1253 pragma restrict_references(define_array,RNDS,WNDS);
1254
1255 procedure column_value(c in integer, position in integer,
1256 tstz_tab in out nocopy timestamp_with_ltz_Table);
1257 pragma restrict_references(column_value,RNDS,WNDS);
1258
1259 procedure variable_value(c in integer, name in varchar2,
1260 value out nocopy timestamp_with_ltz_Table);
1261 pragma restrict_references(variable_value,RNDS,WNDS);
1265
1262 $else
1263 /* time zone features not supported in this environment */
1264 $end
1266
1267 -- Interval support
1268 -- yminterval_unconstrained
1269 procedure bind_variable(c in integer, name in varchar2,
1270 value in YMINTERVAL_UNCONSTRAINED);
1271
1272 pragma restrict_references(bind_variable,WNDS);
1273
1274 procedure define_column(c in integer, position in integer,
1275 column in YMINTERVAL_UNCONSTRAINED);
1276
1277 pragma restrict_references(define_column,RNDS,WNDS);
1278
1279 procedure column_value(c in integer, position in integer,
1280 value out YMINTERVAL_UNCONSTRAINED);
1281
1282 pragma restrict_references(column_value,RNDS,WNDS);
1283
1284 procedure variable_value(c in integer, name in varchar2,
1285 value out YMINTERVAL_UNCONSTRAINED);
1286
1287 pragma restrict_references(variable_value,RNDS,WNDS);
1288
1289 procedure bind_array(c in integer, name in varchar2,
1290 iym_tab in Interval_Year_To_Month_Table);
1291 pragma restrict_references(bind_array,WNDS);
1292
1293 procedure bind_array(c in integer, name in varchar2,
1294 iym_tab in Interval_Year_To_Month_Table,
1295 index1 in integer, index2 in integer);
1296 pragma restrict_references(bind_array,WNDS);
1297
1298 procedure define_array(c in integer, position in integer,
1299 iym_tab in Interval_Year_To_Month_Table,
1300 cnt in integer, lower_bound in integer);
1301 pragma restrict_references(define_array,RNDS,WNDS);
1302
1303 procedure column_value(c in integer, position in integer,
1304 iym_tab in out nocopy interval_year_to_month_table);
1305 pragma restrict_references(column_value,RNDS,WNDS);
1306
1307 procedure variable_value(c in integer, name in varchar2,
1308 value out nocopy Interval_Year_To_Month_Table);
1309 pragma restrict_references(variable_value,RNDS,WNDS);
1310
1311 -- DSINTERVAL_UNCONSTRAINED
1312 procedure bind_variable(c in integer, name in varchar2,
1313 value in DSINTERVAL_UNCONSTRAINED);
1314
1315 pragma restrict_references(bind_variable,WNDS);
1316
1317 procedure define_column(c in integer, position in integer,
1318 column in DSINTERVAL_UNCONSTRAINED);
1319
1320 pragma restrict_references(define_column,RNDS,WNDS);
1321
1322 procedure column_value(c in integer, position in integer,
1323 value out DSINTERVAL_UNCONSTRAINED);
1324
1325 pragma restrict_references(column_value,RNDS,WNDS);
1326
1327 procedure variable_value(c in integer, name in varchar2,
1328 value out DSINTERVAL_UNCONSTRAINED);
1329
1330 pragma restrict_references(variable_value,RNDS,WNDS);
1331
1332 procedure bind_array(c in integer, name in varchar2,
1333 ids_tab in Interval_Day_To_Second_Table);
1334 pragma restrict_references(bind_array,WNDS);
1335
1336 procedure bind_array(c in integer, name in varchar2,
1337 ids_tab in Interval_Day_To_Second_Table,
1338 index1 in integer, index2 in integer);
1339 pragma restrict_references(bind_array,WNDS);
1340
1341 procedure define_array(c in integer, position in integer,
1342 ids_tab in Interval_Day_To_Second_Table,
1343 cnt in integer, lower_bound in integer);
1344 pragma restrict_references(define_array,RNDS,WNDS);
1345
1346 procedure column_value(c in integer, position in integer,
1347 ids_tab in out nocopy interval_day_to_second_table);
1348 pragma restrict_references(column_value,RNDS,WNDS);
1349
1350 procedure variable_value(c in integer, name in varchar2,
1351 value out nocopy Interval_Day_To_Second_Table);
1352 pragma restrict_references(variable_value,RNDS,WNDS);
1353
1354 procedure describe_columns2(c in integer, col_cnt out integer,
1355 desc_t out desc_tab2);
1356 pragma restrict_references(describe_columns2,WNDS);
1357
1358 -- Get the description for the specified column.
1359 -- Bug 702903: This is a replacement for - or an alternative to - the
1360 -- describe_columns API.
1361 -- Input parameters:
1362 -- c
1363 -- Cursor id number of the cursor from which to describe the column.
1364 -- Output Parameters:
1365 -- col_cnt
1366 -- The number of columns in the select list of the query.
1367 -- desc_tab2
1368 -- The describe table to fill in with the description of each of the
1369 -- columns of the query. This table is indexed from one to the number
1370 -- of elements in the select list of the query.
1371
1372 -- binary_float
1373 procedure bind_variable(c in integer, name in varchar2,
1374 value in binary_float);
1375 pragma restrict_references(bind_variable,WNDS);
1376
1377 procedure define_column(c in integer, position in integer,
1378 column in binary_float);
1379 pragma restrict_references(define_column,RNDS,WNDS);
1380
1381 procedure column_value(c in integer, position in integer,
1382 value out binary_float);
1383 pragma restrict_references(column_value,RNDS,WNDS);
1384
1385 procedure variable_value(c in integer, name in varchar2,
1386 value out binary_float);
1387 pragma restrict_references(variable_value,RNDS,WNDS);
1388
1392
1389 procedure bind_array(c in integer, name in varchar2,
1390 bflt_tab in Binary_Float_Table);
1391 pragma restrict_references(bind_array,WNDS);
1393 procedure bind_array(c in integer, name in varchar2,
1394 bflt_tab in Binary_Float_Table,
1395 index1 in integer, index2 in integer);
1396 pragma restrict_references(bind_array,WNDS);
1397
1398 procedure define_array(c in integer, position in integer,
1399 bflt_tab in Binary_Float_Table,
1400 cnt in integer, lower_bound in integer);
1401 pragma restrict_references(define_array,RNDS,WNDS);
1402
1403 procedure column_value(c in integer, position in integer,
1404 bflt_tab in out nocopy Binary_Float_Table);
1405 pragma restrict_references(column_value,RNDS,WNDS);
1406
1407 procedure variable_value(c in integer, name in varchar2,
1408 value out nocopy Binary_Float_Table);
1409 pragma restrict_references(variable_value,RNDS,WNDS);
1410
1411 -- binary_double
1412 procedure bind_variable(c in integer, name in varchar2,
1413 value in binary_double);
1414 pragma restrict_references(bind_variable,WNDS);
1415
1416 procedure define_column(c in integer, position in integer,
1417 column in binary_double);
1418 pragma restrict_references(define_column,RNDS,WNDS);
1419
1420 procedure column_value(c in integer, position in integer,
1421 value out binary_double);
1422 pragma restrict_references(column_value,RNDS,WNDS);
1423
1424 procedure variable_value(c in integer, name in varchar2,
1425 value out binary_double);
1426 pragma restrict_references(variable_value,RNDS,WNDS);
1427
1428 procedure bind_array(c in integer, name in varchar2,
1429 bdbl_tab in Binary_Double_Table);
1430 pragma restrict_references(bind_array,WNDS);
1431
1432 procedure bind_array(c in integer, name in varchar2,
1433 bdbl_tab in Binary_Double_Table,
1434 index1 in integer, index2 in integer);
1435 pragma restrict_references(bind_array,WNDS);
1436
1437 procedure define_array(c in integer, position in integer,
1438 bdbl_tab in Binary_Double_Table,
1439 cnt in integer, lower_bound in integer);
1440 pragma restrict_references(define_array,RNDS,WNDS);
1441
1442 procedure column_value(c in integer, position in integer,
1443 bdbl_tab in out nocopy Binary_Double_Table);
1444 pragma restrict_references(column_value,RNDS,WNDS);
1445
1446 procedure variable_value(c in integer, name in varchar2,
1447 value out nocopy Binary_Double_Table);
1448 pragma restrict_references(variable_value,RNDS,WNDS);
1449
1450 -- Procedures and functions new in release 11
1451
1452 $if utl_ident.is_oracle_server $then
1453 procedure bind_variable(c in integer, name in varchar2,
1454 value in "<ADT_1>");
1455 pragma interface(c, bind_variable);
1456 pragma restrict_references(bind_variable,WNDS);
1457
1458 procedure bind_variable(c in integer, name in varchar2,
1459 value in REF "<ADT_1>");
1460 pragma interface(c, bind_variable);
1461 pragma restrict_references(bind_variable,WNDS);
1462 $else
1463 /* ADT or schema level collection not supported in this environment */
1464 $end
1465
1466 procedure bind_variable(c in integer, name in varchar2,
1467 value in "<TABLE_1>");
1468 pragma interface(c, bind_variable);
1469 pragma restrict_references(bind_variable,WNDS);
1470
1471 procedure bind_variable(c in integer, name in varchar2,
1472 value in "<VARRAY_1>");
1473 pragma interface(c, bind_variable);
1474 pragma restrict_references(bind_variable,WNDS);
1475
1476 $if utl_ident.is_oracle_server $then
1477 procedure bind_variable(c in integer, name in varchar2,
1478 value in "<OPAQUE_1>");
1479 pragma interface(c, bind_variable);
1480 pragma restrict_references(bind_variable,WNDS);
1481
1482 procedure variable_value(c in integer, name in varchar2,
1483 value out "<ADT_1>");
1484 pragma interface(c, variable_value);
1485 pragma restrict_references(variable_value,RNDS,WNDS);
1486
1487 procedure variable_value(c in integer, name in varchar2,
1488 value out REF "<ADT_1>");
1489 pragma interface(c, variable_value);
1490 pragma restrict_references(variable_value,RNDS,WNDS);
1491 $else
1492 /* ADT or schema level collection not supported in this environment */
1493 $end
1494
1495 procedure variable_value(c in integer, name in varchar2,
1496 value out "<TABLE_1>");
1497 pragma interface(c, variable_value);
1498 pragma restrict_references(variable_value,RNDS,WNDS);
1499
1500 procedure variable_value(c in integer, name in varchar2,
1501 value out "<VARRAY_1>");
1502 pragma interface(c, variable_value);
1503 pragma restrict_references(variable_value,RNDS,WNDS);
1504
1505 $if utl_ident.is_oracle_server $then
1506 procedure variable_value(c in integer, name in varchar2,
1507 value out "<OPAQUE_1>");
1508 pragma interface(c, variable_value);
1509 pragma restrict_references(variable_value,RNDS,WNDS);
1510
1511 procedure define_column(c in integer, position in binary_integer,
1515
1512 column in "<ADT_1>");
1513 pragma interface(c, define_column);
1514 pragma restrict_references(define_column,RNDS,WNDS);
1516 procedure define_column(c in integer, position in binary_integer,
1517 column in REF "<ADT_1>");
1518 pragma interface(c, define_column);
1519 pragma restrict_references(define_column,RNDS,WNDS);
1520 $else
1521 /* ADT or schema level collection not supported in this environment */
1522 $end
1523
1524 procedure define_column(c in integer, position in binary_integer,
1525 column in "<TABLE_1>");
1526 pragma interface(c, define_column);
1527 pragma restrict_references(define_column,RNDS,WNDS);
1528
1529 procedure define_column(c in integer, position in binary_integer,
1530 column in "<VARRAY_1>");
1531 pragma interface(c, define_column);
1532 pragma restrict_references(define_column,RNDS,WNDS);
1533
1534 $if utl_ident.is_oracle_server $then
1535 procedure define_column(c in integer, position in binary_integer,
1536 column in "<OPAQUE_1>");
1537 pragma interface(c, define_column);
1538 pragma restrict_references(define_column,RNDS,WNDS);
1539
1540 procedure column_value(c in integer, position in binary_integer,
1541 value out "<ADT_1>");
1542 pragma interface(c, column_value);
1543 pragma restrict_references(column_value,RNDS,WNDS);
1544
1545 procedure column_value(c in integer, position in binary_integer,
1546 value out REF "<ADT_1>");
1547 pragma interface(c, column_value);
1548 pragma restrict_references(column_value,RNDS,WNDS);
1549 $else
1550 /* ADT or schema level collection not supported in this environment */
1551 $end
1552
1553 procedure column_value(c in integer, position in binary_integer,
1554 value out "<TABLE_1>");
1555 pragma interface(c, column_value);
1556 pragma restrict_references(column_value,RNDS,WNDS);
1557
1558 procedure column_value(c in integer, position in binary_integer,
1559 value out "<VARRAY_1>");
1560 pragma interface(c, column_value);
1561 pragma restrict_references(column_value,RNDS,WNDS);
1562
1563 $if utl_ident.is_oracle_server $then
1564 procedure column_value(c in integer, position in binary_integer,
1565 value out "<OPAQUE_1>");
1566 pragma interface(c, column_value);
1567 pragma restrict_references(column_value,RNDS,WNDS);
1568 $else
1569 /* ADT or schema level collection not supported in this environment */
1570 $end
1571
1572 procedure describe_columns3(c in integer, col_cnt out integer,
1573 desc_t out desc_tab3);
1574 pragma restrict_references(describe_columns3,WNDS);
1575 -- Like describe_columns2 defined above. The description records
1576 -- from this call include information about columns' user-defined types.
1577
1578 function to_refcursor(cursor_number in out integer) return sys_refcursor;
1579 pragma restrict_references(to_refcursor,RNDS,WNDS);
1580
1581 -- This function takes a DBMS_SQL OPENed, PARSEd, and EXECUTEd cursor
1582 -- and transforms/migrates it into a PL/SQL manageable REF CURSOR
1583 -- (weakly-typed cursor) that can be consumed by PL/SQL native dynamic SQL.
1584 -- This function is only used with SELECT cursors.
1585 -- Once the cursor_number is transformed into a REF CURSOR, the
1586 -- cursor_number is no longer accessible by any DBMS_SQL operations.
1587 -- Input parameters:
1588 -- cursor_number
1589 -- Cursor number of the cursor to be transformed into REF CURSOR.
1590 -- Output parameters:
1591 -- cursor_number
1592 -- Cursor number will be NULLed.
1593 -- Return value:
1594 -- PL/SQL REF CURSOR transformed from a DBMS_SQL cursor number.
1595 --
1596
1597 function to_cursor_number(rc in out sys_refcursor) return integer;
1598 pragma restrict_references(to_cursor_number,RNDS,WNDS);
1599
1600 -- This function takes an OPENed strongly or weakly-typed ref cursor and
1601 -- transforms it into a DBMS_SQL cursor number.
1602 -- Input parameters:
1603 -- rc
1604 -- REF Cursor to be transformed into cursor number.
1605 -- Return value:
1606 -- DBMS_SQL manageable cursor number transformed from a REF CURSOR.
1607 -- Once the REF CURSOR is transformed into a DBMS_SQL cursor number,
1608 -- the REF CURSOR is no longer accessible by any native dynamic SQL
1609 -- operations.
1610 --
1611
1612 procedure parse(c in integer, statement in clob,
1613 language_flag in integer);
1614
1615
1616 --------------------------------------------------------------------------
1617 -- Extended overloads for parse
1618
1619 $if utl_ident.is_oracle_server $then
1620 procedure parse(c in integer,
1621 statement in varchar2,
1622 language_flag in integer,
1623 edition in varchar2);
1624 --
1625 procedure parse(c in integer,
1626 statement in varchar2,
1627 language_flag in integer,
1628 edition in varchar2 default NULL,
1629 apply_crossedition_trigger in varchar2,
1630 fire_apply_trigger in boolean default TRUE);
1631
1632 --
1633 procedure parse(c in integer,
1634 statement in clob,
1635 language_flag in integer,
1636 edition in varchar2);
1637 --
1638 procedure parse(c in integer,
1639 statement in clob,
1643 fire_apply_trigger in boolean default TRUE);
1640 language_flag in integer,
1641 edition in varchar2 default NULL,
1642 apply_crossedition_trigger in varchar2,
1644
1645 --
1646 procedure parse(c in integer,
1647 statement in varchar2a,
1648 lb in integer,
1649 ub in integer,
1650 lfflg in boolean,
1651 language_flag in integer,
1652 edition in varchar2);
1653
1654 --
1655 procedure parse(c in integer,
1656 statement in varchar2a,
1657 lb in integer,
1658 ub in integer,
1659 lfflg in boolean,
1660 language_flag in integer,
1661 edition in varchar2 default NULL,
1662 apply_crossedition_trigger in varchar2,
1663 fire_apply_trigger in boolean default TRUE);
1664
1665 --
1666 procedure parse(c in integer,
1667 statement in varchar2s,
1668 lb in integer,
1669 ub in integer,
1670 lfflg in boolean,
1671 language_flag in integer,
1672 edition in varchar2);
1673 --
1674 procedure parse(c in integer,
1675 statement in varchar2s,
1676 lb in integer,
1677 ub in integer,
1678 lfflg in boolean,
1679 language_flag in integer,
1680 edition in varchar2 default NULL,
1681 apply_crossedition_trigger in varchar2,
1682 fire_apply_trigger in boolean default TRUE);
1683 $else
1684 /* Edition overloads are not supported in this environment */
1685 $end
1686
1687 --
1688 --------------------------------------------------------------------------
1689 function open_cursor(security_level in integer) return integer;
1690 pragma restrict_references(open_cursor,RNDS,WNDS);
1691 -- This overload of open_cursor takes a security_level.
1692 -- Open a new cursor with specified security level.
1693 -- When no longer needed, this cursor MUST BE CLOSED explicitly by
1694 -- calling "close_cursor".
1695 -- Return value:
1696 -- Cursor id number of the new cursor.
1697 --
1698 -- Input parameters:
1699 -- security_level
1700 -- Specifies the level of security protection to enforce on the opened
1701 -- cursor. Valid security level values are 0, 1, and 2. When a NULL
1702 -- argument value is provided to this overload, as well as for cursors
1703 -- opened using the overload without the security_level parameter,
1704 -- the level of protection will be set to system default, level 1.
1705 --
1706 -- Level 0 allows all DBMS_SQL operations on the cursor without any
1707 -- security checks. The cursor may be fetched from, and even re-bound
1708 -- and re-executed, by code running with a different effective userid
1709 -- or roles than those in effect at the time the cursor was parsed.
1710 -- By default, level 0 is disallowed.
1711 --
1712 -- Level 1 requires that the effective userid and roles of the caller
1713 -- to dbms_sql for bind and execute operations on this cursor must be
1714 -- the same as those of the caller of the most recent parse operation
1715 -- on this cursor.
1716 --
1717 -- Level 2 requires that the effective userid and roles of the caller
1718 -- to dbms_sql for all bind, execute, define, describe, and fetch
1719 -- operations on this cursor must be the same as those of the caller
1720 -- of the most recent parse operation on this cursor.
1721 --
1722 -- Should this behavior change w.r.t. Oracle Database 10g cause problems
1723 -- in extant applications, please contact Oracle Support.
1724 --
1725 -- treat_as_client_for_results
1726 -- TRUE to treat this caller as the client to receive the statement
1727 -- results returned to client. FALSE otherwise.
1728 --------------------------------------------------------------------------
1729
1730 --------------------------------------------------------------------------
1731 -- Implicit result support
1732 --------------------------------------------------------------------------
1733 $if utl_ident.is_oracle_server $then
1734 function open_cursor(treat_as_client_for_results in boolean)
1735 return integer;
1736 pragma restrict_references(open_cursor,RNDS,WNDS);
1737
1738 function open_cursor(security_level in integer,
1739 treat_as_client_for_results in boolean)
1740 return integer;
1741 pragma restrict_references(open_cursor,RNDS,WNDS);
1742 $else
1743 /* Implicit result not supported in this environment */
1744 $end
1745
1746 $if utl_ident.is_oracle_server $then
1747 procedure return_result(rc in out sys_refcursor,
1748 to_client in boolean default true);
1749 procedure return_result(rc in out integer,
1750 to_client in boolean default true);
1751 pragma restrict_references(return_result,RNDS,WNDS);
1752 $else
1753 /* return_result not supported in this environment */
1754 $end
1755 -- Returns the result of an executed statement to the client application.
1756 -- The result can be retrieved later by the client.
1757 --
1758 -- Or, it can return the statement result to and be retrieved later by the
1762 -- procedure via JDBC, a .NET stored procedure via ADO.NET, or an external
1759 -- immediate caller that executes a recursive statement in which this
1760 -- statement result will be returned. The caller can be a PL/SQL stored
1761 -- procedure executing the recursive statement via DBMS_SQL, a Java stored
1763 -- procedure via OCI.
1764 --
1765 -- Input parameters:
1766 -- rc
1767 -- Cursor number or the REF CURSOR of the statement to return.
1768 -- to_client
1769 -- Return the statement result to the client or not? If not, it will
1770 -- be returned to the immediate caller instead.
1771 -- Output parameters:
1772 -- rc
1773 -- Cursor number or the REF CURSOR will be NULLed.
1774 --
1775 -- NOTES:
1776 -- * In the current release, only a SQL query can be returned. And the return
1777 -- of statement results over remote procedure calls is not supported.
1778 -- * Once the statement is returned, it is no longer accessible except by
1779 -- the client or the immediate caller to which it is returned. And the
1780 -- caller of RETURN_RESULT does not need to and should not close the cursor
1781 -- of the statement after it has been returned.
1782 -- * Statement results cannot be returned when the statement being executed
1783 -- by the client or any intermediate recursive statement is a SQL query
1784 -- and an error will be raised.
1785 -- * A ref cursor being returned can be strongly or weakly-typed.
1786 -- * A query being returned can be partially fetched.
1787 -- * Because EXECUTE IMMEDIATE statement provides no interface to retrieve
1788 -- the statement results returned from its recursive statement, the cursors
1789 -- of the statement results returned to the caller of the EXECUTE IMMEDIATE
1790 -- statement will be closed when the EXECUTE IMMEDIATE statement completes.
1791 -- To retrieve the returned statement results from a recursive statement in
1792 -- PL/SQL, use DBMS_SQL to execute the recursive statement instead.
1793 --------------------------------------------------------------------------
1794
1795 --------------------------------------------------------------------------
1796 $if utl_ident.is_oracle_server $then
1797 procedure get_next_result(c in integer, rc out sys_refcursor);
1798 procedure get_next_result(c in integer, rc out integer);
1799 pragma restrict_references(get_next_result,RNDS,WNDS);
1800 $else
1801 /* get_next_result not supported in this environment */
1802 $end
1803 -- Gets the statement of the next result returned to this caller of the
1804 -- recursive statement or, if this caller sets itself as the client for
1805 -- the recursive statement, the next result return to this client. The
1806 -- statements are returned in the same order as they are returned by
1807 -- RETURN_RESULT.
1808 --
1809 -- Input parameters:
1810 -- c
1811 -- Cursor id number of the recursive statement cursor to get the result
1812 -- from.
1813 -- Output parameters:
1814 -- rc
1815 -- Cursor number or the REF CURSOR of the statement of the result
1816 -- returned.
1817 -- Exceptions:
1818 -- no_data_found
1819 -- Raised if there is not more returned statement result.
1820 --
1821 -- NOTES:
1822 -- * After the cursor of a statement result is retrieved, the caller must
1823 -- close the cursor properly when it is no longer needed.
1824 -- * The cursors for all unretrieved returned statement results will be
1825 -- closed after the cursor of the recursive statement is closed.
1826 --------------------------------------------------------------------------
1827
1828 --------------------------------------------------------------------------
1829 -- 32k varchar2 support for bind_array/define_array
1830 --------------------------------------------------------------------------
1831 procedure bind_array(c in integer, name in varchar2,
1832 c_tab in varchar2a);
1833 pragma restrict_references(bind_array,WNDS);
1834
1835 procedure bind_array(c in integer, name in varchar2,
1836 c_tab in varchar2a,
1837 index1 in integer, index2 in integer);
1838 pragma restrict_references(bind_array,WNDS);
1839
1840 procedure define_array(c in integer, position in integer,
1841 c_tab in varchar2a,
1842 cnt in integer, lower_bound in integer);
1843 pragma restrict_references(define_array,RNDS,WNDS);
1844
1845 procedure column_value(c in integer, position in integer,
1846 c_tab in out nocopy varchar2a);
1847 pragma restrict_references(column_value,RNDS,WNDS);
1848
1849 procedure variable_value(c in integer, name in varchar2,
1850 value out nocopy varchar2a);
1851 pragma restrict_references(variable_value,RNDS,WNDS);
1852
1853 --------------------------------------------------------------------------
1854 -- Extended overloads for parse
1855 $if utl_ident.is_oracle_server $then
1856 procedure parse(c in integer,
1857 statement in varchar2,
1858 language_flag in integer,
1859 edition in varchar2 default NULL,
1860 apply_crossedition_trigger in varchar2 default NULL,
1861 fire_apply_trigger in boolean default TRUE,
1862 schema in varchar2);
1863 --
1864 procedure parse(c in integer,
1865 statement in clob,
1866 language_flag in integer,
1867 edition in varchar2 default NULL,
1871 --
1868 apply_crossedition_trigger in varchar2 default NULL,
1869 fire_apply_trigger in boolean default TRUE,
1870 schema in varchar2);
1872 procedure parse(c in integer,
1873 statement in varchar2a,
1874 lb in integer,
1875 ub in integer,
1876 lfflg in boolean,
1877 language_flag in integer,
1878 edition in varchar2 default NULL,
1879 apply_crossedition_trigger in varchar2 default NULL,
1880 fire_apply_trigger in boolean default TRUE,
1881 schema in varchar2);
1882 --
1883 procedure parse(c in integer,
1884 statement in varchar2s,
1885 lb in integer,
1886 ub in integer,
1887 lfflg in boolean,
1888 language_flag in integer,
1889 edition in varchar2 default NULL,
1890 apply_crossedition_trigger in varchar2 default NULL,
1891 fire_apply_trigger in boolean default TRUE,
1892 schema in varchar2);
1893
1894 -- container support
1895 procedure parse(c in integer,
1896 statement in varchar2,
1897 language_flag in integer,
1898 edition in varchar2 default NULL,
1899 apply_crossedition_trigger in varchar2 default NULL,
1900 fire_apply_trigger in boolean default TRUE,
1901 schema in varchar2 default NULL,
1902 container in varchar2);
1903 --
1904 procedure parse(c in integer,
1905 statement in clob,
1906 language_flag in integer,
1907 edition in varchar2 default NULL,
1908 apply_crossedition_trigger in varchar2 default NULL,
1909 fire_apply_trigger in boolean default TRUE,
1910 schema in varchar2 default NULL,
1911 container in varchar2);
1912
1913 --
1914 procedure parse(c in integer,
1915 statement in varchar2a,
1916 lb in integer,
1917 ub in integer,
1918 lfflg in boolean,
1919 language_flag in integer,
1920 edition in varchar2 default NULL,
1921 apply_crossedition_trigger in varchar2 default NULL,
1922 fire_apply_trigger in boolean default TRUE,
1923 schema in varchar2 default NULL,
1924 container in varchar2);
1925 --
1926 procedure parse(c in integer,
1927 statement in varchar2s,
1928 lb in integer,
1929 ub in integer,
1930 lfflg in boolean,
1931 language_flag in integer,
1932 edition in varchar2 default NULL,
1933 apply_crossedition_trigger in varchar2 default NULL,
1934 fire_apply_trigger in boolean default TRUE,
1935 schema in varchar2 default NULL,
1936 container in varchar2);
1937 $else
1938 /* Edition overloads are not supported in this environment */
1939 $end
1940
1941 -------------
1942 -- Named Datatype CONSTANTS
1943 --
1944 Varchar2_Type constant pls_integer := 1;
1945 Number_Type constant pls_integer := 2;
1946 Long_Type constant pls_integer := 8;
1947 Rowid_Type constant pls_integer := 11;
1948 Date_Type constant pls_integer := 12;
1949 Raw_Type constant pls_integer := 23;
1950 Long_Raw_Type constant pls_integer := 24;
1951 Char_Type constant pls_integer := 96;
1952 Binary_Float_Type constant pls_integer := 100;
1953 Binary_Double_Type constant pls_integer := 101;
1954 MLSLabel_Type constant pls_integer := 106;
1955 User_Defined_Type constant pls_integer := 109;
1956 Ref_Type constant pls_integer := 111;
1957 Clob_Type constant pls_integer := 112;
1958 Blob_Type constant pls_integer := 113;
1959 Bfile_Type constant pls_integer := 114;
1960 Timestamp_Type constant pls_integer := 180;
1961 Timestamp_With_TZ_Type constant pls_integer := 181;
1962 Interval_Year_to_Month_Type constant pls_integer := 182;
1963 Interval_Day_To_Second_Type constant pls_integer := 183;
1964 Urowid_Type constant pls_integer := 208;
1965 Timestamp_With_Local_TZ_type constant pls_integer := 231;
1966
1967 -- #(10144724): The typo Binary_Bouble_Type is purposefully retained for
1968 -- backward compatibility.
1969 Binary_Bouble_Type constant pls_integer := 101;
1970 end;
[Home] [Help]