DBMS_SQL Package

The DBMS_SQL package allows for precise control over the fetch and processing of rows.  The SQL can be completed programmatically and submitted for parsing.  The key words here are ‘submitted for parsing’; you can force a hard parse upon any SQL using this package.

You can use this to share cursors as well.  Maybe content for another article, but there is overhead in opening and closing cursors.  Implicit cursors (where you just do a ‘select into’ perhaps) do the open/parse/fetch/close and if you are populating variables this way, you are causing a lot of CPU time for your apps.

Opening a cursor and using the DBMS_SQL.Parse command to process different SQL keeps these cursors open and avoids the costly overhead of quickly opening and closing dozens of cursors.

1: Declare

2:    CurID       integer;

3:    v_rows      number;

4:    v_ename   EMP.ENAME%TYPE;

5: Begin

6:     CurID := DBMS_SQL.OPEN_CURSOR;

7:     DBMS_SQL.PARSE(CurID,’SELECT ENAME FROM EMP’,1);

8:     DBMS_SQL.DEFINE_COLUMN(CurID,1,v_ename,10);

9:     v_rows := DBMS_SQL.EXECUTE(CurID);

10:    WHILE DBMS_SQL.FETCH_ROWS(CurID) > 0

11:    LOOP

12:         DBMS_SQL.COLUMN_VALUE(CurID,1,v_ename);

13:         DBMS_OUTPUT.PUT_LINE(‘Ename = ‘ || v_ename);

14:     END LOOP;

15:   DBMS_SQL.CLOSE_CURSOR(CurID);

This example is a simple ‘SELECT ENAME FROM EMP’; see line 7.  The quoted text can be a character variable that contains SQL text.  You can manipulate this SQL if you like prior to the DBMS_SQL_PARSE statement (again, line 7).

Notice that the programming is a bit like file processing, where you have a cursor id variable (see line 6) and you open and store the pointer to this cursor in this variable.  You can have up to 50 cursors by default open in your PL/SQL module…the init.ora setting ‘MAX_CURSORS’ controls how many cursors your program can have open at one time. 

This SQL works with one column so line 8 sets up the variable to hold the ENAME data.  Notice at line 9 you can capture the row count.  Lines 10 thru 14 will loop thru the cursor.  This example is simply printing out the ENAME using DBMS_OUTPUT (see line 13).

You have to close your open cursors or your PL/SQL program will end in an error condition.

IF you want the total number of rows processed, you can use DBMS_SQL.LAST_ROW_COUNT.  This is a function, so use it like this:  v_rows := DBMS_SQL.Last_Row_Count;   Put this statement after the loop and before the closing of the cursor.  The information is lost once the cursor is closed.

1: Declare

2:    CurID       integer;

3:    v_rows      number;

4:    v_ename     EMP.ENAME%TYPE;

5:    v_job       EMP.JOB%TYPE;

6:    v_sal       EMP.SAL%TYPE;

7: Begin

8:     CurID := DBMS_SQL.OPEN_CURSOR;

9:     DBMS_SQL.PARSE(CurID,’select ename, job, sal from emp’,1);

10:    DBMS_SQL.DEFINE_COLUMN(CurID,1,v_ename,10);

11:    DBMS_SQL.DEFINE_COLUMN(CurID,2,v_job,9);

12:    DBMS_SQL.DEFINE_COLUMN(CurID,3,v_sal);

13:    v_rows := DBMS_SQL.EXECUTE(CurID);

14:   WHILE DBMS_SQL.FETCH_ROWS(CurID) > 0

15:    LOOP

16:         DBMS_SQL.COLUMN_VALUE(CurID,1,v_ename);

17:         DBMS_SQL.COLUMN_VALUE(CurID,2,v_job);

18:         DBMS_SQL.COLUMN_VALUE(CurID,3,v_sal);

19:         DBMS_OUTPUT.PUT_LINE(‘Ename = ‘ || v_ename || ‘ Job = ‘ || v_job || Sal = ‘ || v_sal);

20:     END LOOP;

21: DBMS_OUTPUT.PUT_LINE(‘Total Rows processed = ‘ || DBMS_SQL.LAST_ROW_COUNT);

22:   DBMS_SQL.CLOSE_CURSOR(CurID);

This example selects three columns from the EMP table.  Notice that lines 10 through 12 are the column value clauses indicating the variable to store the selected contents and their relative position in the SELECT clause.

In this example, notice that the DBMS_SQL_FETCH_ROWS function is being used to run the LOOP; when no rows are returned, the loop will exit.  Notice that this example prints the total rows processed using the above-mentioned DBMS_SQL.Last_Row_Count syntax.  Also notice that I use the %TYPE syntax to get the exact column attributes at lines 4 thru 6.

1:    In_percent  number;

2:    CurID         integer;

3:    v_rows        number;

4: Begin

5:    CurID := DBMS_SQL.OPEN_CURSOR;

6:    DBMS_SQL.PARSE(CurID,UPDATE EMP SET SAL = SAL * :v_pct,1);

7:    DBMS_SQL.BIND_VARIABLE(CurID,’:v_pct’,In_percent);

8:    v_rows := DBMS_SQL.EXECUTE(CurID);

9:    DBMS_OUTPUT.PUT_LINE(‘Rows Updated = ‘ || v_rows);

10:   DBMS_SQL.CLOSE_CURSOR(CurID);

This is a DML example and it illustrates how to pass bind variables to a SQL statement.  This example also illustrates the precise control over the PARSE and EXECUTE of a SQL statement.

Notice at line 6 that there is a bind variable in this SQL ‘:v_pct’. Before the EXECUTE at line 8, the bind variable will need to be provided using the DBMS_SQL.Bind_Variabe syntax illustrated at line 7.

Since this is a DML statement, v_rows at line 8 is capturing the number of rows affected by the DML.  The standard exception processing applies here as well, we covered this in a prior article (click here to review the article).

Fuentes.

Artículo:   "Oracle Coding Best Practices - Part 5" Publicado en https://blog.toadworld.com/ por  Dan Hotka el 20 Nov, 2017. Consultado el 10/10/2022.

URL: https://blog.toadworld.com/2017/11/20/oracle-coding-best-practices-part-5