Examples

Example 1

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

CREATE OR REPLACE PROCEDURE exec(STRING IN varchar2) AS
    cursor_name INTEGER;
    ret INTEGER;
BEGIN
   cursor_name := DBMS_SQL.OPEN_CURSOR;
  
   --DDL statements are executed by the parse call, which
   --performs the implied commit
   DBMS_SQL.PARSE(cursor_name, string, DBMS_SQL.V7);
   ret := DBMS_SQL.EXECUTE(cursor_name);
   DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;

Creating such a procedure allows 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.

exec('create table acct(c1 integer)');

You could even call this procedure remotely, as shown in the following example. This allows you to perform remote DDL.

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

Example 2

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

-- This procedure copies rows from a given source table to a
-- given destination table assuming that both source and
-- destination tables have the following columns:
--   - ID of type NUMBER,
--   - NAME of type VARCHAR2(30),
--   - BIRTHDATE of type DATE.
  id                 NUMBER;
  name               VARCHAR2(30);
  birthdate          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.V7);
  DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, id);
  DBMS_SQL.DEFINE_COLUMN(source_cursor, 2, name, 30);
  DBMS_SQL.DEFINE_COLUMN(source_cursor, 3, birthdate);
  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, :name, :birthdate)',
                 DBMS_SQL.V7);

  -- 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);
      DBMS_SQL.COLUMN_VALUE(source_cursor, 2, name);
      DBMS_SQL.COLUMN_VALUE(source_cursor, 3, birthdate);
    
      -- 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', id);
      DBMS_SQL.BIND_VARIABLE(destination_cursor, 'name', name);
      DBMS_SQL.BIND_VARIABLE(destination_cursor, 'birhtdate',
                             birthdate);
      ignore := DBMS_SQL.EXECUTE(destination_cursor);
    ELSE
  
    -- no more row to copy
      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;