NATIVE DYNAMIC SQL

What is Dynamic SQL?

Generally programmer knows the SQL statements that are to be executed at the time of writing program. However, in some cases the programmer may not precisely know the command that is to be executed at the time of writing program. Instead the command may vary from execution to execution and it is to be constructed dynamically (at runtime).  An SQL statement that is constructed  “on the fly” is dynamic SQL statement.

Until Oracle8 the only way to execute dynamic SQL was by using DBMS_SQL package. Oracle8i introduced native dynamic SQL, where you can directly place dynamic SQL commands into PL/SQL blocks. Native dynamic SQL is faster then using DBMS_SQL package.

Dynamic SQL should be considered only when static SQL is not possible. Dynamic SQL will impact performance.

Why do we need dynamic SQL?

We should use dynamic SQL where static SQL doesn’t support the operation that we want to perform.

The following are the situations where you may want to use Dynamic SQL:

q  You want to execute an SQL DDL or DCL command that cannot be executed statically. That means the command is not allowed in the PL/SQL block.  For example, you cannot execute DROP TABLE command from a PL/SQL block.

q  You want to access a table or a view but do not know the name of the object until you run the program.

           

An Example of Dynamic SQL

Let’s look at an example. Assume we have to drop a table. But the name of the table to be dropped is not known. The name of the table is formed using the word SALES and four digits year. For example, it the current year is 2000 then table name would be SALES2000.

That means depending upon the current year the name of the table changes. So the DROP TABLE command should drop the table of the current year and the current year is to be taken from the system date.

The solution to this problem is to construct DROP TABLE command as follows:

     cmd :=   ‘DROP TABLE  sales’ || to_char(sysdate,’yyyy’);

The above statement will put the required command into a char variable and then the command in the char variable is to be executed with EXECUTE IMMEDIATE command.

Execute Immediate Statement

This statement is used to execute a command that is a char variable. In the above example we put the required command in a variable called cmd. Now, we can execute the command as follows:

  EXECUTE IMMEDIATE cmd;

Now let us examine the complete syntax of Execute Immediate statement.

EXECUTE IMMEDIATE dynamic_string

[INTO {variable[, variable]... | record}]

[USING [IN | OUT | IN OUT] bind_argument

    [, [IN | OUT | IN OUT] bind_argument]...];

Dynamic_string	Is the string that contains the command to be executed.
Variable | record	Is a variable into which values retrieved by SELECT command are to be copied. If a record is given then complete row is copied into the record. Record must be a variable of user-defined data type or %ROWTYPE% record.
Bind_argument	Is the value that is to be passed to SQL statement that is being executed. This value will replace a placeholder in the command (more on this later).

The following stored procedure takes the name of the table and drops the table.

create or replace procedure droptable ( tablename varchar2) is

  cmd  varchar2(100);

begin

  cmd := 'drop table ' || tablename;

  execute immediate cmd;

end;

You can now call the above procedure to drop a table. You have to supply the name of the table to be dropped. Remember that in PL/SQL DDL commands are not allowed in static SQL, so dynamic SQL is required.

execute droptable('oldsales');

The following is another stored function that takes the year and deletes all rows from the tables whose name is formed as SALESyear. Where year is the value passed to the procedure.

create or replace function deletesalesrows (year number)

return number is

cmd  varchar2(100);

begin

    cmd := 'delete from sales'|| year;

    execute immediate cmd;

    return  sql%rowcount;

end;

The function takes year number and deletes all rows from sales table of that year. It also returns the number of rows deleted from the table.

You can invoke the function to delete rows of 2000 sales table and display the number of rows deleted as follows:

begin

   dbms_output.put_line( deletesalesrows(2000));

end;

Using Placeholders

Placeholders can be used to replace a missing value at runtime. A placeholder is a name preceded by : (colon).  The placeholder is to be replaced with a value through USING clause of EXECUTE IMMEDIATE statement.

The following example deletes all rows of the given table where PRODID is equal to the product id passed.

create or replace procedure DeleteSalesRows

      (tablename varchar2, prodid number) is

cmd varchar2(100);

begin

    cmd := 'delete rows from ' || tablename || ' where prodid = :prodid';

    execute immediate cmd using  prodid;

end;

/

At the time of executing a command that has placeholders, we must pass values that replace placeholders through USING clause of EXECUTE IMMEDIATE command.  In the above example, the value of variable PRODID will replace the placeholder :PRODID and then the command is executed.

Note: The number of placeholders and the number of values passed by USING clause must be equal.

Placeholders and parameters are matched by the position and not by name. That means if the same placeholder is used for twice or more then for each occurrence the value is to be passed by USING clause as show below.

cmd := 'INSERT INTO sometable VALUES (:x, :x, :y, :x)';

EXECUTE IMMEDIATE sql_stmt USING a, a, b, a;

You have to pass four values to the above command although there are only two placeholders – X and Y. This is because of the fact that bind arguments are associated with placeholders by position and not by name.  That means the first bind argument is associated with first placeholder and second bind argument with second placeholder and so on.

Note: Placeholders cannot be used for names of schema objects.

The following dynamic SQL statement is INVALID.

cmd := ‘drop table :table_name’;

execute immediate cmd using tablename;

Execute a Query Dynamically

So far whatever we have seen is related to non-query SQL statements. Now, let us see how to execute an SQL query dynamically. The difference between executing a non-query and a query is; a query returns one or more rows. In the beginning we will confine our examples to executing single-row query statements.

When a query in executed dynamically using EXECUTE IMMEDIATE command then we have to supply INTO clause to catch the values retrieved by the query.  The following example shows how to use SELECT command with dynamic SQL to retrieve highest and lowest prices at which the given product is sold in the current year.

declare

      cmd  varchar2(100);

      prodid  number(5)  :=  10;

      lprice  number(5);

      hprice  number(5);

begin

      cmd := ‘select  min(price), max(price) from sales’;

      cmd := cmd || to_char(sysdate,’yyyy’) || ‘  where prodid = :1’;

      -- execute the command by sending product id

      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;

While a query is executed using EXECUTE IMMEDIATE command  the following rules will apply:

q  The number of columns to be retrieved should be known at the time of writing program.

q  Only one record can be retrieved as only one set of variable can be passed using INTO.

The following function is used to return salary of the employee from the given table.

create or replace function GetSalary(tablename varchar2, empno number) return number is

  cmd varchar2(100);

  v_sal  number(5);

begin

    cmd := 'select sal from ' || tablename || ' where empno = :empno';

    execute immediate cmd  into  v_sal  using   empno;

    return v_sal;

exception

   when no_data_found then

    return null;

end;

The above function returns NULL if the given employee number is not found in the given table.

Executing multi-row query dynamically

Using simple EXECUTE IMMEDIATE statement we can execute a query that retrieves only one row. However, when we need to execute a query that has the potential to retrieve multiple rows, we need to follow a different process.

To deal with multi-row query dynamically, we have to use a Cursor. All the rows retrieved by the query are copied into cursor. And then using FETCH statement each row will be fetched and processed.

The following are the statements used to execute a multi-row query dynamically.

Open-For statement to open the cursor

The first step is to open a cursor with the query statement.   This statement associates a cursor variable with the given query and executes the query. This statement has USING option to pass values to placeholders.

The complete syntax of OPEN-FOR statement is as follows:

OPEN {cursor_variable}

  FOR dynamic_string

   [USING bind_argument[, bind_argument]...];

Cursor_variable	Is a weakly typed cursor variable, i.e. a variable that doesn’t have any return type.
Dynamic_string	The SELECT command to be executed.
Bind_argument	Is the value to be passed to placeholders of the command.

The following is an example where all rows of SALES table of the current year are retrieved.

DECLARE

   TYPE SalesCursorType IS REF CURSOR;  -- define weak REF CURSOR type

   salescursor  SalesCursorType;  -- declare cursor variable

BEGIN

   OPEN salescursor FOR  -- open cursor variable

      'SELECT  proid, qty, price from sales’ || to_char(sysdate,’yyyy’);

   .

   .

   .

END;

Fetching row from cursor

Fetches one row from the cursor and copies the values of the columns into corresponding variables.

FETCH {cursor_variable } INTO {define_variable[, define_variable]   ... | record};

Cursor_variable            is the cursor variable with which the result of the query is associated.

Define_variable            is the variable into which the value the corresponding columns should be copied.

Record                         is the variable of a user-defined record type or %ROWTYPE%.

To fetch rows from SALESCURSOR that we have defined in the previous step, we have to use Fetch statement as follows:

Loop

    Fetch salescursor into prodid, qty, price;

    Exit when sales_cursor%notfound;   --exit when no more records exist

    -- process the record here

End loop

Closing the cursor

The last step in the process is closing the cursor after it is processed.

CLOSE {cursor_variable};

A sample program

The following program is consolidating all that we have seen regarding how to execute a multi-row query dynamically.

declare

   type salescursortype is ref cursor;  -- define weak ref cursor type

   salescursor  salescursortype;  -- declare cursor variable

   prodid  number(5);

   qty     number(5);

   price   number(5);

 begin

   open salescursor for  -- open cursor variable

      'select  proid, qty, price from sales’ || to_char(sysdate,’yyyy’);

 loop

    fetch salescursor into prodid, qty, price;

    exit when sales_cursor%notfound;  --exit when no more records exist

    -- process the record here

 end loop

 close salescursor;   -- close the cursor after it is processed.

end;   -- end of the block

Dynamic PL/SQL Blocks

You can execute an anonymous PL/SQL block dynamically using EXECUTE IMMEDIATE statement. It is useful in cases where the procedure to be invoked is known only at runtime.

The following procedure invokes one of the two procedure based on the name of the company. For this we assume we already have two procedures with names HIKESAL_A and HIKESAL_B where A and B are company names.

create or replace procedure callhikesal(company varchar2)is

begin

  execute immediate

     'begin

        hikesal_' || company;

     'end;'

end;

The above procedure calls the procedure with the name HIKESAL_A or HIKESAL_B depending on the name of the company – A or B – passed to the procedure.

The same can be done without dynamic SQL but it becomes length and needs to be updated if more there is any change in number of companies.

The following is non-dynamic SQL version of the same procedure.

create or replace procedure callhikesal(company varchar2)is

begin

   if  company = ‘A’ then

        hikesal_a;

   else

        hikesal_b;

   end if;

end;

But as you can notice, if we add one more company to the list then the procedure is to be modified to accommodate another company.

Summary

Dynamic SQL is the process of constructing SQL commands dynamically and executing them.  This was done using DBMS_SQL package prior to Oracle8i but in Oracle8i Native Dynamic SQL was introduced making dynamic SQL less cumbersome and faster.

EXECUTE IMMEDIATE statement is used to execute an SQL command that is constructed at runtime. You can execute DML, single-row SELECT and even multi-row SELECT.

While executing multi-row query, we have to use a cursor to retrieve the data into cursor and then fetch one row at a time from user.

It is also possible to execute a PL/SQL block dynamically.