Most of the code we write and then execute is 100% complete at the time of compilation. This is generally referred to as “static” code. A select-into query is, for example, static SQL. In relatively rare cases, however, we don’t have all the information we need at compile time. We need to get a bit more when the code is actually running. This is referred to as “dynamic” code. Almost all the dynamic code we write as database developers is dynamic SQL (you can also dynamically, at runtime, construct and execute PL/SQL blocks – I talked about that in an earlier Feuertip).
So I thought I’d run through a variety of really-good-to-knows about dynamic SQL. Here goes:
Use execute immediate and not dbms_sql. Unless you can’t.
There are basically two ways to execute SQL or PL/SQL statements dynamically in PL/SQL: with the execute immediate statement (aka, native dynamic SQL) or with dbms_sql (aka, um, not native, since it is a built-in package sitting “on top” of native PL/SQL).
A long, long time ago – when I can still remember! – there was only dbms_sql. Those were not the good old days. You can get done all you need with dbms_sql, but it is cumbersome, verbose, and, well, just kind of ugly. Working with execute immediate is so much easier.
The only times you really need to use dbms_sql these days is for method 4 dynamic SQL (the most dynamic of them all!) or when you want to take advantage of a procedure like dbms_sql.describe_columns to get information back about the structure of your dynamic query.
Bottom line: if you are using dbms_sql, be prepared to justify why you are not going the native route.
SQLCODE is reset to 0 when you call execute immediate.
This is not likely to be an issue for you, but I ran into it recently, building some cool, flexible code for error management.
Run this code and you will see what I mean:
create or replace procedure show_sqlcode (p_info in varchar2) is
c_sqlcode constant integer := sqlcode;
begin
dbms_output.put_line(p_info || ' sqlcode = ' || c_sqlcode);
end;
/
begin
raise no_data_found;
exception
when others then
dbms_output.put_line('in exc section before static sqlcode = ' || sqlcode);
show_sqlcode('static');
end;
/
begin
raise no_data_found;
exception
when others then
dbms_output.put_line('in exc section before dynamic sqlcode = ' || sqlcode);
execute immediate 'begin show_sqlcode (''dynamic''); end;';
end;
/
Don’t concatenate, bind.
When you are writing a program with dynamic SQL in it (that is, you construct your statement at runtime and execute it with EXECUTE IMMEDIATE or DBMS_SQL – most likely and preferably the former), you should make sure to bind all variable values into that statement, and not concatenate, for some excellent reasons:
- Reduce the chance of a SQL injection hacking of your code (can only happen with concatenation).
- Improve performance by reusing already-parsed cursors.
- Make your code easier to read and maintain.
LiveSQL shows a rewrite from concatenation to binding.
Binding to placeholders works differently for SQL and PL/SQL.
Differently for dynamic SQL and dynamic PL/SQL, to be more accurate.
And the difference only really makes itself apparent when you reference the same placeholder (same name) more than once in your statement.
Bottom line: the bind values are associated with placeholders in dynamic SQL by position. In dynamic PL/SQL, values are associated by name. So with dynamic SQL, you must provide a value for each placeholder, regardless of repetition. In a dynamic PL/SQL block, you provide a value for each unique placeholder.
This LiveSQL script demonstrates the difference.
Use SQL statement templates.
You can depend on this: your dynamic SQL exercise will be more complicated than you initially expected, things will go wrong, and you will get easily confused. So do whatever you can to deal with that upfront. One great thing you can do is always execute immediate a “template” (declared as a constant in your block) that contains your SQL text.
By doing this, you consciously separate the construction of your statement from the binding and execution.
It is much easier to read and evaluate the code for correctness.
And when something goes wrong, you can easily log the statement that caused the problem. If you construct that statement right inside the execute immediate call, you have to do it again in your exception handler!
Make stored program units with dynamic SQL “invoker rights.”
Make sure you add authid current_user to the header of your program unit if it contains dynamic SQL.
That makes it an “invoker rights” program unit, which means that when you run a subprogram in the unit (likely, hopefully, a package), all references to database objects are resolved (at runtime!) according to the privileges of the invoker, not the definer.
So think about it:
You write a definer rights program (authid definer, the default) that executes dynamically-constructed SQL.
You then grant execute privilege on that package to another schema.
A user connects using that schema and runs your program. That user can now make changes to any table that your schema has access to, even if the connecting schema does not.
That’s a big risk to take with dynamically-constructed SQL!
Resources
I am certain you will not be surprised to hear I’ve written about dynamic SQL before (as have others). So here are some links you might find useful:
In which I offer all sorts of warnings about dynamic SQL and point out scenarios in which you do not need to rely on it to solve your problems.
Using Dynamic SQL for Multi-row Queries
In this article, I will explore how to use PL/SQL features to execute dynamic SELECT statements that return multiple rows.
“There’s a right time to use dynamic SQL, but there’s never a right time for SQL injection.”
No list of resources on any Oracle Database topic is c0mplete without at least one referral to a Tim Hall article.
The definitive (and only) official documentation from the Company That Is Oracle.
