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/06/04 15:18] – [Security with DBMS_SQL] 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===+====Statment====
 To define a static SQL, let's check the following example: To define a static SQL, let's check the following example:
  
Line 37: 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 58: 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 88: 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 121: 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 162: Line 162:
 </Code> </Code>
  
-===Security with DBMS_SQL===+====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> 
 + 
 +More info about this record, below: 
 + 
 +===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+ 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+
  
Line 211: 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.1591283885.txt.gz
  • Last modified: 2020/06/04 15:18
  • by andonovj