There may be situations where we need to run dynamic queries in our developments. We can utilize execute immediate statement or DBMS_SQL package in order to use dynamic sql in PL/SQL. In this post, I prepared a detailed example using DBMS_SQL package.
Code snippet is implemented in Oracle 19c.
declare
curid integer;
v_cnt NUMBER;
v_tab DBMS_SQL.desc_tab2;
fdb number;
v_row clob;
v_sql clob := 'select * from hr.employees where department_id = :department_id';
TYPE r_type IS RECORD
(
v2_column VARCHAR2(32767),
num_column NUMBER,
date_column DATE,
clob_column CLOB,
ids_column INTERVAL DAY TO SECOND,
iym_column INTERVAL YEAR TO MONTH,
ts_column TIMESTAMP,
tstz_column TIMESTAMP WITH TIME ZONE,
tsltz_column TIMESTAMP WITH LOCAL TIME ZONE
);
rec r_type;
Begin
curid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(curid, v_sql, dbms_sql.native);
DBMS_SQL.BIND_VARIABLE(curid, ':department_id', 60);
DBMS_SQL.describe_columns2(curid, v_cnt, v_tab);
FOR i IN 1 .. v_cnt
LOOP
IF (v_tab(i).col_type in (1, 96, 11, 208)) THEN --VARCHAR2, CHAR, ROWID, UROWID
DBMS_SQL.define_column(curid, i, CAST(NULL AS VARCHAR2), 32767);
ELSIF (v_tab(i).col_type = 2) THEN --NUMBER
DBMS_SQL.define_column(curid, i, CAST(NULL AS NUMBER));
ELSIF (v_tab(i).col_type = 12) THEN --DATE
DBMS_SQL.define_column(curid, i, CAST(NULL AS DATE));
ELSIF (v_tab(i).col_type = 112) THEN --CLOB
DBMS_SQL.define_column(curid, i, CAST(NULL AS CLOB));
ELSIF (v_tab(i).col_type = 180) THEN --TIMESTAMP
DBMS_SQL.define_column(curid, i, CAST(NULL AS TIMESTAMP));
ELSIF (v_tab(i).col_type = 181) THEN --TIMESTAMP WITH TIME ZONE
DBMS_SQL.define_column(curid, i, CAST(NULL AS TIMESTAMP WITH TIME ZONE));
ELSIF (v_tab(i).col_type = 182) THEN --INTERVAL YEAR TO MONTH
DBMS_SQL.define_column(curid, i, CAST(NULL AS INTERVAL YEAR TO MONTH));
ELSIF (v_tab(i).col_type = 183) THEN --INTERVAL DAY TO SECOND
DBMS_SQL.define_column(curid, i, CAST(NULL AS INTERVAL DAY TO SECOND));
ELSIF (v_tab(i).col_type = 231) THEN --TIMESTAMP WITH LOCAL TIME ZONE
DBMS_SQL.define_column(curid, i, CAST(NULL AS TIMESTAMP WITH LOCAL TIME ZONE));
ELSE
DBMS_SQL.define_column(curid, i, CAST(NULL AS VARCHAR2), 4000);
END IF;
END LOOP;
fdb := DBMS_SQL.execute (curid);
WHILE (DBMS_SQL.fetch_rows(curid) > 0) LOOP
FOR j IN 1..v_cnt
LOOP
IF (v_tab(j).col_type in (1, 96, 11, 208)) THEN
DBMS_SQL.COLUMN_VALUE(curid, j, rec.v2_column);
ELSIF (v_tab(j).col_type = 2) THEN
DBMS_SQL.COLUMN_VALUE(curid, j, rec.num_column);
ELSIF (v_tab(j).col_type = 12) THEN
DBMS_SQL.COLUMN_VALUE(curid, j, rec.date_column);
ELSIF (v_tab(j).col_type = 112) THEN
DBMS_SQL.COLUMN_VALUE(curid, j, rec.clob_column);
ELSIF (v_tab(j).col_type = 180) THEN
DBMS_SQL.COLUMN_VALUE(curid, j, rec.ts_column);
ELSIF (v_tab(j).col_type = 181) THEN
DBMS_SQL.COLUMN_VALUE(curid, j, rec.tstz_column);
ELSIF (v_tab(j).col_type = 182) THEN
DBMS_SQL.COLUMN_VALUE(curid, j, rec.iym_column);
ELSIF (v_tab(j).col_type = 183) THEN
DBMS_SQL.COLUMN_VALUE(curid, j, rec.ids_column);
ELSIF (v_tab(j).col_type = 231) THEN
DBMS_SQL.COLUMN_VALUE(curid, j, rec.tsltz_column);
ELSE
DBMS_SQL.COLUMN_VALUE(curid, j, rec.v2_column);
END IF;
v_row := v_row ||
case
when rec.v2_column is not null then rec.v2_column
when rec.num_column is not null then to_char(rec.num_column)
when rec.clob_column is not null then rec.clob_column
when rec.date_column is not null then to_char(rec.date_column)
when rec.ts_column is not null then to_char(rec.ts_column)
when rec.tstz_column is not null then to_char(rec.tstz_column)
when rec.iym_column is not null then to_char(rec.iym_column)
when rec.ids_column is not null then to_char(rec.ids_column)
when rec.tsltz_column is not null then to_char(rec.tsltz_column)
end ||' ';
rec := null;
END LOOP;
dbms_output.put_line(v_row);
v_row := '';
END LOOP;
DBMS_SQL.CLOSE_CURSOR(curid);
end;
103 Alexander Hunold AHUNOLD 590.423.4567 03-JAN-06 IT_PROG 9000 102 60
104 Bruce Ernst BERNST 590.423.4568 21-MAY-07 IT_PROG 6000 103 60
105 David Austin DAUSTIN 590.423.4569 25-JUN-05 IT_PROG 4800 103 60
106 Valli Pataballa VPATABAL 590.423.4560 05-FEB-06 IT_PROG 4800 103 60
107 Diana Lorentz DLORENTZ 590.423.5567 07-FEB-07 IT_PROG 4200 103 60
Note that, unlike execute immediate statement, when you use bind variable, DBMS_SQL bind by name, not by position. On the other hand, execute immediate always use positional notation as shown below.
declare
curid integer;
fdb number;
v_num1 number;
v_num2 number;
v_num3 number;
Begin
curid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(curid, 'select :b1, :b1, :b1 from dual', dbms_sql.native);
DBMS_SQL.BIND_VARIABLE(curid, ':b1', 10);
DBMS_SQL.BIND_VARIABLE(curid, ':b1', 20);
DBMS_SQL.BIND_VARIABLE(curid, ':b1', 30);
DBMS_SQL.define_column(curid, 1, v_num1);
DBMS_SQL.define_column(curid, 2, v_num2);
DBMS_SQL.define_column(curid, 3, v_num3);
fdb := DBMS_SQL.execute (curid);
WHILE (DBMS_SQL.fetch_rows(curid) > 0) LOOP
DBMS_SQL.COLUMN_VALUE(curid, 1, v_num1);
DBMS_SQL.COLUMN_VALUE(curid, 2, v_num2);
DBMS_SQL.COLUMN_VALUE(curid, 3, v_num3);
dbms_output.put_line(v_num1||', '||v_num2||', '||v_num3);
END LOOP;
DBMS_SQL.CLOSE_CURSOR(curid);
execute immediate 'select :b1, :b1, :b1 from dual'
into v_num1, v_num2, v_num3 using 10, 20, 30;
dbms_output.put_line(v_num1||', '||v_num2||', '||v_num3);
end;
30, 30, 30
10, 20, 30
DBMS_SQL.DESC_TAB vs DBMS_SQL.DESC_TAB2
As of Oracle 12c, column names can be greater than 32 characters. In DESC_REC2, the length of COL_NAME field has been expanded to the maximum possible size of VARCHAR2. Therefore, it is preferred to use DESC_TAB2 type. As a result, DESC_REC record type has been decrecated in favor of DESC_REC2.
type desc_rec is record (
col_type binary_integer := 0,
col_max_len binary_integer := 0,
col_name varchar2(32) := '',
col_name_len binary_integer := 0,
col_schema_name varchar2(32) := '',
col_schema_name_len binary_integer := 0,
col_precision binary_integer := 0,
col_scale binary_integer := 0,
col_charsetid binary_integer := 0,
col_charsetform binary_integer := 0,
col_null_ok boolean := TRUE);
type desc_tab is table of desc_rec index by binary_integer;
type desc_rec2 is record (
col_type binary_integer := 0,
col_max_len binary_integer := 0,
col_name varchar2(32767) := '',
col_name_len binary_integer := 0,
col_schema_name varchar2(32) := '',
col_schema_name_len binary_integer := 0,
col_precision binary_integer := 0,
col_scale binary_integer := 0,
col_charsetid binary_integer := 0,
col_charsetform binary_integer := 0,
col_null_ok boolean := TRUE);
type desc_tab2 is table of desc_rec2 index by binary_integer;