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/03 07:04] – [Static 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====
 To define a static SQL, let's check the following example: To define a static SQL, let's check the following example:
  
Line 24: Line 26:
 END get_count; END get_count;
 </Code> </Code>
- 
- 
-===DDL Statment=== 
- 
- 
-===DML Statment=== 
- 
- 
-===Select Statment=== 
  
 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. 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.
Line 44: 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 65: 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 95: 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 128: 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 169: Line 162:
 </Code> </Code>
  
-===Anonymous Block=== +====Describe Unknown No. of Columns==== 
-<Code:pl/sql|Example Anonymous Block>+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>
  
 +More info about this record, below:
  
-===Select Statment=== +===DESC_REC=== 
-<Code:pl/sql|Example Select Statement>+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>
 +
 +
 +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:bash|DBMS_SQL Error>
 +DBMS_SQL access denied.
 </Code> </Code>
 =====Differences===== =====Differences=====
Line 197: 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.1591167867.txt.gz
  • Last modified: 2020/06/03 07:04
  • by andonovj