plsql_static_dynamic_sql

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision		 Previous revision
plsql_static_dynamic_sql [2020/05/31 15:26] – andonovjplsql_static_dynamic_sql [2020/06/04 15:41] (current) – [Differences] andonovj
Line 1: Line 1:
-=====Overview=====+======Overview======
 In this section we will discuss: In this section we will discuss:
   * Static SQL   * Static SQL
Line 6: Line 6:
 So let's get going, what do you say ? So let's get going, what do you say ?
  
-=====Static SQL=====+======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. 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: To define a static SQL, let's check the following example:
  
Line 35: Line 37:
  
 We will discuss both ways now: We will discuss both ways now:
-====Native====+=====Native=====
 Let's see example of a Native Dynamic SQL Let's see example of a Native Dynamic SQL
  
Line 56: Line 58:
  
 Native SQL is all about "building" of the query using pieces. Thus we can provide the necessary query in the necessary case. Native SQL is all about "building" of the query using pieces. Thus we can provide the necessary query in the necessary case.
-====DBMS_SQL====+=====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") 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")
  
Line 86: Line 88:
  
 We will examine each of these executions. We will examine each of these executions.
-===DDL & Session Control===+====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: DDL statment or Data Definition language statment is a statment which create, alters or delete definitions of data. It includes the following commands:
   * Create   * Create
Line 119: Line 121:
 In Oracle DB, DDLs are auto-commit. In Oracle DB, DDLs are auto-commit.
  
-===DML Statment===+====DML Statment====
 DML is Data Modification Language is a language used to modify the data, it includes the following commands: DML is Data Modification Language is a language used to modify the data, it includes the following commands:
   * Update   * Update
Line 125: Line 127:
   * Insert   * Insert
  
-In order to execute DML using DBMS_SQL we need to pass through the following steps from the SQL Processing Flow: +In order to execute DML using DBMS_SQL we need to pass through the following steps from the SQL Processing Flow APIs: 
-  * Unordered List Item+  * 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: So let's check an example of this:
  
 <Code:pl/sql|Example Dynamic DML> <Code:pl/sql|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;
 +</Code>
  
 +====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
 +);                
 +<Code>
 +
 +===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;
 </Code> </Code>
  
-===Anonymous Block=== +More info about this record, below: 
-<Code:pl/sql|Example Anonymous Block>+ 
 +===DESC_REC=== 
 +The last collection has relation with the previous collection as follows: 
 + 
 +<Code:bash|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 
 +</Code> 
 + 
 +Each desk_tab points to a desc_rec collection with the following content: 
 + 
 +<Code:bash|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; 
 +</Code> 
 + 
 +With the new versions, there is slightly different provided values, you can check all the values in that collections: [[https://docs.oracle.com/cd/E21901_01/timesten.1122/e21645/d_sql.htm#TTPLP058|here]] 
 + 
 +Phew, gosh, that was a long walk, let's see example finally: 
 + 
 +===Example=== 
 +<Code:bash|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; 
 +</Code> 
 + 
 +====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+ 
 + 
 +<Code:bash|Security for DBMS_SQL> 
 +DBMS_SQL.OPEN_CURSOR(security _level IN INTEGER) 
 +</Code> 
 + 
 +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:
  
 +<Code:bash|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.
 </Code> </Code>
  
  
-===Select Statment=== +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:
-<Code:pl/sql|Example Select Statement>+
  
 +<Code:bash|DBMS_SQL Error>
 +DBMS_SQL access denied.
 </Code> </Code>
 =====Differences===== =====Differences=====
Line 162: Line 299:
   * Used for: Dynamic Queries and Sorts (Queries which can change depending on the user's preferences)   * Used for: Dynamic Queries and Sorts (Queries which can change depending on the user's preferences)
   * DDL can be used, unlike Static SQL   * 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.
  • plsql_static_dynamic_sql.1590938815.txt.gz
  • Last modified: 2020/05/31 15:26
  • by andonovj