Dynamic SQL - for newbies



I have been dealing with dynamic SQL for some time. And that is something that is still a mystery for many newcomers (and experienced Oracle guys as well).
Here I am going to tell how you can use dynamic SQL. How, but more important is "why", because when creating a comment you should never describe "how" - you always should describe "why".

Why you might need dynamic SQL.
There is DDL. There is DML. There are other things.
You cannot use SELECT on a table which name is given to you during runtime. That restriction applies not only to SELECT.
For that reason you might use dynamic SQL which is not limited by this constraint.

Why you might favor static SQL over dynamic SQL
Static SQL is faster. Static SQL is in some ways better (sorry - no details here).

How to use dynamic sql in PL/SQL
1. Use some external application to generate the anonymus block and execute it.
3. Use DBMS_SQL.
4. Other options are there... but I won't describe them... Oracle always has an exception from any rule :)

You might create any query and store it int a varchar2 variable. PL/SQL allows varchar2 up to 32k characters so your query can be that long.
In Oracle 11 you might use clob as well - so the query can be longer.
You might store output of your query into a variable usingEXECUTE IMMEDIATE query_string_which_might_be_a_variable INTO output_variable.
You might provide input parameters like EXECUTE IMMEDIATE query INTO variable_out USING var1_in,var2_in.
Query is bound with variables using ':variable', like EXECUTE IMMEDIATE 'SELECT 1 FROM t WHERE column1=:1 AND column2=:2 and column3>:1.
EXECUTE IMMEDIATE should use the order of variables and bind them to the query. It should detect which variables are repeated - however it never worked that way for me for some reason...

Most important problems that I have encountered with EXECUTE IMMEDIATE:
- Slower than static SQL
- Variables are assigned by position instead of their name
- Cannot execute queries longer then 32k in Oracle 10.2G

What about DBMS_SQL?
That is something that I like a lot. Its use is more complicated. It is not that "user friendly" as EXECUTE IMMEDIATE. But I feel that it is clear and straightforward where EXECUTE IMMEDIATE is difficult to manage and control.
To use DBMS_SQL queries you should request a cursor handle, parse the query, bind variables, execute the query, ensure that your cursor is closed and take the risk of cursors leak.
Without going into the details of usage (you might find that on Oracle pages) here are the pros (+) and cons (-) of DBMS_SQL that I can see:
- slower than static SQL
+ coding it straightforward and simple for me
- coding differs from PL/SQL style and does not fit into it well
+ variables are assigned by name
- you need to open a cursor handle
- you need to ensure that the cursor is closed if an exception occured... and if no exception occured
- every exception block should handle the cursor closing (might become a problem if you don't have common finalize subprocedure)
- severe risk of "cursor leak" (when you forgot about closing the cursor) which might lead to application (or database) fail
- needs more lines of code than EXECUTE IMMEDIATE
+ can execute very very long queries in Oracle 10.2G (and older) when using varchar2a and varchar2s
- every exception handling block affects the exception object - you loose some information when handling an exception thus some logging mechanism is required

For me the battle has a simple result set:
1. Use static SQL when appropriate (when you know the names of the table and columns). This code is compiled in advance thus is way faster.
2. Use EXECUTE IMMEDIATE whenever executing simple dynamic queries.
3. Use DBMS_SQL whenever executing complex queries. Be aware of the risk and create your own logging mechanism (or use some existing). Spend more time on coding but save it on code maintenance. Create strong coding standard and always use it (especially initialization/finalization blocks and exception handling including logging).

Please, feel free to comment it and add your own piece of knowledge here :)


I created the following function returning REF CURSOR. If I call this function from a SQL statement, it is showing an error : inconsistent datatype. expected number got cursor.

Please tell me the solution.

create or replace
 function jarek_refcursor return sys_refcursor
     c1 sys_refcursor;
      open c1 for select * from tab;
      return c1;
 select jarek_refcursor from dual;

I think that this is wrong place to ask such questions. Please use your-favorite-web-search-engine and/or forum in the future.
But this time I will give you this answer: What are going to return? A table? A row? Or a-java-class-that-has-opened-cursor-and-you-can-iterate-over-the-elements? Anyway - I feel that you have wrong approach here. If you really need to work on this - use DBMS_SQL or similar things. Create a cursor handle and pass it. And note that it seems strange for me... I would suggest that you re-check your problem and solve this in a different way.