Oracle Database DBMS SQL

https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9
Admin
DBMS_SQL is an Oracle Database PL/SQL package that provides a dynamic SQL interface to parse and...
Tokens:26,163
Snippets:232
Trust Score:9.7
Update:3 months ago
Show doc for...
Tokens:
Raw
### DESCRIBE_COLUMNS3 Usage Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Example demonstrating how to use DESCRIBE_COLUMNS3 to inspect user-defined types in a table.

```sql
CREATE TYPE PROJECT_T AS OBJECT
      ( projname          VARCHAR2(20),
        mgr               VARCHAR2(20))
/
 
CREATE TABLE projecttab(deptno NUMBER, project HR.PROJECT_T)
/
 
DECLARE
  curid      NUMBER;
  desctab    DBMS_SQL.DESC_TAB3;
  colcnt     NUMBER;
  sql_stmt   VARCHAR2(200) := 'select * from projecttab';
BEGIN
 
    curid := DBMS_SQL.OPEN_CURSOR;
 
    DBMS_SQL.PARSE(curid, sql_stmt, DBMS_SQL.NATIVE);
 
    DBMS_SQL.DESCRIBE_COLUMNS3(curid, colcnt, desctab);
 
    FOR i IN 1 .. colcnt LOOP
      IF desctab(i).col_type = 109 THEN
        DBMS_OUTPUT.PUT(desctab(i).col_name || ' is user-defined type: ');
        DBMS_OUTPUT.PUT_LINE(desctab(i).col_schema_name || '.' ||
                             desctab(i).col_type_name);
      END IF;
    END LOOP;
 
    DBMS_SQL.CLOSE_CURSOR(curid);
END;
/
 
Output:
 
PROJECT is user-defined type: HR.PROJECT_T
```

--------------------------------

### Bind Package Variable Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Example demonstrating the use of BIND_VARIABLE_PKG to bind a collection type variable to a dynamic SQL cursor.

```PL/SQL
CREATE OR REPLACE PACKAGE ty_pkg AS 
   TYPE rec IS RECORD ( n1 NUMBER, n2 NUMBER); 
   TYPE trec IS TABLE OF REC INDEX BY BINARY_INTEGER; 
   TYPE trect IS TABLE OF NUMBER;
   TYPE trecv IS VARRAY(100) OF NUMBER;
END ty_pkg; 
/
CREATE OR REPLACE PROCEDURE dyn_sql_ibbi AS 
  dummy NUMBER; 
  cur   NUMBER; 
  v1 ty_pkg.trec; 
  str VARCHAR2(3000); 
  n1 NUMBER;
  n2 NUMBER;
BEGIN 
  FOR i in 1..3 LOOP
     v1(i).n1 := i*10; 
     v1(i).n2 := i*20;
  END LOOP; 
  str := 'SELECT * FROM TABLE(:v1)' ; 
  cur := DBMS_SQL.OPEN_CURSOR(); 
  DBMS_SQL.PARSE(cur, str, DBMS_SQL.NATIVE); 
  DBMS_SQL.BIND_VARIABLE_PKG(cur, ':v1', v1); 
  dummy := DBMS_SQL.EXECUTE(cur); 
  DBMS_SQL.DEFINE_COLUMN(cur, 1, n1);   
  DBMS_SQL.DEFINE_COLUMN(cur, 2, n2);   

  LOOP 
    IF DBMS_SQL.FETCH_ROWS(cur) > 0 THEN 
      -- get column values of the row 
       DBMS_SQL.COLUMN_VALUE(cur, 1, n1); 
       DBMS_SQL.COLUMN_VALUE(cur, 2, n2); 
       DBMS_OUTPUT.PUT_LINE('n1 = '||n1||' n2 = '||n2); 
    ELSE 
      -- No more rows 
       EXIT; 
    END IF; 
  END LOOP; 
  DBMS_SQL.CLOSE_CURSOR(cur); 
END dyn_sql_ibbi;
/
EXEC dyn_sql_ibbi;
```

--------------------------------

### DBMS_SQL.PARSE Usage Examples

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Examples showing how to pass SQL statements to the PARSE procedure.

```plsql
DBMS_SQL.PARSE(cursor1, 'BEGIN proc; END;', 2);
DBMS_SQL.PARSE(cursor1, 'INSERT INTO tab VALUES(1)', 2);
```

--------------------------------

### BIND_VARIABLE Call Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Example showing how to call BIND_VARIABLE with or without the leading colon in the name parameter.

```PL/SQL
BIND_VARIABLE(cursor_name, ':X', 3500); 

or

BIND_VARIABLE (cursor_name, 'X', 3500);
```

--------------------------------

### Bulk Fetching with DEFINE_ARRAY

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Example demonstrating how to use DEFINE_ARRAY to perform bulk fetches from a SELECT statement.

```PL/SQL
PROCEDURE BULK_PLSQL(deptid NUMBER)
    TYPE namelist IS TABLE OF employees.last_name%TYPE;
    TYPE sallist IS TABLE OF employees.salary%TYPE;
    names    namelist;
    sals     sallist;
    c        NUMBER;
    r        NUMBER;
    sql_stmt VARCHAR2(32767) :=
        'SELECT last_name, salary FROM employees WHERE department_id = :b1';
 
BEGIN
    c := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(c, sql_stmt, dbms_sql.native);
 
    DBMS_SQL.BIND_VARIABLE(c, 'b1', deptid);
 
    DBMS_SQL.DEFINE_ARRAY(c, 1, names, 5);
    DBMS_SQL.DEFINE_ARRAY(c, 2, sals, 5);
 
    r := DBMS_SQL.EXECUTE(c);
 
    LOOP
      r := DBMS_SQL.FETCH_ROWS(c);
      DBMS_SQL.COLUMN_VALUE(c, 1, names);
      DBMS_SQL.COLUMN_VALUE(c, 2, sals);
      EXIT WHEN r != 5;
    END LOOP;
 
    DBMS_SQL.CLOSE_CURSOR(c);
 
    -- loop through the names and sals collections
    FOR i IN names.FIRST .. names.LAST  LOOP
      DBMS_OUTPUT.PUT_LINE('Name = ' || names(i) || ', salary = ' || sals(i));
    END LOOP;
END;
/
```

--------------------------------

### Dynamic SQL using VARIABLE_VALUE_PKG

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Example demonstrating the use of VARIABLE_VALUE_PKG to retrieve the value of a bind variable of a collection type.

```plsql
CREATE OR REPLACE PACKAGE ty_pkg AS 
TYPE rec IS RECORD 
   ( n1 NUMBER, 
    n2 NUMBER); 
TYPE trect IS TABLE OF NUMBER; 
END ty_pkg; 
/
CREATE OR REPLACE PROCEDURE dyn_sql_nt  AS 
  dummy NUMBER; 
  cur   NUMBER; 
  v1 ty_pkg.trect; 
  v2 ty_pkg.trect; 
  str VARCHAR2(3000); 
BEGIN 
  v1 := ty_pkg.trect(1000); 
  str := 'declare v1 ty_pkg.trect;  begin v1:=:v1;  v1(1) := 2000; :v2 := v1; end;' ; 
  cur := DBMS_SQL.OPEN_CURSOR(); 
  DBMS_SQL.PARSE(cur, str, DBMS_SQL.NATIVE); 
  DBMS_SQL.BIND_VARIABLE_PKG(cur, ':v1', v1); 
  DBMS_SQL.BIND_VARIABLE_PKG(cur, ':v2', v2); 
  dummy := DBMS_SQL.EXECUTE(cur); 
  DBMS_SQL.VARIABLE_VALUE_PKG(cur, ':v2', v2); 
  DBMS_OUTPUT.PUT_LINE('n =  ' 
  || V2(1)); 
  DBMS_SQL.CLOSE_CURSOR(cur); 
END dyn_sql_nt;
/
EXEC dyn_sql_nt;
```

--------------------------------

### Converting Ref Cursor to DBMS_SQL Cursor

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Example demonstrating the transformation of a native dynamic SQL ref cursor into a DBMS_SQL cursor for further processing.

```plsql
CREATE OR REPLACE PROCEDURE DO_QUERY(sql_stmt VARCHAR2) IS
  TYPE CurType IS REF CURSOR;
  src_cur         CurType;
  curid           NUMBER;
  desctab         DBMS_SQL.DESC_TAB;
  colcnt          NUMBER;
  namevar         VARCHAR2(50);
  numvar          NUMBER;
  datevar         DATE;
  empno           NUMBER := 100;
BEGIN
 
 
    -- sql_stmt := 'select ...... from employees where employee_id = :b1';
    OPEN src_cur FOR sql_stmt USING empno;
 
    -- Switch from native dynamic SQL to DBMS_SQL
    curid := DBMS_SQL.TO_CURSOR_NUMBER (src_cur);
 
    DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);
 
    -- Define columns
    FOR i IN 1 .. colcnt LOOP
        IF desctab(i).col_type = 2 THEN
           DBMS_SQL.DEFINE_COLUMN(curid, i, numvar);
        ELSIF desctab(i).col_type = 12 THEN
            DBMS_SQL.DEFINE_COLUMN(curid, i, datevar);
.......
         ELSE
            DBMS_SQL.DEFINE_COLUMN(curid, i, namevar, 25);
         END IF;
    END LOOP;
 
  -- Fetch Rows
    WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
        FOR i IN 1 .. colcnt LOOP
          IF (desctab(i).col_type = 1) THEN
            DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
         ELSIF (desctab(i).col_type = 2) THEN
            DBMS_SQL.COLUMN_VALUE(curid, i, numvar);
          ELSIF (desctab(i).col_type = 12) THEN
            DBMS_SQL.COLUMN_VALUE(curid, i, datevar);
....
          END IF;
        END LOOP;
    END LOOP;
 
    DBMS_SQL.CLOSE_CURSOR(curid);
END;
/ 
```

--------------------------------

### GET_NEXT_RESULT Usage Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Demonstrates iterating through statement results using GET_NEXT_RESULT.

```PL/SQL
DECLARE
  c  INTEGER;
  rc SYS_REFCURSOR;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR(treat_as_client_for_results => TRUE);
  DBMS_SQL.PARSE(c                  => c,
                 statement          => 'begin proc; end;');
  DBMS_SQL.EXECUTE(c);
  LOOP
    BEGIN
      DBMS_SQL.GET_NEXT_RESULT(c, rc);
    EXCEPTIONS
      WHEN no_data_found THEN
        EXIT;
    END;
    LOOP
      FETCH rc INTO ...
      ...
    END LOOP;
  END LOOP;
END;
```

--------------------------------

### SQL Statement Bind Variable Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Example of a SQL statement using a bind variable with a leading colon.

```SQL
SELECT emp_name FROM emp WHERE SAL > :X;
```

--------------------------------

### COLUMN_VALUE Execution Result

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Expected output after executing the vector data fetch example.

```text
fetched ID 1: [1.11000001E+000,2.22000003E+000,3.32999992E+000]
fetched ID 2: [4.44000006E+000,5.55000019E+000,6.65999985E+000]
fetched ID 3: [7.76999998E+000,8.88000011E+000,9.98999977E+000]
```

--------------------------------

### Returning Results with RETURN_RESULT

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Example of a stored procedure that opens multiple ref cursors and returns them to the caller.

```plsql
CREATE PROCEDURE proc AS
  rc1 sys_refcursor;
  rc2 sys_refcursor;
BEGIN
  OPEN rc1 FOR SELECT * FROM t1;
  DBMS_SQL.RETURN_RESULT(rc1);
  OPEN rc2 FOR SELECT * FROM t2;
  DBMS_SQL.RETURN_RESULT(rc2);
END;
/ 
```

--------------------------------

### Define an Array for Bulk Fetching

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Demonstrates using DEFINE_ARRAY to fetch rows into a numeric PL/SQL table using a specified starting index.

```PL/SQL
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;
/
```

--------------------------------

### Use COLUMN_VALUE with VECTOR Data Type

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Example demonstrating the use of COLUMN_VALUE with DEFINE_ARRAY and VECTOR_Table to fetch vector data.

```sql
DROP TABLE dbmsSqlTable;
CREATE TABLE dbmsSqlTable (embedding VECTOR(3, float32), id NUMBER);
INSERT INTO dbmsSqlTable VALUES ('[1.11, 2.22, 3.33]', 1);
INSERT INTO dbmsSqlTable VALUES ('[4.44, 5.55, 6.66]', 2);
INSERT INTO dbmsSqlTable VALUES ('[7.77, 8.88, 9.99]', 3);

SET SERVEROUTPUT ON;

DECLARE
  cur NUMBER;
  stmt_1 VARCHAR2(255) := 'SELECT embedding FROM dbmsSqlTable ORDER BY id';
  vecArray DBMS_SQL.VECTOR_TABLE;
  rowsProcessed NUMBER;
BEGIN
  cur := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(cur, stmt_1, DBMS_SQL.NATIVE);
  DBMS_SQL.DEFINE_ARRAY(cur, 1, vecArray, 3, 1);
  rowsProcessed := DBMS_SQL.EXECUTE_AND_FETCH(cur);
 
  FOR i IN 1..rowsProcessed LOOP
    DBMS_SQL.COLUMN_VALUE(cur, 1, vecArray);
    DBMS_OUTPUT.PUT_LINE('fetched ID ' || i || ': ' || FROM_VECTOR(vecArray(i)));
   END LOOP;
  DBMS_SQL.CLOSE_CURSOR(cur);
END;
/
```

--------------------------------

### Create a Multi-Column Table

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Defines a sample table structure used for demonstrating multi-column bulk fetching.

```SQL
CREATE TABLE multi_tab (num NUMBER, 
                        dat1 DATE, 
                        var VARCHAR2(24), 
                        dat2 DATE)
```

--------------------------------

### Bulk UPDATE with DBMS_SQL

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Demonstrates updating salaries for multiple employees using array binding.

```PL/SQL
DECLARE
  stmt VARCHAR2(200);
  empno_array     DBMS_SQL.NUMBER_TABLE;
  salary_array    DBMS_SQL.NUMBER_TABLE;
  c               NUMBER;
  dummy           NUMBER;
BEGIN
 
  empno_array(1):= 7369;
  empno_array(2):= 7876;
  empno_array(3):= 7900;
  empno_array(4):= 7934;
 
  salary_array(1) := 10000;
  salary_array(2) := 10000;
  salary_array(3) := 10000;
  salary_array(4) := 10000;
 
  stmt := 'update emp set sal = :salary_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, ':salary_array', salary_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;
/
```

--------------------------------

### Execute DELETE with DBMS_SQL

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Demonstrates the basic lifecycle of a DBMS_SQL cursor: opening, parsing, binding, executing, and closing.

```PL/SQL
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;
```

--------------------------------

### Bind and Define User-defined Types in DBMS_SQL

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Demonstrates how to use DBMS_SQL to perform DML operations and column definitions with VARRAY types.

```sql
CREATE TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30)
/
 
CREATE TABLE depts (region VARCHAR2(25), dept_names dnames_var)
/
INSERT INTO depts VALUES('Europe', dnames_var('Shipping','Sales','Finance'))
/
INSERT INTO depts VALUES('Americas', dnames_var('Sales','Finance','Shipping'))
/
INSERT INTO depts VALUES('Asia', dnames_var('Finance','Payroll','Shipping','Sales'))
/
 
CREATE OR REPLACE PROCEDURE update_depts(new_dnames dnames_var, region VARCHAR2) IS
   some_dnames dnames_var;
   c      NUMBER;
   r      NUMBER;
   sql_stmt VARCHAR2(32767) :=
    'UPDATE depts SET dept_names = :b1 WHERE region = :b2 RETURNING dept_names INTO :b3';
 
BEGIN
 
   c := DBMS_SQL.OPEN_CURSOR;
 
   DBMS_SQL.PARSE(c, sql_stmt, dbms_sql.native);
 
   DBMS_SQL.BIND_VARIABLE(c, 'b1', new_dnames);
   DBMS_SQL.BIND_VARIABLE(c, 'b2', region);
   DBMS_SQL.BIND_VARIABLE(c, 'b3', some_dnames);
 
   r := DBMS_SQL.EXECUTE(c);
 
   -- Get value of outbind variable
   DBMS_SQL.VARIABLE_VALUE(c, 'b3', some_dnames);
 
   DBMS_SQL.CLOSE_CURSOR(c);
 
   -- select dept_names
   sql_stmt := 'SELECT dept_names FROM depts WHERE region = :b1';
 
   c := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(c, sql_stmt, dbms_sql.native);
 
   DBMS_SQL.DEFINE_COLUMN(c, 1, some_dnames);
   DBMS_SQL.BIND_VARIABLE(c, 'b1', region);
 
   r := DBMS_SQL.EXECUTE_AND_FETCH(c);
 
   DBMS_SQL.COLUMN_VALUE(c, 1, some_dnames);
 
   DBMS_SQL.CLOSE_CURSOR(c);
 
    -- loop through some_dnames collections
    FOR i IN some_dnames.FIRST .. some_dnames.LAST  LOOP
        DBMS_OUTPUT.PUT_LINE('Dept. Name = ' || some_dnames(i) || ' Updated!');
    END LOOP;
END;
/
 
DECLARE
  new_dnames dnames_var;
BEGIN
  new_dnames := dnames_var('Benefits', 'Advertising', 'Contracting', 
                           'Executive', 'Marketing');
  update_depts(new_dnames, 'Asia');
END;
/
```

--------------------------------

### Execute bulk PL/SQL procedure calls

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Demonstrates calling a PL/SQL procedure in bulk using array binding for input and output parameters.

```plsql
      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;
     /
```

--------------------------------

### DBMS_SQL.DEFINE_ARRAY

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

The DEFINE_ARRAY procedure is used to define a PL/SQL table for bulk fetching of data from a cursor. It associates a cursor column with a PL/SQL table, specifying the number of rows to fetch and the starting index.

```APIDOC
## DBMS_SQL.DEFINE_ARRAY

### Description
Defines a PL/SQL table to receive data fetched from a cursor. This is used in conjunction with FETCH_ROWS and COLUMN_VALUE to perform bulk data retrieval.

### Syntax
DBMS_SQL.DEFINE_ARRAY(c, position, table_variable, count, index);

### Parameters
- **c** (NUMBER) - The cursor ID returned by OPEN_CURSOR.
- **position** (NUMBER) - The position of the column in the SELECT list.
- **table_variable** (PL/SQL Table) - The PL/SQL table variable to store the fetched data.
- **count** (NUMBER) - The number of rows to fetch in each batch.
- **index** (NUMBER) - The starting index in the PL/SQL table for the fetched rows.
```

--------------------------------

### Bulk Fetch Multiple Columns

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Shows how to define multiple arrays to fetch data from a table into corresponding PL/SQL tables.

```PL/SQL
DECLARE
  c       NUMBER;
  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);

/* 



*/

EXCEPTION WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(c) THEN
      DBMS_SQL.CLOSE_CURSOR(c);
    END IF;
    RAISE;
END;
/
```

--------------------------------

### Bulk INSERT with DBMS_SQL

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Demonstrates inserting multiple rows into the emp table using array binding.

```PL/SQL
DECLARE
  stmt VARCHAR2(200);
  empno_array      DBMS_SQL.NUMBER_TABLE;
  empname_array    DBMS_SQL.VARCHAR2_TABLE;
  jobs_array       DBMS_SQL.VARCHAR2_TABLE;
  mgr_array        DBMS_SQL.NUMBER_TABLE;
  hiredate_array   DBMS_SQL.VARCHAR2_TABLE;
  sal_array        DBMS_SQL.NUMBER_TABLE;
  comm_array       DBMS_SQL.NUMBER_TABLE;
  deptno_array     DBMS_SQL.NUMBER_TABLE;
  c                NUMBER;
  dummy            NUMBER;
BEGIN
  empno_array(1):= 9001;
  empno_array(2):= 9002;
  empno_array(3):= 9003;
  empno_array(4):= 9004;
  empno_array(5):= 9005;
  empno_array(6):= 9006;
  empno_array(7):= 9007;

  empname_array(1) := 'Dopey';
  empname_array(2) := 'Grumpy';
  empname_array(3) := 'Doc';
  empname_array(4) := 'Happy';
  empname_array(5) := 'Bashful';
  empname_array(6) := 'Sneezy';
  empname_array(7) := 'Sleepy';

  jobs_array(1) := 'Miner';
  jobs_array(2) := 'Miner';
  jobs_array(3) := 'Miner';
  jobs_array(4) := 'Miner';
  jobs_array(5) := 'Miner';
  jobs_array(6) := 'Miner';
  jobs_array(7) := 'Miner';

  mgr_array(1) := 9003;
  mgr_array(2) := 9003;
  mgr_array(3) := 9003;
  mgr_array(4) := 9003;
  mgr_array(5) := 9003;
  mgr_array(6) := 9003;
  mgr_array(7) := 9003;

  hiredate_array(1) := '06-DEC-2006';
  hiredate_array(2) := '06-DEC-2006';
  hiredate_array(3) := '06-DEC-2006';
  hiredate_array(4) := '06-DEC-2006';
  hiredate_array(5) := '06-DEC-2006';
  hiredate_array(6) := '06-DEC-2006';
  hiredate_array(7) := '06-DEC-2006';

  sal_array(1):= 1000;
  sal_array(2):= 1000;
  sal_array(3):= 1000;
  sal_array(4):= 1000;
  sal_array(5):= 1000;
  sal_array(6):= 1000;
  sal_array(7):= 1000;

  comm_array(1):= 0;
  comm_array(2):= 0;
  comm_array(3):= 0;
  comm_array(4):= 0;
  comm_array(5):= 0;
  comm_array(6):= 0;
  comm_array(7):= 0;

  deptno_array(1):= 11;
  deptno_array(2):= 11;
  deptno_array(3):= 11;
  deptno_array(4):= 11;
  deptno_array(5):= 11;
  deptno_array(6):= 11;
  deptno_array(7):= 11;

  stmt := 'INSERT INTO emp VALUES(
     :num_array, :name_array, :jobs_array, :mgr_array, :hiredate_array, 
     :sal_array, :comm_array, :deptno_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);
  DBMS_SQL.BIND_ARRAY(c, ':jobs_array', jobs_array);
  DBMS_SQL.BIND_ARRAY(c, ':mgr_array', mgr_array);
  DBMS_SQL.BIND_ARRAY(c, ':hiredate_array', hiredate_array);
  DBMS_SQL.BIND_ARRAY(c, ':sal_array', sal_array);
  DBMS_SQL.BIND_ARRAY(c, ':comm_array', comm_array);
  DBMS_SQL.BIND_ARRAY(c, ':deptno_array', deptno_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;
/
SHOW ERRORS;
```

--------------------------------

### DBMS_SQL Procedures and Functions

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Overview of key procedures and functions available in the DBMS_SQL package for managing dynamic SQL execution.

```APIDOC
## DBMS_SQL Procedures and Functions

### Description
The DBMS_SQL package allows for the execution of dynamic SQL statements. Key operations include:
- **DEFINE_COLUMN / DEFINE_COLUMN_LONG / DEFINE_ARRAY**: Specifies variables to receive SELECT values.
- **EXECUTE**: Runs a SQL statement.
- **FETCH_ROWS**: Retrieves rows satisfying a query.
- **EXECUTE_AND_FETCH**: Combines execution and fetching for efficiency.
- **COLUMN_VALUE / VARIABLE_VALUE**: Retrieves values from columns or output variables.
- **CLOSE_CURSOR**: Closes a cursor when no longer needed.
- **OPEN_CURSOR**: Opens a cursor for a session.
- **PARSE**: Parses a SQL statement.
- **BIND_VARIABLE**: Binds a value to a placeholder in a SQL statement.
```

--------------------------------

### Syntax for VARIABLE_VALUE_PKG

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

The basic syntax for calling the VARIABLE_VALUE_PKG procedure.

```plsql
DBMS_SQL.VARIABLE_VALUE_PKG ( 
   c                 IN   INTEGER, 
   name              IN   VARCHAR2,
   value             OUT NOCOPY <table_type>); 
```

--------------------------------

### Use DBMS_SQL.BIND_ARRAY with VECTOR

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Demonstrates performing a bulk insert into a table containing a VECTOR column using DBMS_SQL.BIND_ARRAY.

```sql
CREATE TABLE vec_seq_table(
  col_vector VECTOR(1, float32),
  col_seq NUMBER
);

SET SERVEROUTPUT ON;
DECLARE
  cur NUMBER;
  stmt_1 VARCHAR2(255) :=
    'INSERT INTO vec_seq_table(col_vector, col_seq) VALUES (:1, :2)';
  number_array DBMS_SQL.NUMBER_TABLE;
  v_array DBMS_SQL.VECTOR_TABLE;
  rowsProcessed NUMBER;
BEGIN
  FOR cnt IN 1 .. 5 LOOP
    v_array(cnt) := TO_VECTOR('[' || cnt || ']', 1, float32);
    number_array(cnt) := cnt;
  END LOOP;

  cur := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(cur, stmt_1, DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_ARRAY(cur, ':1', v_array);
  DBMS_SQL.BIND_ARRAY(cur, ':2', number_array);
  rowsProcessed := DBMS_SQL.EXECUTE(cur);
  DBMS_SQL.CLOSE_CURSOR(cur);
  COMMIT;
END;
/

SELECT * FROM vec_seq_table ORDER BY col_seq;
```

--------------------------------

### EXECUTE_AND_FETCH Function Syntax

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Syntax definition for the EXECUTE_AND_FETCH function.

```sql
DBMS_SQL.EXECUTE_AND_FETCH (
   c              IN INTEGER,
   exact          IN BOOLEAN DEFAULT FALSE)
  RETURN INTEGER;
```

--------------------------------

### Define Column Supported Datatypes

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

List of supported datatypes for the DEFINE_COLUMN procedure.

```text
BINARY_DOUBLE
BINARY_FLOAT
BFILE
BLOB
BOOLEAN
CLOB CHARACTER SET ANY_CS
DATE
DSINTERVAL_UNCONSTRAINED
JSON
NUMBER
TIME_UNCONSTRAINED
TIME_TZ_UNCONSTRAINED
TIMESTAMP_LTZ_UNCONSTRAINED
TIMESTAMP_TZ_UNCONSTRAINED
TIMESTAMP_UNCONSTRAINED
UROWID
VECTOR
YMINTERVAL_UNCONSTRAINED
user-defined object types
collections (VARRAYs and nested tables)
REFs
Opaque types
```

--------------------------------

### DESCRIBE_COLUMNS2 Procedure Syntax

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Syntax definition for the DESCRIBE_COLUMNS2 procedure.

```sql
DBMS_SQL.DESCRIBE_COLUMNS2 ( 
   c              IN  INTEGER, 
   col_cnt        OUT INTEGER, 
   desc_t         OUT DESC_TAB2);
```

--------------------------------

### DESCRIBE_COLUMNS3 Procedure Syntax

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Syntax definition for the DESCRIBE_COLUMNS3 procedure.

```sql
DBMS_SQL.DESCRIBE_COLUMNS3 ( 
   c              IN  INTEGER, 
   col_cnt        OUT INTEGER, 
   desc_t         OUT DESC_TAB3);

BMS_SQL.DESCRIBE_COLUMNS3 ( 
   c              IN  INTEGER, 
   col_cnt        OUT INTEGER, 
   desc_t         OUT DESC_TAB4);
```

--------------------------------

### OPEN_CURSOR Functions

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Opens a new cursor with optional security levels. Explicitly close the cursor using CLOSE_CURSOR when finished.

```PL/SQL
DBMS_SQL.OPEN_CURSOR (
   treat_as_client_for_results    IN     BOOLEAN    DEFAULT FALSE) 
  RETURN INTEGER;

DBMS_SQL.OPEN_CURSOR (
   security_level                 IN     INTEGER,
   treat_as_client_for_results    IN     BOOLEAN    DEFAULT FALSE) 
  RETURN INTEGER;
```

```PL/SQL
pragma restrict_references(open_cursor,RNDS,WNDS);
```

--------------------------------

### EXECUTE Function Syntax

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Syntax definition for the EXECUTE function.

```sql
DBMS_SQL.EXECUTE (
   c   IN INTEGER)
  RETURN INTEGER;
```

--------------------------------

### EXECUTE_AND_FETCH Pragma

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Pragma restriction for the EXECUTE_AND_FETCH function.

```sql
pragma restrict_references(execute_and_fetch,WNDS);
```

--------------------------------

### Bind Package Variable Syntax

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Syntax for the BIND_VARIABLE_PKG procedure, which binds a variable to a cursor based on its name.

```PL/SQL
DBMS_SQL.BIND_VARIABLE_PKG (
   c              IN INTEGER,
   name           IN VARCHAR2,
   value          IN <datatype>);
```

--------------------------------

### IS_OPEN Pragma

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Restrict references pragma for the IS_OPEN function.

```PL/SQL
pragma restrict_references(is_open,RNDS,WNDS);
```

--------------------------------

### DBMS_SQL.DESCRIBE_COLUMNS

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Describes the columns for a cursor opened and parsed through DBMS_SQL.

```APIDOC
## DBMS_SQL.DESCRIBE_COLUMNS

### Description
Describes the columns for a cursor opened and parsed through DBMS_SQL, filling a describe table with column metadata.

### Syntax
DBMS_SQL.DESCRIBE_COLUMNS (c IN INTEGER, col_cnt OUT INTEGER, desc_t OUT DESC_TAB);

### Parameters
- **c** (INTEGER) - ID number of the cursor.
- **col_cnt** (INTEGER) - Number of columns in the select list.
- **desc_t** (DESC_TAB) - Describe table to fill with column descriptions.
```

--------------------------------

### Parse SQL Statement with Constant

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

This code fails to compile on the client because it references a package constant.

```plsql
DBMS_SQL.PARSE(cur_hdl, stmt_str, DBMS_SQL.NATIVE); -- uses constant DBMS_SQL.NATIVE
```

--------------------------------

### BIND_ARRAY Supported Data Types

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

List of supported variable and table type pairs for the BIND_ARRAY procedure.

```text
bdbl_tab     Binary_Double_Table
bf_tab       Bfile_Table
bflt_tab     Binary_Float_Table
bl_tab       Blob_Table
bool_tab     Boolean_Table
c_tab        Varchar2_Table
c_tab        Varchar2A
cl_tab       Clob_Table
d_tab        Date_Table
ids_tab      Interval_Day_To_Second_Table
iym_tab      Interval_Year_To_Month_Table
j_tab        Json_Table
n_tab        Number_Table
tm_tab       Time_Table
tms_tab      Timestamp_Table
tstz_tab     Timestamp_With_ltz_Table
tstz_tab     Timestamp_With_Time_Zone_Table
ttz_tab      Time_With_Time_Zone_Table
ur_tab       Urowid_Table
v_tab        Vector_Table
```

--------------------------------

### Define Column VARCHAR2 Syntax

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Syntax for defining a VARCHAR2 column with a specified size.

```PL/SQL
DBMS_SQL.DEFINE_COLUMN (
   c              IN INTEGER,
   position       IN INTEGER,
   column         IN VARCHAR2 CHARACTER SET ANY_CS,
   column_size    IN INTEGER);
```

--------------------------------

### DEFINE_ARRAY Procedure Syntax

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Syntax definition for defining a collection for batch row fetching.

```PL/SQL
DBMS_SQL.DEFINE_ARRAY (
   c                   IN INTEGER, 
   position            IN INTEGER,
   <variable>          IN <table_type> 
   cnt                 IN INTEGER, 
   lower_bound         IN INTEGER);
```

--------------------------------

### DBMS_SQL.TO_REFCURSOR

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Transforms an opened, parsed, and executed DBMS_SQL cursor into a PL/SQL manageable REF CURSOR.

```APIDOC
## DBMS_SQL.TO_REFCURSOR

### Description
This function takes an OPENed, PARSEd, and EXECUTEd cursor and transforms it into a PL/SQL manageable REF CURSOR that can be consumed by PL/SQL native dynamic SQL.

### Syntax
DBMS_SQL.TO_REFCURSOR(cursor_number IN OUT INTEGER) RETURN SYS_REFCURSOR;

### Parameters
- **cursor_number** (INTEGER) - Required - Cursor number of the cursor to be transformed into REF CURSOR.

### Return Values
Returns a PL/SQL REF CURSOR transformed from a DBMS_SQL cursor number.
```

--------------------------------

### Bulk DELETE with DBMS_SQL

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Demonstrates deleting rows based on an array of department numbers using a range-limited BIND_ARRAY call.

```PL/SQL
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;
/
```

--------------------------------

### DESCRIBE_COLUMNS3 Pragma

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Pragma restriction for the DESCRIBE_COLUMNS3 procedure.

```sql
PRAGMA RESTRICT_REFERENCES(describe_columns3,WNDS);
```

--------------------------------

### Execute Dynamic SQL Procedure

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

A generic procedure to parse and execute a provided SQL string, suitable for DDL or DML without binds.

```PL/SQL
CREATE OR REPLACE PROCEDURE exec(STRING IN varchar2) AS
    cursor_name INTEGER;
    ret INTEGER;
BEGIN
   cursor_name := DBMS_SQL.OPEN_CURSOR;
```

```PL/SQL
   DBMS_SQL.PARSE(cursor_name, string, DBMS_SQL.NATIVE);
   ret := DBMS_SQL.EXECUTE(cursor_name);
   DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
```

--------------------------------

### DESCRIBE_COLUMNS2 Pragma

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Pragma restriction for the DESCRIBE_COLUMNS2 procedure.

```sql
PRAGMA RESTRICT_REFERENCES(describe_columns2,WNDS);
```

--------------------------------

### DBMS_SQL.EXECUTE_AND_FETCH

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Executes the given cursor and fetches rows in a single operation.

```APIDOC
## DBMS_SQL.EXECUTE_AND_FETCH

### Description
Executes the given cursor and fetches rows. This function provides the same functionality as calling EXECUTE and then calling FETCH_ROWS, potentially reducing network round-trips.

### Syntax
DBMS_SQL.EXECUTE_AND_FETCH (c IN INTEGER, exact IN BOOLEAN DEFAULT FALSE) RETURN INTEGER;

### Parameters
- **c** (INTEGER) - ID number of the cursor to execute and fetch.
- **exact** (BOOLEAN) - Set to TRUE to raise an exception if the number of rows actually matching the query differs from one.

### Return Values
- Returns the number of rows actually fetched.
```

--------------------------------

### FETCH_ROWS Pragma

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Restrict references pragma for the FETCH_ROWS function.

```PL/SQL
pragma restrict_references(fetch_rows,WNDS);
```

--------------------------------

### Column Value Supported Types

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

List of supported data types for the COLUMN_VALUE procedure.

```text
BINARY_DOUBLE
BINARY_FLOAT
BFILE
BLOB
BOOLEAN
CLOB CHARACTER SET ANY_CS
DATE
DSINTERVAL_UNCONSTRAINED
JSON
NUMBER
TIME_TZ_UNCONSTRAINED
TIME_UNCONSTRAINED
TIMESTAMP_LTZ_UNCONSTRAINED
TIMESTAMP_TZ_UNCONSTRAINED
TIMESTAMP_UNCONSTRAINED
UROWID
VARCHAR2 CHARACTER SET ANY_CS
VECTOR
YMINTERVAL_UNCONSTRAINED
user-defined object types
collections (VARRAYs and nested tables)
REFs
Opaque types
```

--------------------------------

### DBMS_SQL.DESCRIBE_COLUMNS2

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Describes the specified columns of a query cursor.

```APIDOC
## DBMS_SQL.DESCRIBE_COLUMNS2

### Description
Describes the specified column. This is an alternative to the DESCRIBE_COLUMNS procedure.

### Syntax
DBMS_SQL.DESCRIBE_COLUMNS2 (c IN INTEGER, col_cnt OUT INTEGER, desc_t OUT DESC_TAB2);

### Parameters
- **c** (INTEGER) - ID number of the cursor for the columns being described.
- **col_cnt** (INTEGER) - Number of columns in the select list of the query.
- **desc_t** (DESC_TAB2) - Describe table to fill in with the description of each of the columns of the query.
```

--------------------------------

### Perform single-row DML operations with DBMS_SQL

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

These procedures demonstrate single-row INSERT, UPDATE, and DELETE operations using DBMS_SQL with outbind variables.

```PL/SQL
      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;
      /
```

```PL/SQL
      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;
      /
```

```PL/SQL
      CREATE OR REPLACE PROCEDURE single_Row_Delete
           (c1 NUMBER, r OUT NUMBER) is
      c NUMBER;
      n number;
      BEGIN
        c := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(c, 'DELETE FROM tab WHERE ROWNUM = :bnd1 ' ||
                      'RETURNING c1*c2 INTO :bnd2', DBMS_SQL.NATIVE);
        DBMS_SQL.BIND_VARIABLE(c, 'bnd1', c1);
        DBMS_SQL.BIND_VARIABLE(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;
      /
```

--------------------------------

### DBMS_SQL.OPEN_CURSOR

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Opens a new cursor and returns the cursor ID. Supports optional security levels and client result handling.

```APIDOC
## DBMS_SQL.OPEN_CURSOR

### Description
Opens a new cursor. The security_level parameter allows for application of fine-grained control to the security of the opened cursor.

### Syntax
DBMS_SQL.OPEN_CURSOR (treat_as_client_for_results IN BOOLEAN DEFAULT FALSE) RETURN INTEGER;
DBMS_SQL.OPEN_CURSOR (security_level IN INTEGER, treat_as_client_for_results IN BOOLEAN DEFAULT FALSE) RETURN INTEGER;

### Parameters
- **security_level** (INTEGER) - Optional - Specifies the level of security protection (0, 1, or 2). Default is 1.
- **treat_as_client_for_results** (BOOLEAN) - Optional - Allows the caller to set itself as the client to receive statement results.

### Return Values
Returns the cursor ID number of the new cursor.
```

--------------------------------

### Define Table Structure for Copy Procedure

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Defines the expected column structure for the source and destination tables used in the copy procedure.

```text
id        of type NUMBER
name      of type VARCHAR2(30)
birthdate of type DATE
```

--------------------------------

### Define Column Procedure Syntax

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Defines a column to be selected from a cursor. The column type is inferred from the provided column variable.

```PL/SQL
DBMS_SQL.DEFINE_COLUMN (
   c              IN INTEGER,
   position       IN INTEGER,
   column         IN <datatype>);
```

--------------------------------

### Define VARCHAR2_TABLE

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Defines a table type for VARCHAR2(4000) values indexed by binary integers.

```PL/SQL
TYPE varchar2_table IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
```

--------------------------------

### Supported Table Types for COLUMN_VALUE

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9

Mapping of parameter names to their corresponding table types for bulk operations.

```sql
bdbl_tab     Binary_Double_Table
bf_tab       Bfile_Table
bflt_tab     Binary_Float_Table
bl_tab       Blob_Table
bool_tab     Boolean_Table
c_tab        Varchar2_Table
c_tab        Varchar2A
cl_tab       Clob_Table
d_tab        Date_Table
ids_tab      Interval_Day_To_Second_Table
iym_tab      Interval_Year_To_Month_Table
j_tab        Json_table
n_tab        Number_Table
tm_tab       Time_Table
tms_tab      Timestamp_Table
tstz_tab     Timestamp_With_ltz_Table
tstz_tab     Timestamp_With_Time_Zone_Table
ttz_tab      Time_With_Time_Zone_Table
ur_tab       Urowid_Table
v_tab        Vector_Table
```
Tokens:
Raw