Overview

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

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.

Statment

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

Static SQL Example

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

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:

Native

Let's see example of a Native Dynamic SQL

Native Dynamic SQL

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

DBMS_SQL

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 & Session Control

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:

Example Dynamic DDL

In Oracle DB, DDLs are auto-commit.

DML Statment

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:

Example Dynamic DML

Security with DBMS_SQL

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+

Security for DBMS_SQL

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:

DBMS_SQL Error

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 Error

Differences

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