A Quick Guide to Writing Dynamic SQL in PL/SQL
Here are some options and examples to get you started writing Dynamic SQL when using PL/SQL.
Join the DZone community and get the full member experience.Join For Free
Dynamic SQL refers to SQL statements that are constructed and executed at runtime. Dynamic is the opposite of static. Static SQL (also known as Embedded SQL) refers to SQL statements that are fully specified, or fixed, at the time the code containing that statement is compiled. Dynamic PL/SQL refers to entire PL/SQL blocks of code that are constructed dynamically, then compiled and executed.
There are, broadly, two ways to execute dynamic SQL and PL/SQL:
Native dynamic SQL: use native PL/SQL statements (EXECUTE IMMEDIATE or OPEN FOR) to handle your dynamic SQL needs.
DBMS_SQL: use a built-in package with a big, fat API to parse, execute, etc. your dynamic SQL statements.
Oh, great. Two ways to do it, with the obvious next question: which one should you use?
First, the short answer, then the long answer.
Use native dynamic SQL, which means — almost always — use EXECUTE IMMEDIATE. It is very easy to use and is quite efficient.
There is really just one general use case for DBMS_SQL these days (Oracle Database 11g and higher): method 4 dynamic SQL.
Which brings me to the long answer.
Three are four methods of dynamic SQL:
Method 1: The simplest kind of dynamic statement. Either a DDL statements (e.g., CREATE TABLE) or a non-query DML (update, insert, delete, merge) that has no bind variables.
Examples using EXECUTE IMMEDIATE:
BEGIN EXECUTE IMMEDIATE 'DROP TABLE my_table'; END;
(You cannot execute DDL statements natively in PL/SQL, as you could a SELECT. So you must put the statement inside quotes and execute it dynamically.)
PROCEDURE null_out_column (column_in IN VARCHAR2) IS BEGIN /* Basic protection against SQL injection */ IF column_in NOT IN (<white list of columns>) THEN RAISE_APPLICATION_ERROR (-20000, 'Unable to retrieve data'); END IF; EXECUTE IMMEDIATE 'UPDATE my_table SET ' || column_in || ' = NULL'; END;
Method 2: Non-query DML statements with one or more bind variables, the number of which is known at compile time.
Example using EXECUTE IMMEDIATE:
PROCEDURE updnumval ( col_in IN VARCHAR2 , start_in IN DATE , end_in IN DATE , val_in IN NUMBER ) IS l_update varchar2(1000) := 'UPDATE employees SET ' || col_in || ' = :val WHERE hire_date BETWEEN :lodate AND :hidate AND :val IS NOT NULL'; BEGIN /* Basic protection against SQL injection */ IF numcol NOT IN ('SALARY', 'COMMISSION_PCT') THEN RAISE_APPLICATION_ERROR (-20000, 'Unable to retrieve data'); END IF; EXECUTE IMMEDIATE l_update USING val_in, start_in, end_in, val_in; END;
Notice that I must provide val_in twice. Assignments of variables to placeholders is positional in dynamic SQL.
Because I know the number of placeholders in the string (four), I can use USING to bind the parameters in.
Method 3: a SELECT statement whose select list (number of elements returned by the query) and bind variables are fixed at compile-time.
Example using EXECUTE IMMEDIATE:
FUNCTION best_ever_ranking_in ( ranking_mview_in IN VARCHAR2, user_id_in IN INTEGER) RETURN INTEGER IS l_ranking INTEGER; BEGIN EXECUTE IMMEDIATE 'SELECT MAX (ranking) FROM ' || sys.DBMS_ASSERT.sql_object_name (ranking_mview_in) || ' WHERE user_id = :empid' INTO l_ranking USING user_id_in; RETURN l_ranking; END;
This is an example from the PL/SQL Challenge. We have a set of materialized views that hold player rankings (refreshed weekly). To get the lifetime best ranking for an individual, I pass in the name of the materialized view and the user ID.
Because I know the number of elements in the SELECT list (1), I can use the INTO clause of EXECUTE IMMEDIATE to get it back.
Because I know the number of placeholders in the string (1), I can use USING to bind the parameter in.
Method 4: "I know what I don't know." At the time I write my code, I don't know how many elements will be in my SELECT list and/or I don't know the number of variables that may need to be bound to placeholders. Wow. I don't know very much, do I?
Method 4 is hard to implement because you have to write very flexible code. And it is extremely difficult (but not impossible) to do this with EXECUTE IMMEDIATE because the INTO and USING clauses are static.
As a result, DBMS_SQL is usually the best implementation mechanism for method 4 dynamic SQL. It provides a very granular API that gives you complete control over every step in the dynamic SQL flow, including: create cursor, parse statement, bind variables, execute statement, get column values, and close cursor.
And that's just great — if you need it. A loooong time ago, before EXECUTE IMMEDIATE was available, DBMS_SQL was mostly just a big pain because of all the code you had to write even for simple scenarios.
Here's an example of using DBMS_SQL to implement a method 3 program that displays the values of any two columns (well, one's of type number, another type string) in employees:
PROCEDURE showemps ( numcol IN VARCHAR2, strcol IN VARCHAR2) IS cur INTEGER := DBMS_SQL.OPEN_CURSOR; rec employees%ROWTYPE; fdbk INTEGER; loc INTEGER; BEGIN /* Basic protection against SQL injection */ IF numcol NOT IN ('SALARY', 'COMMISSION_PCT') THEN RAISE_APPLICATION_ERROR (-20000, 'Unable to retrieve data'); END IF; DBMS_SQL.PARSE (cur, 'SELECT ' || numcol || ', ' || strcol || ' FROM employees', DBMS_SQL.NATIVE); DBMS_SQL.DEFINE_COLUMN (cur, 1, 1); DBMS_SQL.DEFINE_COLUMN (cur, 2, 'a', 30); fdbk := DBMS_SQL.EXECUTE (cur); LOOP /* Fetch next row. Exit when done. */ EXIT WHEN DBMS_SQL.FETCH_ROWS (cur) = 0; DBMS_SQL.COLUMN_VALUE (cur, 1, rec.employee_id); DBMS_SQL.COLUMN_VALUE (cur, 2, rec.last_name); DBMS_OUTPUT.PUT_LINE ( TO_CHAR (rec.employee_id) || '=' || rec.last_name); END LOOP; DBMS_SQL.CLOSE_CURSOR (cur); END;
So pretty clearly you do not want to use DBMS_SQL unless you need to, and method 4 is pretty much exactly when you need to.
Showing you how to use DBMS_SQL for method 4 in a blog post leads to a really, really long blog post. So instead, I suggest you check out the LiveSQL script listed below. It should give you a pretty good feel for at least some of the steps involved. But to summarize:
If you do not know the number of items in the SELECT list, you will need to execute calls to DEFINE_COLUMN within a loop, as in:
WHILE (l_index IS NOT NULL) LOOP IF is_string (columns_in, l_index) THEN DBMS_SQL.define_column (cursor_io, l_index, 'a', columns_in (l_index).data_length); ELSIF is_number (columns_in, l_index) THEN DBMS_SQL.define_column (cursor_io, l_index, 1); ELSIF is_date (columns_in, l_index) THEN DBMS_SQL.define_column (cursor_io, l_index, SYSDATE); END IF; l_index := columns_in.NEXT (l_index); END LOOP;
And if you don't know how many bind variables there are, then you will need to execute calls to BIND_VARIABLE in a loop, something like this:
PROCEDURE method4_bind_variables ( sql_in IN VARCHAR2 , placeholders_in IN DBMS_SQL.varchar2a , values_in IN DBMS_SQL.varchar2a) IS l_cursor INTEGER := DBMS_SQL.open_cursor; l_feedback PLS_INTEGER; BEGIN /* WARNING: a program like this should NEVER be available directly to an end user - that is, a user should not be able to enter a SQL statement and leave it to you to execute it for you! Major SQL injection opening. */ DBMS_SQL.parse (l_cursor, sql_in, DBMS_SQL.native); FOR indx IN 1 .. values_in.COUNT LOOP DBMS_SQL.bind_variable (l_cursor , placeholders_in (indx) , values_in (indx)); END LOOP; l_feedback := DBMS_SQL.execute (l_cursor); DBMS_OUTPUT.put_line (SQL%ROWCOUNT); DBMS_SQL.close_cursor (l_cursor); END;
Mostly, though, you will simply not need to bother with DBMS_SQL, because true method 4 dynamic SQL requirements are rare.
Here are some resources to help you explore dynamic SQL further:
Published at DZone with permission of Steven Feuerstein, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.