Oracle, Dynamic SQL and Dynamic PL/SQL Usage

Hi everyone.

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

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.

dbms_sql

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

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.

Advertisements

About ... from Emrah METE

Bilgisayar Mühendisi
This entry was posted in Oracle, Root and tagged , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s