Using SQL%ROWCOUNT with Dynamic PL/SQL

Using SQL%ROWCOUNT attribute when executing static or dynamic DML statement is very handy. Situation changes dramatically when you check this attribute after executing dynamic or static PL/SQL command:

Static PL/SQL

BEGIN
    NULL;
    dbms_output.put_line('Rowcount=' || SQL%ROWCOUNT);
END;
/

Result:

Rowcount=

Dynamic PL/SQL

BEGIN
    EXECUTE IMMEDIATE 'BEGIN NULL; END;';
    dbms_output.put_line('Rowcount=' || SQL%ROWCOUNT);
END;
/

Result:

Rowcount=1

Static PL/SQL “has” NULL as SQL%ROWCOUNT value while Dynamic PL/SQL – always “produces” 1, even if that dynamic PL/SQL does affect certain number of records in a table:

Dynamic SQL:

BEGIN
    EXECUTE IMMEDIATE 'DELETE FROM emp WHERE ROWNUM<=2';
    dbms_output.put_line('Rowcount=' || SQL%ROWCOUNT);
    ROLLBACK;
END;
/

Result:

Rowcount=2

Same command in Dynamic PL/SQL:

BEGIN
    EXECUTE IMMEDIATE 'BEGIN DELETE FROM emp WHERE ROWNUM<=2; END;';
    dbms_output.put_line('Rowcount=' || SQL%ROWCOUNT);
    ROLLBACK;
END;
/

Result:

Rowcount=1

Sometimes, we have to use dynamic PL/SQL so getting correct number of affected rows may be critical. Here is a simple but effective solution:

DECLARE
    v_cnt NUMBER;
BEGIN
    EXECUTE IMMEDIATE 'BEGIN 
                           DELETE FROM emp WHERE ROWNUM<=2; 
                           :0:=SQL%ROWCOUNT; 
                       END;'  USING OUT v_cnt;
    dbms_output.put_line('Rowcount=' || v_cnt);
    ROLLBACK;
END;
/

Result:

Rowcount=2

We use bind variable in the OUT mode to get the result of STATIC SQL inside of

DYNAMIC PL/SQL.

 

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds”.

A few thoughts on parameterized cursors.

In PL/SQL, parameterized cursors offer a great deal of flexibility and clarity of the code.

Let’s illustrate this point with a specific (though artificial) example.

Let say, we need to show all employees who is paid above average in their respective department. In SQL, the solution would be quite simple:

SQL> SELECT deptno, ename, sal
  2  FROM emp e
  3  WHERE sal>(SELECT AVG(sal)
  4             FROM emp
  5             WHERE deptno=e.deptno)
  6  ORDER BY deptno, sal DESC, ename;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 KING             5000
        20 FORD             3000
        20 SCOTT            3000
        20 JONES            2975
        30 BLAKE            2850
        30 ALLEN            1600

We are going to solve the same simple problem in PL/SQL (using anonymous block). The first example will utilize 2 PL/SQL variables instead of cursor parameters:

SET SERVEROUTPUT ON FORMAT WRAPPED

DECLARE
  CURSOR d IS
  SELECT deptno, AVG(sal) avg_sal
  FROM emp
  GROUP BY deptno
  ORDER BY 1;
  v_deptno  NUMBER;
  v_avg_sal NUMBER;
  CURSOR e IS
  SELECT ename, sal
  FROM emp
  WHERE deptno=v_deptno
    AND sal>v_avg_sal
  ORDER BY sal DESC, ename;
BEGIN
  DBMS_OUTPUT.PUT_LINE('deptno ename       sal');
  DBMS_OUTPUT.PUT_LINE('------ -------- ------');
  FOR v1 IN d LOOP
    v_deptno:=v1.deptno;
    v_avg_sal:=v1.avg_sal;
    FOR v2 IN e LOOP
      DBMS_OUTPUT.PUT_LINE(LPAD(v1.deptno,6) || ' ' || RPAD(v2.ename, 9) || LPAD(v2.sal, 6));
    END LOOP;
  END LOOP;
END;
/

deptno ename       sal
------ -------- ------
    10 KING       5000
    20 FORD       3000
    20 SCOTT      3000
    20 JONES      2975
    30 BLAKE      2850
    30 ALLEN      1600

Note the use of the “SET SERVEROUTPUT ON FORMAT WRAPPED” sqlplus command. You can read more about it here.

Alternatively, we can you a record variable:

DECLARE
  CURSOR d IS
  SELECT deptno, AVG(sal) avg_sal
  FROM emp
  GROUP BY deptno
  ORDER BY 1;
  v_dept d%ROWTYPE;
  CURSOR e IS
  SELECT ename, sal
  FROM emp
  WHERE deptno=v_dept.deptno
    AND sal>v_dept.avg_sal
  ORDER BY sal DESC, ename;
BEGIN
  DBMS_OUTPUT.PUT_LINE('deptno ename       sal');
  DBMS_OUTPUT.PUT_LINE('------ -------- ------');
  FOR v1 IN d LOOP
    v_dept:=v1;
    FOR v2 IN e LOOP
      DBMS_OUTPUT.PUT_LINE(LPAD(v1.deptno,6) || ' ' || RPAD(v2.ename, 9) || LPAD(v2.sal, 6));
    END LOOP;
  END LOOP;
END;

A better way to pass variables to a cursor is to use cursor parameters:

DECLARE
  CURSOR d IS
  SELECT deptno, AVG(sal) avg_sal
  FROM emp
  GROUP BY deptno
  ORDER BY 1;
  CURSOR e(c_deptno NUMBER, c_avg_sal NUMBER) IS
  SELECT ename, sal
  FROM emp
  WHERE deptno=c_deptno
    AND sal>c_avg_sal
  ORDER BY sal DESC, ename;
BEGIN
  DBMS_OUTPUT.PUT_LINE('deptno ename       sal');
  DBMS_OUTPUT.PUT_LINE('------ -------- ------');
  FOR v1 IN d LOOP
    FOR v2 IN e(v1.deptno, v1.avg_sal) LOOP
      DBMS_OUTPUT.PUT_LINE(LPAD(v1.deptno,6) || ' ' || RPAD(v2.ename, 9) || LPAD(v2.sal, 6));
    END LOOP;
  END LOOP;
END;
/

deptno ename       sal
------ -------- ------
    10 KING       5000
    20 FORD       3000
    20 SCOTT      3000
    20 JONES      2975
    30 BLAKE      2850
    30 ALLEN      1600

As you can see, no variable declaration and assignment is needed we; instead, we declare cursor parameters. This gives the code better clarity and readability as both, the cursor parameters and the cursor itself are defined in one place. You don’t need any intermediary variable for opening a nested cursor.

The following example will optimize the last block by using a single cursor parameter:

DECLARE
  CURSOR d IS
  SELECT deptno, AVG(sal) avg_sal
  FROM emp
  GROUP BY deptno
  ORDER BY 1;
  CURSOR e(c_dept d%ROWTYPE) IS
  SELECT ename, sal
  FROM emp
  WHERE deptno=c_dept.deptno
    AND sal>c_dept.avg_sal
  ORDER BY sal DESC, ename;
BEGIN
  DBMS_OUTPUT.PUT_LINE('deptno ename       sal');
  DBMS_OUTPUT.PUT_LINE('------ -------- ------');
  FOR v1 IN d LOOP
    FOR v2 IN e(v1) LOOP
      DBMS_OUTPUT.PUT_LINE(LPAD(v1.deptno,6) || ' ' || RPAD(v2.ename, 9) || LPAD(v2.sal, 6));
    END LOOP;
  END LOOP;
END;
/

So what is the main advantages of using parameterized cursors over using cursors with [bind] variables?

  • Parameterized cursors support default values for cursor parameters
  • The cursors can be referenced more than once with different parameter values
  • A cursor with parameter(s) encapsulates all information necessary for opening and fetching data which makes it safer for use as you don’t need to trace the assignment of cursor parameters/variables all over the place.

 

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

 

Create and test a function that returns a cursor.

Interview Question: How to create and test a function that returns a cursor?

Level: Beginner/Intermediate

Step 1: Create a sample function

All you need to know to complete this step is how to use SYS_REFCURSOR type:

SQL> CREATE OR REPLACE FUNCTION get_emp_data RETURN SYS_REFCURSOR
  2  AS
  3     c SYS_REFCURSOR;
  4  BEGIN
  5     OPEN c FOR
  6     SELECT empno,ename,job,deptno
  7     FROM emp;
  8
  9     RETURN c;
 10  END;
 11  /

Function created.

Step 2: Test the new function
I will demonstrate 3 methods/workarounds for testing the function that has just been created.

Method/Workaround #1: Using Anonymous Block
Assumption: We know the structure of the returning cursor, i.e fields and their data types.

set serveroutput on size 2000 format wrapped

DECLARE
  c_emp SYS_REFCURSOR;
  TYPE typ_emp IS RECORD
   (
      empno  emp.empno%TYPE,
      ename  emp.ename%TYPE,
      job    emp.job%TYPE,
      deptno emp.deptno%TYPE
   );
   v typ_emp;
BEGIN
   c_emp:=get_emp_data;
   --Print the header line:
   DBMS_OUTPUT.PUT_LINE(LPAD('empno', 10) || '  ' ||
                        RPAD('ename', 15) ||
                        RPAD('job', 15) ||
                        LPAD('deptno', 6)
                        );
   DBMS_OUTPUT.PUT_LINE(LPAD('------', 10) || '  ' ||
                        RPAD('------', 15) ||
                        RPAD('------', 15) ||
                        LPAD('------', 6)
                        );
   LOOP
       FETCH c_emp INTO v;
       EXIT WHEN c_emp%NOTFOUND;
       --Print the current employee record:
       DBMS_OUTPUT.PUT_LINE(LPAD(v.empno, 10) || '  ' ||
                            RPAD(v.ename, 15) ||
                            RPAD(v.job, 15) ||
                            LPAD(v.deptno, 6)
                           );
   END LOOP;
   CLOSE c_emp;
END;
/
     empno  ename          job            deptno
    ------  ------         ------         ------
      7369  SMITH          CLERK              20
      7499  ALLEN          SALESMAN           30
      7521  WARD           SALESMAN           30
      7566  JONES          MANAGER            20
      7654  MARTIN         SALESMAN           30
      7698  BLAKE          MANAGER            30
      7782  CLARK          MANAGER            10
      7788  SCOTT          ANALYST            20
      7839  KING           PRESIDENT          10
      7844  TURNER         SALESMAN           30
      7876  ADAMS          CLERK              20
      7900  JAMES          CLERK              30
      7902  FORD           ANALYST            20
      7934  MILLER         CLERK              10

PL/SQL procedure successfully completed.

Method/Workaround #2: Using Select Statement
This method does not require us to know the structure of the cursor returned by the function:

SQL> SELECT get_emp_data
  2  FROM dual;

GET_EMP_DATA
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7369 SMITH      CLERK             20
      7499 ALLEN      SALESMAN          30
      7521 WARD       SALESMAN          30
      7566 JONES      MANAGER           20
      7654 MARTIN     SALESMAN          30
      7698 BLAKE      MANAGER           30
      7782 CLARK      MANAGER           10
      7788 SCOTT      ANALYST           20
      7839 KING       PRESIDENT         10
      7844 TURNER     SALESMAN          30
      7876 ADAMS      CLERK             20
      7900 JAMES      CLERK             30
      7902 FORD       ANALYST           20
      7934 MILLER     CLERK             10

14 rows selected.

Method/Workaround #3: Using Bind Variable and PRINT command in SQL*PLus
The following method does not require us to know the structure of the cursor returned by the function, but it can only be executed in SQL*Plus:

SQL> var c refcursor
SQL> exec :c:=get_emp_data

PL/SQL procedure successfully completed.

SQL> print c

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7369 SMITH      CLERK             20
      7499 ALLEN      SALESMAN          30
      7521 WARD       SALESMAN          30
      7566 JONES      MANAGER           20
      7654 MARTIN     SALESMAN          30
      7698 BLAKE      MANAGER           30
      7782 CLARK      MANAGER           10
      7788 SCOTT      ANALYST           20
      7839 KING       PRESIDENT         10
      7844 TURNER     SALESMAN          30
      7876 ADAMS      CLERK             20
      7900 JAMES      CLERK             30
      7902 FORD       ANALYST           20
      7934 MILLER     CLERK             10

14 rows selected.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

How to Find Bind Variable Values for SQL Currently Executed by Oracle Server, by Zahar Hilkevich

Finding SQL that is currently being executed is fairly trivial – all we need to know is SID of the current session which can be found in v$session dynamic performance view. However, we also need to lookup the values for the bind variables used in the retrieved SQL.

Let’s get the SQL string first:

SELECT b.*
FROM v$session a JOIN v$sql b ON a.sql_id=b.sql_id
WHERE a.sid=[#sid#]

If the actual SQL text is long, we may want to join v$sqltext view:

SELECT b.sql_text
FROM v$session a JOIN v$sqltext b ON a.sql_id=b.sql_id
WHERE a.sid=[#sid#]
ORDER BY b.piece

So far so good, the only thing that may concern you at this moment is bind variables shown in the SQL.
You may need the actual values and not the variable names.
Here is the solution – we need to use v$sql_bind_capture view:

SELECT b.*
FROM v$session a JOIN v$sql_bind_capture b ON a.sql_id=b.sql_id
WHERE a.sid=[#sid#]
ORDER BY child_number, position

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

How to bind IN and OUT parameters in PL/SQL

Suppose that you need to execute dynamic PL/SQL, a stored procedure, with one in and one out parameter:

CREATE OR REPLACE PROCEDURE scott.get_emp_count(p_deptno IN NUMBER, p_count OUT NUMBER)
AS
BEGIN
    SELECT COUNT(*) INTO p_count
    FROM emp
    WHERE deptno=p_deptno;
END get_emp_count;

Now we want to call it in SQL*Plus as follows:

DECLARE
   v_sql VARCHAR2(2000);
   v_count NUMBER;
   v_deptno NUMBER:=10;
BEGIN
   v_sql:='CALL get_emp_count(:0, :1)';
   EXECUTE IMMEDIATE v_sql USING v_deptno, v_count;

   DBMS_OUTPUT.PUT_LINE(v_count);
END;
/
*
ERROR at line 1:
ORA-06536: IN bind variable bound to an OUT position
ORA-06512: at line 7

To make it work we need to use OUT keyword before v_count variable:

DECLARE
   v_sql VARCHAR2(2000);
   v_count NUMBER;
   v_deptno NUMBER:=10;
BEGIN
   v_sql:='CALL get_emp_count(:0, :1)';
   EXECUTE IMMEDIATE v_sql USING v_deptno, OUT v_count;

   DBMS_OUTPUT.PUT_LINE(v_count);
END;
/
3

PL/SQL procedure successfully completed.

Bind parameter mode is similar to a parameter mode in a stored procedure – IN is the default and we don’t have to use it, though we can:

  1  DECLARE
  2     v_sql VARCHAR2(2000);
  3     v_count NUMBER;
  4     v_deptno NUMBER:=10;
  5  BEGIN
  6     v_sql:='CALL get_emp_count(:0, :1)';
  7     EXECUTE IMMEDIATE v_sql USING IN v_deptno, OUT v_count;
  8     DBMS_OUTPUT.PUT_LINE(v_count);
  9* END;
SQL> /
3

PL/SQL procedure successfully completed.

This technique is useful when you don’t know the name of the stored procedure to be executed with EXECUTE IMMEDIATE statement in advance, though you know the parameters.

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.