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:35] – andonovjplsql_static_dynamic_sql [2020/06/04 15:41] (current) – [Differences] andonovj
Line 162: Line 162:
 </Code> </Code>
  
-====Describe Unknown No of Column====+====Describe Unknown No. of Columns====
 One advantage of the DBMS_SQL over the Native Dynamic SQL is the ability to work with: One advantage of the DBMS_SQL over the Native Dynamic SQL is the ability to work with:
  
Line 170: Line 170:
 So let's see how we can work with unknown number of columns. Oracle provided couple APIs for that: So let's see how we can work with unknown number of columns. Oracle provided couple APIs for that:
  
-  * DBMS_SQL.DESC_COLUMNS(2)(3)+  * DBMS_SQL.DESC_COLUMNS(x)
   * DBMS_SQL.DESC_TAB   * DBMS_SQL.DESC_TAB
   * DBMS_SQL.DESC_REC   * DBMS_SQL.DESC_REC
  
 +X - being: ,2 or 3
 Let's go through each of them. Let's go through each of them.
  
Line 179: Line 180:
 It has the following syntax: It has the following syntax:
  
-<Code:bash|DBMS_SQL.DESCRIBE_COLUMNS(x) ( +<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 +c       IN  INTEGER, <- Takes the cursor's ID 
-                               desc_t  OUT DESC_TAB(X)); <- Return associative array (collection) of type DESC_TAB      +col_cnt OUT INTEGER, <- Returns the count of the columns in that Cursor 
-X - 1,2 or 3 (Different versions)                    +desc_t  OUT DESC_TAB(X) <- Return associative array (collection) of type DESC_TAB 
 +);                
 <Code> <Code>
  
 ===DESC_TAB=== ===DESC_TAB===
-That array we saw above and it has the following structure:+That array which we saw above and it has the following structure:
  
 <Code:bash|DBMS_SQL.DESC_TAB> <Code:bash|DBMS_SQL.DESC_TAB>
 TYPE desc_tab IS TABLE OF desc_rec(x) INDEX BY BINARY_INTEGER; TYPE desc_tab IS TABLE OF desc_rec(x) INDEX BY BINARY_INTEGER;
 </Code> </Code>
 +
 +More info about this record, below:
  
 ===DESC_REC=== ===DESC_REC===
Line 222: Line 226:
 </Code> </Code>
  
-With the new tables, there is different syntax, you can check all the values in that collections: [[https://docs.oracle.com/cd/E21901_01/timesten.1122/e21645/d_sql.htm#TTPLP058|here]]+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: Phew, gosh, that was a long walk, let's see example finally:
Line 295: 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.1591284942.txt.gz
  • Last modified: 2020/06/04 15:35
  • by andonovj