Simple Integer

Simple Integer is introduced in Oracle 11g, its not a new data type but it’s a sub type of PLS_INTEGER. It accepts the values in the range of -2,147,483,648 to 2,147,483,647.

•Nulls are not allowed
declare 
   a simple_integer;
begin
   dbms_output.put_line(a);
end;
/
Output:
ORA-06550: line 2, column 6:
PLS-00218: a variable declared NOT NULL must have an initialization assignment


•Allows Overflow.
e.g:
declare 
   a simple_integer:= 2147483647;
begin
   a:=a+2;
   dbms_output.put_line(a);
end;
/
Output:
-2147483647 

There would be performance boost when simple_integer used in real native compilation.

Performance improvements in 11g: Real native compilation

Native compilation improves the performance of the query execution.
Native compilation was introduced in 9i R2 but C- Compiler was needed to be installed in the server and a setting of the parameter plsql_native_library_dir where the intermediate O/S files are created.

But in 11g the above two are not required only the below setting will do.

alter session set plsql_code_type=native;

There is no much difference in the performance in 11g when compared to 10g for interpreted compilation but the there is significant change for the native compilation.


Note: Native compilation takes long time to compile when compared to interpreted compilation.
To find out which objects have been compiled using NATIVE, you can check the view USER_PLSQL_OBJECT_SETTINGS:

SQL> select name, PLSQL_code_type
  2> from user_plsql_object_settings;

Cursor Variable

Cursor variables can be assigned to another cursor variable, once any of the cursor is closed, automatically the cursor area becomes unavailable and the fetch operation leads to invalid cursor error.

With SYS_REFCUROSR:

DECLARE
    c1     SYS_REFCURSOR;
    c2     SYS_REFCURSOR;
    v_name VARCHAR2(20);
BEGIN
    OPEN c1 FOR
      SELECT table_name
      FROM   user_tables;

    c2 := c1;

    CLOSE c1;

    FETCH c2 INTO v_name;

    dbms_output.Put_line(v_name);
END;

/

ORA-01001: invalid cursor

Below, we will close cursor C2

DECLARE
    c1     SYS_REFCURSOR;
    c2     SYS_REFCURSOR;
    v_name VARCHAR2(20);
BEGIN
    OPEN c1 FOR
      SELECT table_name
      FROM   user_tables;

    c2 := c1;

    CLOSE c2;

    FETCH c2 INTO v_name;

    dbms_output.Put_line(v_name);
END;

/

ORA-01001: invalid cursor


-- The same applies the cursor variables defined using type.

DECLARE
    TYPE type_ref IS REF CURSOR;
    TYPE type_ref2 IS REF CURSOR;
    rc1    TYPE_REF;
    rc2    TYPE_REF2;
    v_name VARCHAR2(20);
BEGIN
    OPEN rc1 FOR
      SELECT table_name
      FROM   user_tables;

    rc2 := rc1;

    CLOSE rc2;

    FETCH rc2 INTO v_name;

    dbms_output.Put_line(v_name);
END;

/

ORA-01001: invalid cursor

-- Below, we will close rc1

DECLARE
    TYPE type_ref IS REF CURSOR;
    TYPE type_ref2 IS REF CURSOR;
    rc1    TYPE_REF;
    rc2    TYPE_REF2;
    v_name VARCHAR2(20);
BEGIN
    OPEN rc1 FOR
      SELECT table_name
      FROM   user_tables;

    rc2 := rc1;

    CLOSE rc1;

    FETCH rc2 INTO v_name;

    dbms_output.Put_line(v_name);
END;

/

ORA-01001: invalid cursor

Dynamic SQL with bind varaibles

Oracle converts all the variables used in the PL/SQL into bind variables for performance optimization.
Bind variables improves the performance of a query singnificantly. It avoids the repetition of parsing.

srikar@ORCL> declare
  2  begin
  3  for a in (select empno from emp) loop
  4  delete from emp where empno=a.empno;
  5  end loop;
  6  
  7  end;
  8  
  9  
 10  /

PL/SQL procedure successfully completed.

Above, the variable (a.empno) used in the delete statement is converted to bind variable hence after running the above block, only one soft parse and 14 executions.
Below is the TKPROF output for the delete statement.

DELETE FROM EMP 
WHERE
 EMPNO=:B1 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute     14      0.00       0.01          1         14         47          14
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       15      0.01       0.01          1         14         47          14

So then, what is the need of dynamic sql with binding enabled.
The theory says, Oracle goes for soft parse for each iteration where the iteration changes the variable value in the query in a *dynamic sql* unless you use 'USING' clasuse to bind the inputs.

The following blocks shows that a dynamic delete statement is used, we dint use the 'USING' clause to bind the variable, we simply used the varaible name. So, oracle goes 
for soft parse for each iteration, as there are 14 rows for the loop, 14 parses happens for the delete statement.


begin
   for a in (select empno from t_emp) loop
      execute immediate 'delete from t_emp where empno= '||a.empno;
   end loop;
end;
/

The TKPROF output,

delete from t_emp 
where
 empno= 7369


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          1          0           0
Execute      1      0.00       0.00          0          3          3           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.00          0          4          3           1


like the above plan (delete from t_emp where empno= 7369) there are 14 different delete statements for each employee number, each went for parse. Oracle didnt use single 
parse.

Then the below is the correct way, to bind the variables when used in dynamic sql

srikar@ORCL> declare
  2  begin
  4  for a in (select empno from t_emp) loop
  5  execute immediate 'delete from t_emp where empno= :a ' using a.empno;
  6  end loop;
  7  
  8  end;
  9  /

PL/SQL procedure successfully completed.

The below is the TKPROF output, it shows that the statement went for single parse and multiple
execution.

delete from t_emp 
where
 empno= :a 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     14      0.00       0.00          0         43         17          14
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       15      0.00       0.00          0         43         17          14

Error Handling

There are three built in routines employed for handling errors efficienlty.
1. DBMS_UTILITY.FORMAT_ERROR_STACK
2. DBMS_UTILITY.FORMAT_CALL_STACK
3. DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

DBMS_UTILITY.FORMAT_ERROR_STACK: This routine is similar to SQLERRM routine, but there are no length constraints as in SQLERRM. We will see an example below in a while.

DBMS_UTILITY.FORMAT_CALL_STACK: Returns the execution call stack down to the point at which you will call this function. It is used mostly while debugging.

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE: Displays the call stack at the point where an exception was raised. It gives the error line number and the complete call stack throgh the procedure names. This routine is particularly useful to dispaly the error message and line number when exception is handled otherwise there is no other way to get the error line number if exception is handled.

Following example shows how to make use of all the three routines.
We will create five procedures p1, p2, p3, p4 and sp_error to illustrate this.
Procedure p1 raises an exception, sp_error calls p4, p4 calls p3, p3 calls p2, p2 calls p1.
So, when we call sp_error, it raises the exception which is propagated from p1.

srikar@ORCL> set echo on
srikar@ORCL> set feedback on
srikar@ORCL> show termout
termout ON
srikar@ORCL> @format_error_stackbacktrace.sql;
srikar@ORCL> create or replace procedure p1
  2  as
  3   l_error exception;
  4   pragma exception_init(l_error, -01408);
  5  begin
  6   raise l_error;
  7  end;
  8  /

Procedure created.

srikar@ORCL> create or replace procedure p2
  2  as
  3  begin
  4   p1;
  5  end;
  6  /

Procedure created.

srikar@ORCL> create or replace procedure p3
  2  as
  3  begin
  4   p2;
  5  end;
  6  /

Procedure created.

srikar@ORCL> create or replace procedure p4
  2  as
  3  begin
  4   p3;
  5  end;
  6  /

Procedure created.

srikar@ORCL> create or replace procedure sp_error
  2  as
  3  begin
  4   p4;
  5  
  6  exception
  7   when others then
  8   dbms_output.put_line ('The format error stack: '||dbms_utility.format_error_stack);
  9  
 10  end;
 11  /

Procedure created.

Now, lets execute the procedure sp_error which calls the procedue format_error_stack to display the error message, it dispalys the error message which is similar to SQLERRM.

srikar@ORCL> execute sp_error;
The format error stack: ORA-01408: such column list already indexed
PL/SQL procedure successfully completed.

Now lets modify the sp_error procedure to include all the three error routines

srikar@ORCL> create or replace procedure sp_error
  2  as
  3  begin
  4     p4;
  5  
  6  exception
  7     when others then
  8     dbms_output.put_line ('The format error stack: '||dbms_utility.format_error_stack);
  9     dbms_output.put_line ('The format error backtrace: '||dbms_utility.format_error_backtrace);
 10     dbms_output.put_line ('The format error callstack: '||dbms_utility.format_call_stack);
 11  
 12  end;
 13  /

Procedure created.

Lets execute the procedure sp_error

srikar@ORCL> execute sp_error;
The format error stack: ORA-01408: such column list already indexed

The format error backtrace: ORA-06512: at "SRIKAR.P1", line 6
ORA-06512: at "SRIKAR.P2", line 4
ORA-06512: at "SRIKAR.P3", line 4
ORA-06512: at "SRIKAR.P4", line 4
ORA-06512: at "SRIKAR.SP_ERROR", line 4

The format error callstack: ----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
000007FF56711398
   10  procedure SRIKAR.SP_ERROR
000007FF566FA308         1  anonymous block
PL/SQL procedure successfully completed.

From the above output, it is clear that format_error_stack shows the error message which is similar to SQLERRM, format_error_backtrace displays the error propagation and line number, format_call_stack dispalys the call stack where it is called.

Lets execute the procedure p4 which inturns calls p1 via p3, p2

srikar@ORCL> execute p4;
BEGIN p4; END;

*
ERROR at line 1:
ORA-01408: such column list already indexed
ORA-06512: at "SRIKAR.P1", line 6
ORA-06512: at "SRIKAR.P2", line 4
ORA-06512: at "SRIKAR.P3", line 4
ORA-06512: at "SRIKAR.P4", line 4
ORA-06512: at line 1

as the exception is not handled in any of these procedures (p1, p2, p3, p4), the error message and line number is propaged to the calling environment.

Lets change the procedure p4 to handle the exception

srikar@ORCL> create or replace procedure p4
  2  as
  3  begin
  4     p3;
  5  exception
  6     when others then
  7     raise;
  8  end;
  9  /
Procedure created.

srikar@ORCL> execute p4;
BEGIN p4; END;
*
ERROR at line 1:
ORA-01408: such column list already indexed
ORA-06512: at "SRIKAR.P4", line 7
ORA-06512: at line 1

as seen it only displays the error line number from the procedure p4 but the not actual propagated error line number which is line 6 from procedure p1.
So, if want to dispaly the exact line number of the error from where it is originated we use dbms_utility.format_error_backtrace.

How to get the INIT.ORA parameter values

Here we see the differenet methods to get the init.ora parameter values

1. Using built in DBMS_UTILITY.GET_PARAMETER_VALUE
2. Show parameter
3. Using V$PARAMETER

Method 1: DBMS_UTILITY.GET_PARAMETER_VALUE
DBMS_UTILITY.GET_PARAMETER_VALUE (arg1, arg2, arg3) takes 3 parameters, arg1 takes IN mode we pass the parameter name for which the value needs to be knows. arg2 and arg3 takes IN OUT mode, arg2 returns the integer value if arg1 is of integer type, arg3 returns the string value if arg1 is of string value. If arg1 of string type then arg3 retruns the string value and arg2 returns the length of the arg3 value.

Here is the sample block to illustrate how to use it.

srikar@ORCL> ed
Wrote file afiedt.buf

  1  declare
  2     l_partype  number;
  3     l_intval   number;
  4     l_strval   varchar2(2000);
  5  begin
  6     dbms_output.put_line ('Enter the parameter name: ');
  7     l_partype :=  dbms_utility.get_parameter_value ('&l_param4',l_intval,l_strval);
  8     dbms_output.put('The parameter type is:');
  9     if (l_partype = 1) then
 10     dbms_output.put('String ');
 11     else
 12     dbms_output.put('Number ');
 13     end if;
 14     dbms_output.put(' and the value is: ');
 15     if (l_partype = 1) then
 16     dbms_output.put_line(l_strval);
 17     else
 18     dbms_output.put_line(l_intval);
 19     end if;
 20     dbms_output.put_line('Both the inout values are: l_intval: '
 21                           ||l_intval
 22                           ||' l_strval: '
 23                           ||l_strval ) ;
 24* end;
srikar@ORCL> /
Enter value for l_param4: control_files
old   7:    l_partype :=  dbms_utility.get_parameter_value ('&l_param4',l_intval,l_strval);
new   7:    l_partype :=  dbms_utility.get_parameter_value ('control_files',l_intval,l_strval);
Enter the parameter name:
The parameter type is:String  and the value is: C:\APP\SRIKARDASARI\ORADATA\ORCL\CONTROL01.CTL
Both the inout values are: l_intval: 46 l_strval: C:\APP\SRIKARDASARI\ORADATA\ORCL\CONTROL01.CTL

PL/SQL procedure successfully completed.

Method 2: SHOW PARAMETER
This is simple and straight forward. We pass the parameter name to SHOW PARAMETER let say for control_files,

srikar@ORCL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      C:\APP\SRIKARDASARI\ORADATA\OR
                                                 CL\CONTROL01.CTL, C:\APP\SRIKA
                                                 RDASARI\FLASH_RECOVERY_AREA\OR
                                                 CL\CONTROL02.CTL 

Method 3: V$PARAMETER
We can query against the V$PARAMETER view to get the parameter name.

srikar@ORCL> select name, value from v$parameter where name='control_files';
NAME    VALUE
-------------------------------------------------------------------------------------------control_files C:\APP\SRIKARDASARI\ORADATA\ORCL\CONTROL01.CTL, C:\APP\SRIKARDASARI\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL

srikar@ORCL> select name, value from v$spparameter where name='control_files';

NAME VALUE
-------------------------------------------------------------------------------------------control_files
C:\app\SrikarDasari\oradata\orcl\control01.ctl

control_files
C:\app\SrikarDasari\flash_recovery_area\orcl\control02.ctl

SQL%BULK_ROWCOUNT and SQL%BULK_EXCEPTIONS

SQL%BULK_ROWCOUNT and SQL%BULK_EXCEPTIONS are used in bulk opeartions with FORALL

SQL%BULK_ROWCOUNT
Let say, we are processing a bulk operation, need to know how many records are processed by each interation for the given DML (Update, Delete and Insert).

We have SQL%BULK_ROWCOUNT attribute to know the number of records processed in each iteration in a bulk operation.

The follwoing example shows how to use it.

srikar@ORCL> create table t_emp2 as select * from emp
  2  /

Table created.

srikar@ORCL> select deptno, count(*)
  2     from t_emp2
  3     where deptno in (10, 30)
  4     group by deptno
  5  /

    DEPTNO   COUNT(*)
---------- ----------
        10          3
        30          6

Dept 30 have 6 rows and dept 10 have 3 rows.

srikar@ORCL> declare
  2  
  3     type lt_dept_arr is table of number;
  4     l_dept_arr lt_dept_arr := lt_dept_arr(10, 30);
  5  
  6  begin
  7  
  8     forall i in l_dept_arr.first..l_dept_arr.last
  9     delete from t_emp2 where deptno = l_dept_arr(i);
 10  
 11     for j in l_dept_arr.first..l_dept_arr.last loop
 12        dbms_output.put_line ('The number of records deleted in '
 13                               || j || 'th iteration are  '
 14                               ||sql%bulk_rowcount(j) );
 15     end loop;
 16  
 17  end;
/
The number of records deleted in 1th iteration are  3
The number of records deleted in 2th iteration are  6

PL/SQL procedure successfully completed.


Handling exceptions in FORAll using SQL%BULK_EXCEPTIONS
PL/SQL provides a mechanism to handle exceptions that were raised during the FORALL bulk operation using SQL%BULK_EXCEPTIONS.

SQL%BULK_EXCEPTIONS argument saves all the exceptions raised during the bulk operation provided FORALL statement should have SAVE EXCEPTIONS added.

SQL%BULK_EXCEPTIONS.count gives the number of exceptions raised in the last FORALL bulk opearation

SQL%BULK_EXCEPTIONS.ERROR_INDEX gives the iteration number in which the exception is raised

SQL%BULK_EXCEPTIONS.ERROR_CODE gives the error code, using which we can find the error message as ERRM( - SQL%BULK_EXCEPTIONS ). noted that '-' is added at the beginning
of the SQL%BULK_EXCEPTIONS in the SQLERRM function.

Lets take an example to illustrate the functionality.

srikar@ORCL> create table t_emp as select * from emp;

Table created.

We have created a table 't_emp' whice is similar to emp table.

Now, we append each empname with ' Oracl' due to which error is raised in the bulk operation as the empname column maximum size is 10 characters and the append is not allowed for few columns,so the records whose length is crossing 10 characters would raise an exception.

In the follwoing code, if an exception is raised then the exception is saved and processed with the next records.


srikar@ORCL> ed
Wrote file afiedt.buf

  1  declare
  2         type lt_emp_arr is table of t_emp.empno%type;
  3         l_emp_arr    lt_emp_arr;
  4         errors       number;
  5  begin
  6         select empno bulk collect into l_emp_arr from t_emp;
  7         forall i in l_emp_arr.first..l_emp_arr.last save exceptions
  8         update t_emp set ename = ename||' Oracl' where empno = l_emp_arr(i) ;
  9  exception
 10       when others then
 11        errors := sql%bulk_exceptions.count;
 12        dbms_output.put_line ('The total number of errors occured are '
 13                                  || errors
 14                             );
 15        for j in 1..errors loop
 16           dbms_output.put_line ('The error iteration is  '
 17                                  || sql%bulk_exceptions(j).error_index
 18                                  || ' and the error code is '
 19                                  || sql%bulk_exceptions(j).error_code
 20                                  || ' and the error message is '
 21                                  || sqlerrm (- sql%bulk_exceptions(j).error_code)
 22                                );
 23        end loop;
 24* end;
 25  /
The total number of errors occured are 11
The error iteration is  1 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  2 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  4 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  5 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  6 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  7 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  8 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  10 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , m
aximum: )
The error iteration is  11 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , m
aximum: )
The error iteration is  12 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , m
aximum: )
The error iteration is  14 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , m
aximum: )

PL/SQL procedure successfully completed.

srikar@ORCL> select ename from t_emp;

ENAME
----------
SMITH
ALLEN
WARD Oracl
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING Oracl
TURNER
ADAMS
JAMES
FORD Oracl
MILLER

14 rows selected.