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
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
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
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.
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
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 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
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;
Describe Unknown No. of Columns
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.
DESC_COLUMNS
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:
DESC_REC
The last collection has relation with the previous collection as follows:
DBMS_SQL.DESC_REC Relation
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:
DBMS_SQL.DESC_REC
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:
Example
Unknown number of Columns DBMS_SQL
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;
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
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:
DBMS_SQL Error
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 Error
DBMS_SQL access denied.
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
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.