Skip to content

dbms_sql examples – complete reference for the new oracle dba





dbms_sql examples – complete reference for the new oracle dba

Formerly, to execute dynamic SQL statements, you had to use the supplied package

DBMS_SQL. Now, within PL/SQL, you can execute any kind of dynamic SQL

statement using an interface called native dynamic SQL. The main PL/SQL features

involved are the EXECUTE IMMEDIATE statement and cursor variables (also known as

REF CURSORs).

Example 1

This example does not require the use of dynamic SQL because the text of the

statement is known at compile time., but it illustrate the basic concept underlying the

package.

The DEMO procedure deletes all of the employees from the EMP table whose salaries are

greater than the salary that you specify when you run DEMO.

CREATE OR REPLACE PROCEDURE demo(salary IN NUMBER) AS

cursor_name INTEGER;

rows_processed INTEGER;

BEGIN

cursor_name := dbms_sql.open_cursor;

DBMS_SQL.PARSE(cursor_name, ‘DELETE FROM emp WHERE sal > :x’,

DBMS_SQL.NATIVE);

DBMS_SQL.BIND_VARIABLE(cursor_name, ‘:x’, salary);

rows_processed := DBMS_SQL.EXECUTE(cursor_name);

DBMS_SQL.CLOSE_CURSOR(cursor_name);

EXCEPTION

WHEN OTHERS THEN

DBMS_SQL.CLOSE_CURSOR(cursor_name);

END;

Example 2

The following sample procedure is passed a SQL statement, which it then parses and

runs:

CREATE OR REPLACE PROCEDURE exec(STRING IN varchar2) AS

cursor_name INTEGER;

ret INTEGER;

BEGIN

cursor_name := DBMS_SQL.OPEN_CURSOR;

DDL statements are run by the parse call, which performs the implied commit.

DBMS_SQL.PARSE(cursor_name, string, DBMS_SQL.NATIVE);

ret := DBMS_SQL.EXECUTE(cursor_name);

DBMS_SQL.CLOSE_CURSOR(cursor_name);

END;

Creating such a procedure enables you to perform the following operations:

■The SQL statement can be dynamically generated at runtime by the calling

program.

■The SQL statement can be a DDL statement or a DML without binds.

For example, after creating this procedure, you could make the following call:

exec(‘create table acct(c1 integer)’);

You could even call this procedure remotely, as shown in the following example. This

lets you perform remote DDL.

exec@hq.com(‘CREATE TABLE acct(c1 INTEGER)’);

Using DBMS_SQL

DBMS_SQL 100-15

Example 3

The following sample procedure is passed the names of a source and a destination

table, and copies the rows from the source table to the destination table. This sample

procedure assumes that both the source and destination tables have the following

columns:

id of type NUMBER

name of type VARCHAR2(30)

birthdate of type DATE

This procedure does not specifically require the use of dynamic SQL; however, it

illustrates the concepts of this package.

CREATE OR REPLACE PROCEDURE copy (

source IN VARCHAR2,

destination IN VARCHAR2) IS

id_var NUMBER;

name_var VARCHAR2(30);

birthdate_var DATE;

source_cursor INTEGER;

destination_cursor INTEGER;

ignore INTEGER;

BEGIN

— Prepare a cursor to select from the source table:

source_cursor := dbms_sql.open_cursor;

DBMS_SQL.PARSE(source_cursor,

‘SELECT id, name, birthdate FROM ‘ || source,

DBMS_SQL.NATIVE);

DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, id_var);

DBMS_SQL.DEFINE_COLUMN(source_cursor, 2, name_var, 30);

DBMS_SQL.DEFINE_COLUMN(source_cursor, 3, birthdate_var);

ignore := DBMS_SQL.EXECUTE(source_cursor);

— Prepare a cursor to insert into the destination table:

destination_cursor := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(destination_cursor,

‘INSERT INTO ‘ || destination ||

‘ VALUES (:id_bind, :name_bind, :birthdate_bind)’,

DBMS_SQL.NATIVE);

— Fetch a row from the source table and insert it into the destination table:

LOOP

IF DBMS_SQL.FETCH_ROWS(source_cursor)>0 THEN

— get column values of the row

DBMS_SQL.COLUMN_VALUE(source_cursor, 1, id_var);

DBMS_SQL.COLUMN_VALUE(source_cursor, 2, name_var);

DBMS_SQL.COLUMN_VALUE(source_cursor, 3, birthdate_var);

— Bind the row into the cursor that inserts into the destination table. You

— could alter this example to require the use of dynamic SQL by inserting an

— if condition before the bind.

DBMS_SQL.BIND_VARIABLE(destination_cursor, ‘:id_bind’, id_var);

DBMS_SQL.BIND_VARIABLE(destination_cursor, ‘:name_bind’, name_var);

DBMS_SQL.BIND_VARIABLE(destination_cursor, ‘:birthdate_bind’,

birthdate_var);

ignore := DBMS_SQL.EXECUTE(destination_cursor);

ELSE

— No more rows to copy:

Examples

100-16 Oracle Database PL/SQL Packages and Types Reference

EXIT;

END IF;

END LOOP;

— Commit and close all cursors:

COMMIT;

DBMS_SQL.CLOSE_CURSOR(source_cursor);

DBMS_SQL.CLOSE_CURSOR(destination_cursor);

EXCEPTION

WHEN OTHERS THEN

IF DBMS_SQL.IS_OPEN(source_cursor) THEN

DBMS_SQL.CLOSE_CURSOR(source_cursor);

END IF;

IF DBMS_SQL.IS_OPEN(destination_cursor) THEN

DBMS_SQL.CLOSE_CURSOR(destination_cursor);

END IF;

RAISE;

END;

/

Examples 3, 4, and 5: Bulk DML

This series of examples shows how to use bulk array binds (table items) in the SQL

DML statements DELETE, INSERT, and UPDATE.

In a DELETE statement, for example, you could bind in an array in the WHERE clause

and have the statement be run for each element in the array:

DECLARE

stmt VARCHAR2(200);

dept_no_array DBMS_SQL.NUMBER_TABLE;

c NUMBER;

dummy NUMBER;

begin

dept_no_array(1) := 10; dept_no_array(2) := 20;

dept_no_array(3) := 30; dept_no_array(4) := 40;

dept_no_array(5) := 30; dept_no_array(6) := 40;

stmt := ‘delete from emp where deptno = :dept_array’;

c := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(c, stmt, DBMS_SQL.NATIVE);

DBMS_SQL.BIND_ARRAY(c, ‘:dept_array’, dept_no_array, 1, 4);

dummy := DBMS_SQL.EXECUTE(c);

DBMS_SQL.CLOSE_CURSOR(c);

EXCEPTION WHEN OTHERS THEN

IF DBMS_SQL.IS_OPEN(c) THEN

DBMS_SQL.CLOSE_CURSOR(c);

END IF;

RAISE;

END;

/

In the preceding example, only elements 1 through 4 are used as specified by the

BIND_ARRAY call. Each element of the array potentially deletes a large number of

employees from the database.

Here is an example of a bulk INSERT statement:

DECLARE

stmt VARCHAR2(200);

empno_array DBMS_SQL.NUMBER_TABLE;

empname_array DBMS_SQL.VARCHAR2_TABLE;

Using DBMS_SQL

DBMS_SQL 100-17

c NUMBER;

dummy NUMBER;

BEGIN

FOR i in 0..9 LOOP

empno_array(i) := 1000 + i;

empname_array(I) := get_name(i);

END LOOP;

stmt := ‘INSERT INTO emp VALUES(:num_array, :name_array)’;

c := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(c, stmt, DBMS_SQL.NATIVE);

DBMS_SQL.BIND_ARRAY(c, ‘:num_array’, empno_array);

DBMS_SQL.BIND_ARRAY(c, ‘:name_array’, empname_array);

dummy := DBMS_SQL.EXECUTE(c);

DBMS_SQL.CLOSE_CURSOR(c);

EXCEPTION WHEN OTHERS THEN

IF DBMS_SQL.IS_OPEN(c) THEN

DBMS_SQL.CLOSE_CURSOR(c);

END IF;

RAISE;

END;

/

When the execute takes place, all 10 of the employees are inserted into the table.

Finally, here is an example of an bulk UPDATE statement.

Declare

stmt VARCHAR2(200);

emp_no_array DBMS_SQL.NUMBER_TABLE;

emp_addr_array DBMS_SQL.VARCHAR2_TABLE;

c NUMBER;

dummy NUMBER;

BEGIN

for i in 0..9 loop

emp_no_array(i) := 1000 + i;

emp_addr_array(I) := get_new_addr(i);

END LOOP;

stmt := ‘update emp set ename = :name_array

WHERE empno = :num_array’;

c := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(c, stmt, DBMS_SQL.NATIVE);

DBMS_SQL.BIND_ARRAY(c, ‘:num_array’, empno_array);

DBMS_SQL.BIND_ARRAY(c, ‘:name_array’, empname_array);

dummy := DBMS_SQL.EXECUTE(c);

DBMS_SQL.CLOSE_CURSOR(c);

EXCEPTION WHEN OTHERS THEN

IF DBMS_SQL.IS_OPEN(c) THEN

DBMS_SQL.CLOSE_CURSOR(c);

END IF;

RAISE;

END;

/

When the EXECUTE Function call happens, the addresses of all employees are

updated at once. The two collections are always stepped in unison. If the WHERE clause

returns more than one row, then all those employees get the address the addr_array

happens to be pointing to at that time.

Examples

100-18 Oracle Database PL/SQL Packages and Types Reference

Examples 6 and 7: Defining an Array

The following examples show how to use the DEFINE_ARRAY procedure:

declare

c NUMBER;

d NUMBER;

n_tab DBMS_SQL.NUMBER_TABLE;

indx NUMBER := -10;

BEGIN

c := DBMS_SQL.OPEN_CURSOR;

dBMS_SQL.PARSE(c, ‘select n from t order by 1’, DBMS_SQL.NATIVE);

DBMS_SQL.DEFINE_ARRAY(c, 1, n_tab, 10, indx);

d := DBMS_SQL.EXECUTE(c);

loop

d := DBMS_SQL.FETCH_ROWS(c);

DBMS_SQL.COLUMN_VALUE(c, 1, n_tab);

EXIT WHEN d != 10;

END LOOP;

DBMS_SQL.CLOSE_CURSOR(c);

EXCEPTION WHEN OTHERS THEN

IF DBMS_SQL.IS_OPEN(c) THEN

DBMS_SQL.CLOSE_CURSOR(c);

END IF;

RAISE;

END;

/

Each time the preceding example does a FETCH_ROWS Function call, it fetches 10

rows that are kept in DBMS_SQL buffers. When the COLUMN_VALUE Procedure call

is run, those rows move into the PL/SQL table specified (in this case n_tab), at

positions -10 to -1, as specified in the DEFINE statements. When the second batch is

fetched in the loop, the rows go to positions 0 to 9; and so on.

A current index into each array is maintained automatically. This index is initialized to

"indx" at EXECUTE and keeps getting updated every time a COLUMN_VALUE call is

made. If you reexecute at any point, then the current index for each DEFINE is

re-initialized to "indx".

In this way the entire result of the query is fetched into the table. When FETCH_ROWS

cannot fetch 10 rows, it returns the number of rows actually fetched (if no rows could

be fetched, then it returns zero) and exits the loop.

Here is another example of using the DEFINE_ARRAY procedure:

Consider a table MULTI_TAB defined as:

CREATE TABLE multi_tab (num NUMBER,

dat1 DATE,

var VARCHAR2(24),

dat2 DATE)

To select everything from this table and move it into four PL/SQL tables, you could

use the following simple program:

declare

c NUMBER;

Using DBMS_SQL

DBMS_SQL 100-19

d NUMBER;

n_tab DBMS_SQL.NUMBER_TABLE;

d_tab1 DBMS_SQL.DATE_TABLE;

v_tab DBMS_SQL.VARCHAR2_TABLE;

d_tab2 DBMS_SQL.DATE_TABLE;

indx NUMBER := 10;

BEGIN

c := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(c, ‘select * from multi_tab order by 1’, DBMS_SQL.NATIVE);

DBMS_SQL.DEFINE_ARRAY(c, 1, n_tab, 5, indx);

DBMS_SQL.DEFINE_ARRAY(c, 2, d_tab1, 5, indx);

DBMS_SQL.DEFINE_ARRAY(c, 3, v_tab, 5, indx);

DBMS_SQL.DEFINE_ARRAY(c, 4, d_tab2, 5, indx);

d := DBMS_SQL.EXECUTE(c);

loop

d := DBMS_SQL.FETCH_ROWS(c);

DBMS_SQL.COLUMN_VALUE(c, 1, n_tab);

DBMS_SQL.COLUMN_VALUE(c, 2, d_tab1);

DBMS_SQL.COLUMN_VALUE(c, 3, v_tab);

DBMS_SQL.COLUMN_VALUE(c, 4, d_tab2);

EXIT WHEN d != 5;

END LOOP;

DBMS_SQL.CLOSE_CURSOR(c);

/*

The four tables can be used for anything. One usage might be to use BIND_ARRAY to

move the rows to another table by using a query such as ‘INSERT into SOME_T values

(:a, :b, :c, :d);

*/

EXCEPTION WHEN OTHERS THEN

IF DBMS_SQL.IS_OPEN(c) THEN

DBMS_SQL.CLOSE_CURSOR(c);

END IF;

RAISE;

END;

/

Example 8: Describe Columns

This can be used as a substitute to the SQL*Plus DESCRIBE call by using a SELECT *

query on the table that you want to describe.

DECLARE

c NUMBER;

d NUMBER;

col_cnt INTEGER;

f BOOLEAN;

rec_tab DBMS_SQL.DESC_TAB;

col_num NUMBER;

PROCEDURE print_rec(rec in DBMS_SQL.DESC_REC) IS

BEGIN

Examples

100-20 Oracle Database PL/SQL Packages and Types Reference

DBMS_OUTPUT.NEW_LINE;

DBMS_OUTPUT.PUT_LINE(‘col_type = ‘

|| rec.col_type);

DBMS_OUTPUT.PUT_LINE(‘col_maxlen = ‘

|| rec.col_max_len);

DBMS_OUTPUT.PUT_LINE(‘col_name = ‘

|| rec.col_name);

DBMS_OUTPUT.PUT_LINE(‘col_name_len = ‘

|| rec.col_name_len);

DBMS_OUTPUT.PUT_LINE(‘col_schema_name = ‘

|| rec.col_schema_name);

DBMS_OUTPUT.PUT_LINE(‘col_schema_name_len = ‘

|| rec.col_schema_name_len);

DBMS_OUTPUT.PUT_LINE(‘col_precision = ‘

|| rec.col_precision);

DBMS_OUTPUT.PUT_LINE(‘col_scale = ‘

|| rec.col_scale);

DBMS_OUTPUT.PUT(‘col_null_ok = ‘);

IF (rec.col_null_ok) THEN

DBMS_OUTPUT.PUT_LINE(‘true’);

ELSE

DBMS_OUTPUT.PUT_LINE(‘false’);

END IF;

END;

BEGIN

c := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(c, ‘SELECT * FROM scott.bonus’, DBMS_SQL.NATIVE);

d := DBMS_SQL.EXECUTE(c);

DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);

/*

* Following loop could simply be for j in 1..col_cnt loop.

* Here we are simply illustrating some of the PL/SQL table

* features.

*/

col_num := rec_tab.first;

IF (col_num IS NOT NULL) THEN

LOOP

print_rec(rec_tab(col_num));

col_num := rec_tab.next(col_num);

EXIT WHEN (col_num IS NULL);

END LOOP;

END IF;

DBMS_SQL.CLOSE_CURSOR(c);

END;

/

Example 9: RETURNING clause

The RETURNING clause was added to DML statements in an earlier Oracle database

release. With this clause, INSERT, UPDATE, and DELETE statements can return values

of expressions. These values are returned in bind variables.

DBMS_SQL.BIND_VARIABLE is used to bind these outbinds if a single row is inserted,

updated, or deleted. If multiple rows are inserted, updated, or deleted, then DBMS_

SQL.BIND_ARRAY is used. DBMS_SQL.VARIABLE_VALUE must be called to get the

values in these bind variables.

Using DBMS_SQL

DBMS_SQL 100-21

i) Single row insert

CREATE OR REPLACE PROCEDURE single_Row_insert

(c1 NUMBER, c2 NUMBER, r OUT NUMBER) is

c NUMBER;

n NUMBER;

begin

c := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(c, ‘INSERT INTO tab VALUES (:bnd1, :bnd2) ‘ ||

‘RETURNING c1*c2 INTO :bnd3’, DBMS_SQL.NATIVE);

DBMS_SQL.BIND_VARIABLE(c, ‘bnd1’, c1);

DBMS_SQL.BIND_VARIABLE(c, ‘bnd2’, c2);

DBMS_SQL.BIND_VARIABLE(c, ‘bnd3’, r);

n := DBMS_SQL.EXECUTE(c);

DBMS_SQL.VARIABLE_VALUE(c, ‘bnd3’, r); — get value of outbind variable

DBMS_SQL.CLOSE_CURSOR(c);

END;

/

ii) Single row update

CREATE OR REPLACE PROCEDURE single_Row_update

(c1 NUMBER, c2 NUMBER, r out NUMBER) IS

c NUMBER;

n NUMBER;

BEGIN

c := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(c, ‘UPDATE tab SET c1 = :bnd1, c2 = :bnd2 ‘ ||

‘WHERE rownum < 2’ ||

‘RETURNING c1*c2 INTO :bnd3’, DBMS_SQL.NATIVE);

DBMS_SQL.BIND_VARIABLE(c, ‘bnd1’, c1);

DBMS_SQL.BIND_VARIABLE(c, ‘bnd2’, c2);

DBMS_SQL.BIND_VARIABLE(c, ‘bnd3’, r);

n := DBMS_SQL.EXECUTE(c);

DBMS_SQL.VARIABLE_VALUE(c, ‘bnd3’, r);– get value of outbind variable

DBMS_SQL.CLOSE_CURSOR(c);

END;

/

iii) Single row delete

CREATE OR REPLACE PROCEDURE single_Row_Delete

(c1 NUMBER, c2 NUMBER, r OUT NUMBER) is

c NUMBER;

n number;

BEGIN

c := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(c, ‘delete from tab ‘ ||

‘where rownum < 2 ‘ ||

‘returning c1*c2 into :bnd3’, DBMS_SQL.NATIVE);

DBMS_SQL.BIND_VARIABLE(c, ‘bnd1’, c1);

DBMS_SQL.BIND_VARIABLE(c, ‘bnd2’, c2);

DBMS_SQL.BIND_VARIABLE(c, ‘bnd3’, r);

n := DBMS_SQL.EXECUTE(c);

DBMS_SQL.VARIABLE_VALUE(c, ‘bnd3’, r);– get value of outbind variable

Note: This is similar to DBMS_SQL.VARIABLE_VALUE, which

must be called after running a PL/SQL block with an out-bind

inside DBMS_SQL.

Examples

100-22 Oracle Database PL/SQL Packages and Types Reference

DBMS_SQL.CLOSE_CURSOR(c);

END;

/

iv) Multiple row insert

CREATE OR REPLACE PROCEDURE multi_Row_insert

(c1 DBMS_SQL.NUMBER_TABLE, c2 DBMS_SQL.NUMBER_TABLE,

r OUT DBMS_SQL.NUMBER_TABLE) is

c NUMBER;

n NUMBER;

BEGIN

c := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(c, ‘insert into tab VALUES (:bnd1, :bnd2) ‘ ||

‘RETURNING c1*c2 INTO :bnd3’, DBMS_SQL.NATIVE);

DBMS_SQL.BIND_ARRAY(c, ‘bnd1’, c1);

DBMS_SQL.BIND_ARRAY(c, ‘bnd2’, c2);

DBMS_SQL.BIND_ARRAY(c, ‘bnd3’, r);

n := DBMS_SQL.EXECUTE(c);

DBMS_SQL.VARIABLE_VALUE(c, ‘bnd3’, r);– get value of outbind variable

DBMS_SQL.CLOSE_CURSOR(c);

END;

/

v) Multiple row Update.

CREATE OR REPLACE PROCEDURE multi_Row_update

(c1 NUMBER, c2 NUMBER, r OUT DBMS_SQL.NUMBER_TABLE) IS

c NUMBER;

n NUMBER;

BEGIN

c := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(c, ‘UPDATE tab SET c1 = :bnd1 WHERE c2 = :bnd2 ‘ ||

‘RETURNING c1*c2 INTO :bnd3’, DBMS_SQL.NATIVE);

DBMS_SQL.BIND_VARIABLE(c, ‘bnd1’, c1);

DBMS_SQL.BIND_VARIABLE(c, ‘bnd2’, c2);

DBMS_SQL.BIND_ARRAY(c, ‘bnd3’, r);

n := DBMS_SQL.EXECUTE(c);

DBMS_SQL.VARIABLE_VALUE(c, ‘bnd3’, r);– get value of outbind variable

DBMS_SQL.CLOSE_CURSOR(c);

END;

/

vi) Multiple row delete

CREATE OR REPLACE PROCEDURE multi_row_delete

(c1 DBMS_SQL.NUMBER_TABLE,

r OUT DBMS_SQL.NUMBER_TABLE) is

c NUMBER;

n NUMBER;

BEGIN

c := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(c, ‘DELETE FROM tab WHERE c1 = :bnd1’ ||

‘RETURNING c1*c2 INTO :bnd2’, DBMS_SQL.NATIVE);

Note: bnd1 and bnd2 can be array as well. The value of the

expression for all the rows updated will be in bnd3. There is no way

of differentiating which rows got updated of each value of bnd1

and bnd2.

Using DBMS_SQL

DBMS_SQL 100-23

DBMS_SQL.BIND_ARRAY(c, ‘bnd1’, c1);

DBMS_SQL.BIND_ARRAY(c, ‘bnd2’, r);

n := DBMS_SQL.EXECUTE(c);

DBMS_SQL.VARIABLE_VALUE(c, ‘bnd2’, r);– get value of outbind variable

DBMS_SQL.CLOSE_CURSOR(c);

END;

/

vii) Out-bind in bulk PL/SQL

CREATE OR REPLACE PROCEDURE foo (n NUMBER, square OUT NUMBER) IS

BEGIN square := n * n; END;/

CREATE OR REPLACE PROCEDURE bulk_plsql

(n DBMS_SQL.NUMBER_TABLE, square OUT DBMS_SQL.NUMBER_TABLE) IS

c NUMBER;

r NUMBER;

BEGIN

c := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(c, ‘BEGIN foo(:bnd1, :bnd2); END;’, DBMS_SQL.NATIVE);

DBMS_SQL.BIND_ARRAY(c, ‘bnd1’, n);

DBMS_SQL.BIND_ARRAY(c, ‘bnd2’, square);

r := DBMS_SQL.EXECUTE(c);

DBMS_SQL.VARIABLE_VALUE(c, ‘bnd2’, square);

END;

/

Note: DBMS_SQL.BIND_ARRAY of number_Table internally

binds a number. The number of times statement is run depends on

the number of elements in an inbind array.

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.