In this article I’ll mention how and by which methods dynamic SQLs are created. Let’s first talk about SQL concept briefly.
Dynamic SQL: Dynamic SQLs are the SQL statements which are not created during design phase, but during run time. They are no different than the SQL statements written in terminals. Query is processed, and if there is any result coming back, it’s returned back to the program. We can presently perform dynamic SQL implementation using two different methods. The first one is using DBMS_SQL (DBMS_SQL Pack has existed since Oracle version 8.1.). And the other method is using the expression EXECUTE_IMMEDIATE (EXECUTE_IMMEDIATE has existed since Oracle version 8).
Using EXECUTE_IMMEDIATE comes across as a handier and more productivity-boosting method. However, array processing, cursor processing, and control features of DBMS_SQL uncovers certain advantages in respect of performance.
Now, let’s examine these two different methods and their utilizations.
DBMS_SQL pack can run almost every sequential dynamic SQL or dynamic PL/SQL block. DBMS_SQL pack, due to its structural body, needs a lot of function frames while processing dynamic queries or blocks. By scanning the following flow diagram, you can comprehend more clearly how DBMS_SQL pack works.
Now, let’s see how DBMS_SQL is used. First, let’s create a temporary table and type data in it to apply our examples.
CREATE TABLE TRIAL ( COLA VARCHAR2(100 BYTE), COLB INTEGER ) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING; / — insert clauses Insert into TRIAL(COLA, COLB) Values ('2', 2); Insert into TRIAL(COLA, COLB) Values ('3', 3); Insert into TRIAL(COLA, COLB) Values ('4', 4); COMMIT;
After we’ve created our sample table, we can start using our DBMS_SQL pack. We’ll see how DBMS_SQL pack is simply used in the next example.
declare cursor_name number; -- Step 1 rows_processed number; begin cursor_name := dbms_sql.open_cursor; -- Step 2 dbms_sql.parse (cursor_name ,'DELETE FROM trial where cola=1' ,dbms_sql.native) -- Step 3 — dbms_sql.bind_variable(cursor_name ,? ,?); -- Step 4 rows_processed := dbms_sql.execute(cursor_name); --Step 5 dbms_sql.close_cursor(cursor_name); --Step 6 dbms_output.put_line(rows_processed); exception when others then dbms_sql.close_cursor(cursor_name); end;
To explain the steps sequentially I’ve written above:
Step 1: The first step we’ll take towards running our dynamic query will be a cursor description in order to open cursor, as it is seen in the above flow. We carry out this processing in the first step. The cursor type we just described must be integer or number.
Step 2: In this step, we perform the action of opening cursor which we described as the first step in the flow diagram.
Step 3: We give the cursor we opened to dbms_sql.parse as a parameter along with the sql which will be parsed. In this step, processing of parsing of the sql we just gave will be performed in run time.
Step 4: This step becomes optional according to dynamic queries we will run. In a situation like the example above, step 4 will be optional. However, because dynamic SQLs are always shaped by the external parameters in our everyday life, this step is frequently used in our dynamic SQLs.
Step 5: In this step execution of the dynamic SQL which was prepared in the above steps will be performed. It needs to be remembered that this processing must be equalized to a number or integer variable; otherwise errors in run time will emerge. The value which is appointed back to our variable after execute processing will be the row number that will be affected as a consequence of that query.
Step 6: In this step, we perform the processing which we pointed as the last step in the flow. We close the cursor we had opened. We’ll observe that when we run the piece of code we wrote above, one registry will be removed, and the affected row number value will be pressed as 1.
Now, let’s write an example using DBMS_SQL to express how we can get value from dynamic SQL as a consequence of bind variable and query.
Ex: In this example, let’s write a procedure which will take table name and column name as a parameter, find the maximum value of the relevant column, and enter it to the table trial2, and a pl/sql block that will test this procedure.
CREATE OR REPLACE FUNCTION dynamicsql ( p_table_name VARCHAR2, p_column_name VARCHAR2 ) RETURN NUMBER IS cursor_name NUMBER; rows_processed NUMBER; column_max_value NUMBER := 0; v_sql VARCHAR2 (1000); BEGIN v_sql := ‘select max(‘ || p_column_name || ‘) from ‘ || p_table_name; cursor_name := DBMS_SQL.open_cursor; DBMS_SQL.parse (cursor_name, v_sql, DBMS_SQL.v7); DBMS_SQL.define_column (cursor_name, 1, column_max_value); rows_processed := DBMS_SQL.EXECUTE (cursor_name); rows_processed := DBMS_SQL.fetch_rows (cursor_name); DBMS_SQL.COLUMN_VALUE (cursor_name, 1, column_max_value); DBMS_OUTPUT.put_line (column_max_value); v_sql := ‘insert into deneme2 values(”’ || p_column_name || ”’,:x)’; DBMS_SQL.parse (cursor_name, v_sql, DBMS_SQL.v7); DBMS_SQL.bind_variable (cursor_name, ‘:x’, column_max_value); rows_processed := DBMS_SQL.EXECUTE (cursor_name); DBMS_SQL.close_cursor (cursor_name); RETURN 1; EXCEPTION WHEN OTHERS THEN RETURN -1; END;
the table trial2 and pl/sql block to test this procedure is below:
CREATE TABLE DENEME2 ( COLNAME VARCHAR2(1000 BYTE), MAX_VALUE NUMBER ) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING; DECLARE x NUMBER; BEGIN x := dynamicsql (‘DENEME’, ‘COLA’); DBMS_OUTPUT.put_line (x); END;
In the above example we did the processing of creating dynamic sql, taking the result from our sql in a variant form, and inserting this variant in another table.
While performing these processes, we also observed both how bind variable is used and how it’s used in DBMS_SQL pack. And now we will implement an array_processing example, which is one of the most important functions of DBMS_SQL pack. A reduction of approximately 75% from execution time can be acquired by means of array processing feature. And this comes as a very significant feature that will enhance the performance of our dynamic queries.
An Array Processing Example with DBMS_SQL: Let’s create two rows and perform insert processing in trial3 table with dynamic sql.
create table deneme3 (cola number,colb number); set timing on DECLARE v_array1 DBMS_SQL.number_table; v_array2 DBMS_SQL.number_table; mycursor INTEGER; processed INTEGER; BEGIN — DIZILERI DOLDURUYORUZ FOR i IN 1 .. 1000000 LOOP v_array1 (i) := i + 5; v_array2 (i) := i + 6; END LOOP; — DINAMIK SQL LI OLUSTURUYORUZ mycursor := DBMS_SQL.open_cursor; DBMS_SQL.parse (mycursor, ‘INSERT INTO DENEME3(COLA,COLB) VALUES(:V1,:V2)’, DBMS_SQL.native ); DBMS_SQL.bind_array (mycursor, ‘:V2’, v_array2); DBMS_SQL.bind_array (mycursor, ‘:V1’, v_array1); processed := DBMS_SQL.EXECUTE (mycursor); DBMS_SQL.close_cursor (mycursor); END; PL/SQL procedure successfully completed. Elapsed: 00:00:03.90
Yes, we were able write 1 million registries by means of DBMS_SQL pack using Oracle’s Array Processing feature.
As you can understand from the examples we took on, running dynamic SQL with DBMS_SQL is a little bit complicated. I expressed this at the beginning of my writing. If we are taking the trouble of using DBMS_SQL pack we need to analyze well if we are going to use Array Processing or Bind Variables. Otherwise, we may make our code unnecessarily complicated.
Now let’s examine how EXECUTE IMMEDIATE expression is used, which is another method for processing dynamic SQL.
EXECUTE IMMEDIATE structure has existed since Oracle 8i. It’s a formation which allows us to create and run our DML and DDL processing in a dynamic way and in run time. However, the dynamic queries we will run need to spin only one row. Otherwise we can’t use this structure. As we mentioned earlier, the most frequently used dynamic SQL forming and running method by DB developers is EXECUTE IMMEDIATE. Now let’s run dynamic SQL by using EXECUTE IMMEDIATE expression.
DECLARE v_column_name1 VARCHAR2 (1000) := ‘colA’; v_column_name2 VARCHAR2 (1000) := ‘colB’; v_table_name VARCHAR2 (1000) := ‘DENEME3’; v_sql VARCHAR2 (1000); value1 NUMBER; value2 NUMBER; into_1 NUMBER; into_2 NUMBER; BEGIN — Ornek 1: dogrudan calistirma v_sql := ‘truncate table ‘ || v_table_name; EXECUTE IMMEDIATE v_sql; — Ornek 2 : bind variable kullanimi value1 := 100; value2 := 150; v_sql := ‘insert into ‘ || v_table_name || ‘ values(:1,:2)’; EXECUTE IMMEDIATE v_sql USING value1, value2; COMMIT; — Ornek 3 : select sonucu degiskenlere alinir v_sql := ‘select ‘ || v_column_name1 || ‘,’ || v_column_name2 || ‘ from ‘ || v_table_name || ‘ where colb=150’; EXECUTE IMMEDIATE v_sql INTO into_1, into_2; DBMS_OUTPUT.put_line (‘deger1: ‘ || into_1 || ‘ deger2:’ || into_2); — Ornek 4: hem bind variable alma, hemde disariya deger cikarma. v_sql := ‘select ‘ || v_column_name1 || ‘,’ || v_column_name2 || ‘ from ‘ || v_table_name || ‘ where colb=:xyz’; EXECUTE IMMEDIATE v_sql INTO into_1, into_2 USING value2; DBMS_OUTPUT.put_line (‘deger1: ‘ || into_1 || ‘ deger2:’ || into_2); END;
Four different usages of EXECUTE IMMEDIATE are shown in the above example. As you can perceive from the codes, creating dynamic SQL with EXECUTE IMMEDIATE is quite quick and less complicated compared to DBMS_SQL. As we discussed earlier, if we’re not doing array processing, taking advantage of dynamic SQL structure with EXECUTE IMMEDIATE will be quicker and more efficient. However, we need to remember to prefer this method when necessary, without ignoring the speed of array processing structure.