Bulk Rowcount

Bulk Rowcount

Lets assume, emp table has below data

	EMPNO	ENAME	JOB	MGR	SAL	DEPTNO
1	7782	CLARK	MANAGER	7839	2450.00	10
2	7839	KING	PRESIDENT		5000.00	10
3	7934	MILLER	CLERK	7782	1300.00	10
4	7566	JONES	MANAGER	7839	2975.00	20
5	7788	SCOTT	ANALYST	7566	3000.00	20
6	7876	ADAMS	CLERK	7788	1100.00	20
7	7369	SMITH	CLERK	7902	800.00	20
8	7499	ALLEN	SALESMAN	7698	1600.00	30
9	7521	WARD	SALESMAN	7698	1250.00	30
10	7844	TURNER	SALESMAN	7698	1500.00	30
11	7900	JAMES	CLERK	7698	950.00	30
12	7698	BLAKE	MANAGER	7839	2850.00	30
13	7654	MARTIN	SALESMAN	7698	1250.00	30
14	7902	FORD	ANALYST	7566	3000.00	60

I am writing a script to update data in bulk using a FORALL loop; I also wanted to know the number of rows got updated in each iteration. Using ROWCOUNT, I will know the total count but not for each iteration: See the sample script below

DECLARE

  TYPE larr_typ_dept IS TABLE OF NUMBER;

  larr_dept larr_typ_dept := larr_typ_dept(10, 60);

BEGIN

  FORALL i IN larr_dept.FIRST .. larr_dept.LAST

    UPDATE emp SET sal = sal + 100 WHERE deptno = larr_dept(i);

  dbms_output.put_line('The number of records updated (Count By ROWCOUNT) ' || SQL%ROWCOUNT);

  FOR j IN larr_dept.FIRST .. larr_dept.LAST

  LOOP

    dbms_output.put_line('The number of records updated in ' || j ||

                         'th iteration; For Dept  ' || larr_dept(j) ||

                         ' (Count By BULK_ROWCOUNT) :: ' || SQL%BULK_ROWCOUNT(j));

  END LOOP;

END;

Out put would be

The number of records updated (Count By ROWCOUNT) 4

The number of records updated in 1th iteration; For Dept  10 (Count By BULK_ROWCOUNT) :: 3

The number of records updated in 2th iteration; For Dept  60 (Count By BULK_ROWCOUNT) :: 1

RETURN statement inside a Procedure

Can we have a RETURN statement inside a Procedure?

Yes, as long as RETURN does not return any values

In procedures, a RETURN statement cannot return a value, and therefore cannot contain an expression. The statement simply returns control to the caller before the normal end of the procedure is reached.

Exception Handling - II

RAISE / FORMAT_ERROR_STACK / FORMAT_ERROR_BACKTRACE

RAISE will get you the right error message only if that was the last program executed; assume a scenario where the RAISE is placed in a subprogram which does not handle exception: you will lose the error message, FORMAT_ERROR_STACK and FORMAT_ERROR_BACKTRACE will help there

Sample

CREATE OR REPLACE PROCEDURE p1 IS

BEGIN

  dbms_output.put_line('in p1');

  RAISE no_data_found;

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('From p1:' || 'ERROR' || SQLCODE || ' ' ||

                         substr(SQLERRM, 1, 200));

END;

CREATE OR REPLACE PROCEDURE p2 IS

BEGIN

  dbms_output.put_line('in p2 b4 Calling P1');

  p1;

  dbms_output.put_line('in p2 after Calling P1');

END;

CREATE OR REPLACE PROCEDURE p3 IS

BEGIN

  dbms_output.put_line('in p3 b4 Calling P2');

  p2;

  dbms_output.put_line('in p3 b4 Calling P2');

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('From P3:' || 'ERROR' || SQLCODE || ' ' ||

                         substr(SQLERRM, 1, 200)); 

END;

Output

in p3 b4 Calling P2

in p2 b4 Calling P1

in p1

From p1:ERROR100 ORA-01403: no data found

in p2 after Calling P1

in p3 b4 Calling P2

When the control comes to P3 we do not know what error happened, Adding a RAISE statement will help.

CREATE OR REPLACE PROCEDURE p1 IS

BEGIN

  dbms_output.put_line('in p1');

  RAISE no_data_found;

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('From p1:' || 'ERROR' || SQLCODE || ' ' ||

                         substr(SQLERRM, 1, 200));

    dbms_output.put_line(dbms_utility.format_error_stack);

    RAISE;

END;

CREATE OR REPLACE PROCEDURE p2 IS

BEGIN

  dbms_output.put_line('in p2 b4 Calling P1');

  p1;

  dbms_output.put_line('in p2 after Calling P1');

END;

CREATE OR REPLACE PROCEDURE p3 IS

BEGIN

  dbms_output.put_line('in p3 b4 Calling P2');

  p2;

  dbms_output.put_line('in p3 b4 Calling P2');

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('From P3:' || 'ERROR' || SQLCODE || ' ' ||

                         substr(SQLERRM, 1, 200));

    dbms_output.put_line(dbms_utility.format_error_stack);   

 

END;

Output

in p3 b4 Calling P2

in p2 b4 Calling P1

in p1

From p1:ERROR100 ORA-01403: no data found

ORA-01403: no data found

From P3:ERROR100 ORA-01403: no data found

ORA-01403: no data found

RAISE throws the error. Now FORMAT_ERROR_STACK does not give much information more than SQLERRM, but FORMAT_ERROR_BACKTRACE will give you more information than ERROR_STACK, the output will be similar to the output of the SQLERRM function.

 

CREATE OR REPLACE PROCEDURE p1 IS

BEGIN

  dbms_output.put_line('in p1');

  RAISE no_data_found;

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('From p1:' || 'ERROR' || SQLCODE || ' ' ||

                         substr(SQLERRM, 1, 200));

    dbms_output.put_line(dbms_utility.format_error_stack);

    RAISE;

END;

CREATE OR REPLACE PROCEDURE p2 IS

BEGIN

  dbms_output.put_line('in p2 b4 Calling P1');

  p1;

  dbms_output.put_line('in p2 after Calling P1');

END;

CREATE OR REPLACE PROCEDURE p3 IS

BEGIN

  dbms_output.put_line('in p3 b4 Calling P2');

  p2;

  dbms_output.put_line('in p3 b4 Calling P2');

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('From P3:' || 'ERROR' || SQLCODE || ' ' ||

                         substr(SQLERRM, 1, 200));

    dbms_output.put_line(dbms_utility.format_error_stack);

    dbms_output.put_line(dbms_utility.format_error_backtrace);

 

END;

Output

in p3 b4 Calling P2

in p2 b4 Calling P1

in p1

From p1:ERROR100 ORA-01403: no data found

ORA-01403: no data found

From P3:ERROR100 ORA-01403: no data found

ORA-01403: no data found

ORA-06512: at "SCOTT.P1", line 10

ORA-06512: at "SCOTT.P2", line 4

ORA-06512: at "SCOTT.P3", line 4

 

Exception Handling

How do you throw the exception after logging in the error?

Do a raise after logging the exception

CREATE OR REPLACE PROCEDURE sp_test IS

  ln NUMBER;

BEGIN

  ln := 5 / 0;

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('Error');

    RAISE;

END;

Output would be

Error

And “ORA-01476: divisor is equal to zero” Will be raised

Function inside Procedure

Can we have a function inside procedure?

 

 Sure we can

CREATE OR REPLACE PROCEDURE sp_test (ID IN number)

AS

FUNCTION fn_test (Id IN number)

  RETURN varchar 2

IS

DECLARE eName varchar2(50);

BEGIN

  SELECT Emp_Name INTO eName

    FROM emp WHERE EmpId = Id;

  RETURN oID;

EXCEPTION

  WHEN no_data_found THEN

    eName:= NULL;

    RETURN eName;

END;

BEGIN

  IF(fn_test (ID) IS NULL) THEN

    RAISE_APPLICATION_ERROR(-20001,'EmpId is not valid');

  END IF;

END; 

/

The only disadvantage with this, you will not be able to use this function from any other packages or procedures