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.

For this kind of statment you need the following steps:

• Open Cursor
• * 10g

l_cursor_id := DBMS_SQL.OPEN_CURSOR; ←Returns Integer

• * 11g

DBMS_SQL.OPEN_CURSOR(security _level IN INTEGER) RETURN INTEGER: – 0 - No Security Check – 1 - User / Role Parsing be the same as the binding / executing – 2 - Most Security - The user who defines, binds, executes is the same as the most recent parse. – We will talk more, when we speak about security

• Parse

DBMS_SQL.PARSE(cursor_id,statment,language_flag) Cursor_id = The ID from the previous step statment = The statment as variable language_flag = V6 (for Oracle 6), V7 (For Oracle 7), Native (for current), FOREIGN_SYNTAX (external)

• Execute

• Close Cursor

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(l_cursor_id = DBMS_SQL.OPEN_CURSOR(2)
DBMS_SQL.PARSE(l_cursor_id, l_sql,DBMS_SQL.NATIVE)

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