RDBMS

What is Dynamic SQL?

Usually the SQL statements of a program are known when the program is written (static SQL). Sometimes the command varies from execution to execution and must be constructed at run time – an SQL statement constructed “on the fly” is a dynamic SQL statement.

• Until Oracle8 the only way was the DBMS_SQL package. Oracle8i introduced native dynamic SQL – dynamic statements placed directly in PL/SQL – which is faster than DBMS_SQL.
• Dynamic SQL affects performance; use it only when static SQL is not possible.

When do we need it?

• To execute DDL or DCL commands that are not allowed statically in PL/SQL (e.g. DROP TABLE).
• To access a table or view whose name is known only at run time (e.g. SALES2000, SALES2001 … by year).

EXECUTE IMMEDIATE

create or replace procedure droptable (tablename varchar2) is
   cmd varchar2(100);
begin
   cmd := 'drop table ' || tablename;
   execute immediate cmd;
end;
/
execute droptable('oldsales');

create or replace function deletesalesrows (year number) return number is
   cmd varchar2(100);
begin
   cmd := 'delete from sales' || year;      -- e.g. SALES2000
   execute immediate cmd;
   return sql%rowcount;                     -- rows deleted
end;

Placeholders and the USING Clause

A placeholder is a name preceded by a colon (:prodid) that is replaced at run time by a value given in the USING clause.

create or replace procedure deletesalesrows2 (tablename varchar2, prodid number) is
   cmd varchar2(100);
begin
   cmd := 'delete from ' || tablename || ' where prodid = :prodid';
   execute immediate cmd using prodid;
end;
/
-- placeholders are matched by POSITION, not by name:
cmd := 'insert into sometable values (:x, :x, :y, :x)';
execute immediate cmd using a, a, b, a;     -- four bind arguments

-- INVALID: placeholders cannot stand for schema object names
-- execute immediate 'drop table :table_name' using tablename;

• The number of placeholders (occurrences) must equal the number of values in USING.
• Placeholders cannot be used for the names of schema objects – concatenate names into the string instead.

Executing a Query Dynamically

declare
   cmd    varchar2(100);
   prodid number(5) := 10;
   lprice number(5);
   hprice number(5);
begin
   cmd := 'select min(price), max(price) from sales'
          || to_char(sysdate,'yyyy') || ' where prodid = :1';
   execute immediate cmd into lprice, hprice using prodid;
   dbms_output.put_line('lowest price is : '  || lprice);
   dbms_output.put_line('highest price is : ' || hprice);
end;

With EXECUTE IMMEDIATE the number of columns must be known when writing the program and only one row can be retrieved (one set of INTO variables). A function such as GETSALARY can return NULL by handling NO_DATA_FOUND.

Executing a Multi-row Query Dynamically

A query that may return many rows needs a cursor variable of a weak REF CURSOR type (no return type):

1. OPEN cursor_variable FOR dynamic_string [USING bind_arguments]; – associates the cursor variable with the query and executes it.
2. FETCH cursor_variable INTO variables | record; – in a loop, exiting on %NOTFOUND.
3. CLOSE cursor_variable;

declare
   type salescursortype is ref cursor;     -- weak REF CURSOR type
   salescursor salescursortype;            -- cursor variable
   prodid number(5);
   qty    number(5);
   price  number(5);
begin
   open salescursor for
      'select prodid, qty, price from sales' || to_char(sysdate,'yyyy');
   loop
      fetch salescursor into prodid, qty, price;
      exit when salescursor%notfound;
      -- process the record here
   end loop;
   close salescursor;
end;

Dynamic PL/SQL Blocks

EXECUTE IMMEDIATE can also run an anonymous PL/SQL block – useful when the procedure to call is known only at run time. The static alternative (IF company = 'A' THEN hikesal_a …) must be edited whenever a company is added.

create or replace procedure callhikesal (company varchar2) is
begin
   execute immediate 'begin hikesal_' || company || '; end;';
end;

Chapter 24 Notes (PDF)

Question / Answer (15 questions)

Moderate-level questions based on the Chapter 24 notes (PDF). Click a question to see the answer.

MCQ Test : Check Your Understanding (5 questions)

Select an option and press Check Answer, or answer all questions and press Submit Test to see your score. Each answer comes with an explanation.

Bibliography/References

The chapter notes PDF is the course study material and is credited to its author below. The explanations, questions and MCQs on this page are original study aids written for this course from the topics covered in that PDF. The other works are cited for verification and further reading. Oracle, SQL*Plus and PL/SQL are trademarks of Oracle Corporation; other names belong to their respective owners.

1. Srikanth, P. (n.d.). Oracle For Beginners (chapter notes – the PDF shown on each chapter page). Srikanth Technologies. http://www.srikanthtechnologies.com/
2. Oracle Corporation. (n.d.). Oracle Database PL/SQL Language Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/
3. Oracle Corporation. (n.d.). Oracle Database PL/SQL Packages and Types Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/
4. Feuerstein, S., & Pribyl, B. (2014). Oracle PL/SQL Programming (6th ed.). O’Reilly Media.