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