In this section we will discuss:

• Static SQL
• Dynamic SQL: Both Native and using DBMS_SQL

So let's get going, what do you say ?

Static SQL relies on hard coding the SQL commands inside the code. Once written, that code cannot be changed via parameters and because of that it is called static. There are use cases, when you should use Static, but mostly Native dynamic SQL is used. We will see why in a bit. But first, let's pass through the Static one.

To define a static SQL, let's check the following example:

CREATE OR REPLACE FUNCTION
get_count(p_act_id accounts.act_id%TYPE)
RETURN NUMBER is
  l_count NUMBER;
BEGIN
  SELECT COUNT(*)
    into l_count
   from orders
   where order_act_id=p_act_id;
  RETURN l_count;
END get_count;

In that case, the compiler will be aware of the SQL during compilation time, so if the user (who creates the FUNCTION) doesn't have rights to select “orders” table, that will fail. We will discuss these differences later in this chapter.

Dynamic SQL use queries are variables either local or provided. Thus different queries can be generated based on the customer's input. For example, a client wishes to order via different columns or different values. That is done with Dynamic SQL as it allows that flexabiltiy.

Dynamic SQL can be implemented in two ways:

• Natively
• Via DBMS_SQL

We will discuss both ways now:

Let's see example of a Native Dynamic SQL

CREATE OR REPLACE FUNCTION get_count(
          p_where VARCHAR2) RETURN NUMBER
IS
  l_count NUMBER;
  l_select VARCHAR2(100) := 'SELECT COUNT(*)';
  l_from VARCHAR2(60) := 'FROM orders ';
  l_query VARCHAR2(200);
BEGIN
  l_query := l_select ||
           l_from ||
           p_where;
  EXECUTE IMMEDIATE l_query INTO l_count;
  RETURN l_count;
END  get_count;

Native SQL is all about “building” of the query using pieces. Thus we can provide the necessary query in the necessary case.

Something to note here in contrast with Native Dynamic SQL. But first, little history. When you invoke a procedure, usually you invoke it with the definer's privileges (unless specified otherwise in the procedure, we will discuss that later in the “Security section”)

But usually, a procedure is executed with the definer's rights. HOWEVER, DBMS_SQL is SYS object and SYS is the super-user/god/diety in Oracle. Obviously, we cannot run that package with the definer's rights.

Thus, when you run DBMS_SQL, you run it with the invokes (executing user) rights. DBMS_SQL can run:

• DML, DDL, ALter session
• Queries
• Procedures & Functions
• Anonymous Blocks

When executing DBMS_SQL, you usually pass through the SQL processing flow, through varies APIs:

• Open Cursor - Provide ID of the memory area which will be used after)
• Parse - Prepares Execution Plans for queries and execute the statement for DDL statement)
• Bind Variables - Associate the variables: input and output to the place holders in the “where” clause)
• Define Column - Defining the columns in the select statement)
• Execute - Executes the statment and will return the total number of rows affected, checks bind variables compatibility)
• Fetch Rows - Fetches Rows (only for queries) into the variables, usually used in the LOOP until found.
• Variable Value - While executing: procedures / functions & Anonymous blocks, gets the output values.
• Column Value - Used the get values of the selected columns. Used after each fetched rows
• Close Cursor - Closing the cursor, releasing the memory.

Not all steps are followed for every type of execution:

• DDL And Session Control
• DML statement
• Anonymous Block
• Select Statment

We will examine each of these executions.

DDL statment or Data Definition language statment is a statment which create, alters or delete definitions of data. It includes the following commands:

• Create
• Alter
• Drop
• Truncate

It is important to note that, DDL DOESN'T DELETE data, instead of modifies the structure and/or the headers. For example, the command: “truncate” updates the header of the table, marking them as free. That doesn't mean the data isn't there, but instead if means that Oracle can RE-USE the data.

For this kind of statments you need the following steps:

• Open Cursor
• * 10g: DBMS_SQL.OPEN_CURSOR; ←Returns Integer
• * 11g: DBMS_SQL.OPEN_CURSOR(security _level IN INTEGER): 0 - No Security Check, 1 - Same user, 2 - Same as most recent parse user.
• Parse: DBMS_SQL.PARSE(cursor_id,statment,language_flag): V6/7 (for Oracle 6/7), Native (for current), FOREIGN_SYNTAX (external)
• Execute: Optional, as Parse executes DDL statments, but that might change in the future.
• Close Cursor: Good to use in order to control memory allocate properly.

Let's check an example:

CREATE OR REPLACE PROCEDURE drop_table (p_table_name VARCHAR2) is
  l_sql VARCHAR2(100);
  l_cursor_id INTEGER;
BEGIN
l_sql := 'DROP TABLE '||p_table_name;
l_cursor_id := DBMS_SQL.OPEN_CURSOR(2);
DBMS_SQL.PARSE(l_cursor_id, l_sql,DBMS_SQL.NATIVE)
l_return := DBMS_SQL.EXECUTE(l_cursor_id); 
DBMS_SQL.CLOSE_CURSOR(l_cursor_id)

In Oracle DB, DDLs are auto-commit.

DML is Data Modification Language is a language used to modify the data, it includes the following commands:

• Update
• Delete
• Insert

In order to execute DML using DBMS_SQL we need to pass through the following steps from the SQL Processing Flow APIs:

• Open Cursor
• Parse
• Bind Varialbes (Optional): Generally used to reduce the number of hard parses as Oracle Optimizer will be able to re-use the execution plan if the query is ran again.
• Execute
• Variable Value
• Close Cursor

So let's check an example of this:

CREATE OR REPLACE PROCEDURE insert_record(p_talbe_name VARCHAR2,
                     p_col1_name VARCHAR2,
                     p_col1_value NUMBER,
                     p_col2_name VARCHAR2,
                     p_col2_value NUMBER) IS
      l_sql VARCHAR2(100);
      l_cursor_id INTEGER;
      l_return INTEGER;
BEGIN
      l_sql := 'INSERT INTO '||p_table_name ||'('||
                p_col1_name||','||
                p_col2_name||
                ') '||
                'VALUES (:col1_value,:col2_value)';
       l_crusor_id := DBMS_SQL.OPEN_CURSOR;
       DBMS_SQL.PARSE(l_cursor_id,l_sql,DBMS_SQL.NATIVE);
       DBMS_SQL.BIND.VARIABLE(l_cursor_id,':col1_value',p_col1_value);
       DBMS_SQL.BIND.VARIABLE(l_cursor_id,':col2_value',p_col1_value);
       l_return := DBMS_SQL.EXECUTE(l_cursor_id);
       DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
       COMMIT;
END insert_record;

One advantage of the DBMS_SQL over the Native Dynamic SQL is the ability to work with:

• Unknown Number of Bind Variables
• Unknown Number of Column

So let's see how we can work with unknown number of columns. Oracle provided couple APIs for that:

• DBMS_SQL.DESC_COLUMNS(x)
• DBMS_SQL.DESC_TAB
• DBMS_SQL.DESC_REC

X - being: ,2 or 3 Let's go through each of them.

It has the following syntax:

<Code:bash|DBMS_SQL.DESCRIBE_COLUMNS(x) ( c IN INTEGER, ← Takes the cursor's ID col_cnt OUT INTEGER, ← Returns the count of the columns in that Cursor desc_t OUT DESC_TAB(X) ← Return associative array (collection) of type DESC_TAB );

===DESC_TAB===
That array which we saw above and it has the following structure:

<Code:bash|DBMS_SQL.DESC_TAB>
TYPE desc_tab IS TABLE OF desc_rec(x) INDEX BY BINARY_INTEGER;

More info about this record, below:

The last collection has relation with the previous collection as follows:

desc_tab(1)                       -> desc_rec
desc_tab(2)                       -> desc_rec
desc_tab(3)                       -> desc_rec
desc_tab(4)                       -> desc_rec
desc_tab(n)                       -> desc_rec

Each desk_tab points to a desc_rec collection with the following content:

TYPE desc_rec IS RECORD (
      col_type            BINARY_INTEGER := 0,
      col_max_len         BINARY_INTEGER := 0,
      col_name            VARCHAR2(32)   := '',
      col_name_len        BINARY_INTEGER := 0,
      col_schema_name     VARCHAR2(32)   := '',
      col_schema_name_len BINARY_INTEGER := 0,
      col_precision       BINARY_INTEGER := 0,
      col_scale           BINARY_INTEGER := 0,
      col_charsetid       BINARY_INTEGER := 0,
      col_charsetform     BINARY_INTEGER := 0,
      col_null_ok         BOOLEAN        := TRUE);
TYPE desc_tab IS TABLE OF desc_rec INDEX BY BINARY_INTEGER;

With the new versions, there is slightly different provided values, you can check all the values in that collections: here

Phew, gosh, that was a long walk, let's see example finally:

CREATE OR REPLACE PROCEDURE desc_columns (p_query VARCHAR2) AUTHID DEFINER IS
 l_cursor_id INTEGER;
 l_no_of_columns INTEGER;
 l_desc_tab2 DBMS_SQL.DESC_TAB2;
 l_desc_rec2 DBMS_SQL.DESC_REC2;
BEGIN
 l_cursor_od := DBMS_SQL.OPEN_CURSOR;
 dbms_sql.parse(l_cursor_id,p_query,DBMS_SQL.NATIVE);
 DBMS_SQL.DESCTIBE_COLUMNS2(l_cursor_id,l_no_of_columns,l_desc_tab2);
  FOR i in 1 .. l_no_of_columns LOOP
    l_desc_rec2 := l_desc_tab2(i)
    DBMS_OUTPUT.PUT_LINE('Column Name '||l_desc_rec2.col_name);
    DBMS_OUTPUT.PUT_LINE('Column Thype '||l_desc_rec2.col_type);
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
END desc_columns;

As we discussed, DBMS_SQL has couple security policies to prevent SQL injection. Most of them are controller when we open a cursor as the one explained above for 11g+

DBMS_SQL.OPEN_CURSOR(security _level IN INTEGER)

The security level is integer and sets one of the following configurations:

• 0 - No security Check
• 1 - Userid / Role Parsing be the same as Binding / Executing
• 2 - Most Secure

The most secure level is of course recommend and include the following checks:

• Current Calling User is the same as the user with the most recent parse
• Enabled Roles on the Current call is the same as enabled roles on the most recent parse.
• Container on current call is the same as the container on the most recent parse.

If any of these aren't true, you will receive:

ORA-29470: The effective userid or roles on this call to DBMS_SQL are not the same as those at the time the cursor was parsed.

Other security feature of the DBMS_SQL is the access denied when you try to use invalid Cursor ID. In a nutshell if a session tries to execute an open cursor generated by a different session, you will receive:

DBMS_SQL access denied.

Static and Dynamic SQL behave differently, for example:

Static SQL:

• SQL Known at Compile Time
• Compiles Verifies Object References
• Compiler Can verify Privileges
• Less Flexible
• Faster

Dynamic SQL:

• SQL Known at Runtime
• Compiler Cannot Verify Object References
• Compiler Cannot verify privileges on compile time
• More flexible with ever-channg table/schema definitions.
• Slower
• Used for: Dynamic Queries and Sorts (Queries which can change depending on the user's preferences)
• DDL can be used, unlike Static SQL

In general, you should use DBMS_SQL dynamic SQL, only when you don't know the number of columns or bind variables which will be used in a select, for every other occasion, use the Native Dynamic SQL.