Oracle 12c - PLSQL index by table to Java/SQL

My application front end is Java and DB is Oracle. What array should i use in my PLSQL - program parameters to enable JDBC application to invoke them ?

          Until Oracle 11g, The type has to be defined at the schema-level to enable JDBC applications to interface with PLSQL programs. Now in Oracle 12c this restriction is completely removed.

Let's do some blogging

Before we start with Oracle 12c, lets see these restrictions in Oracle 11g.

CREATE OR REPLACE PACKAGE pkg_plsql_12c
AS
TYPE subject IS TABLE OF VARCHAR2 (100) INDEX BY PLS_INTEGER;

TYPE Marks IS RECORD
  (Maths   NUMBER,
    Physics NUMBER );
    
  PROCEDURE input_is_boolean(
      p_name  IN VARCHAR2,
      p_print IN BOOLEAN);
  PROCEDURE print_subjects(
      p_subject IN subject);
  PROCEDURE print_marks(
      p_marks IN marks);
      
END pkg_plsql_12c;
/

CREATE OR REPLACE PACKAGE BODY pkg_plsql_12c
AS
PROCEDURE input_is_boolean(
    p_name  IN VARCHAR2,
    p_print IN BOOLEAN)
IS
BEGIN
  IF p_print THEN
    DBMS_OUTPUT.put_line (p_name);
  ELSE
    DBMS_OUTPUT.put_line ('I DINT PRINT YOUR NAME');
  END IF;
END input_is_boolean;

PROCEDURE print_subjects(
    p_subject IN subject)
IS
BEGIN
  FOR idx IN 1 .. p_subject.COUNT
  LOOP
    DBMS_OUTPUT.put_line ( p_subject (idx));
  END LOOP;
END print_subjects;

PROCEDURE print_marks(
    p_marks IN marks)
IS
BEGIN
  DBMS_OUTPUT.put_line ( 'MATHS--'||p_marks.MATHS);
  DBMS_OUTPUT.put_line ( 'PHYSICS--'||p_marks.PHYSICS);
END print_marks;
END pkg_plsql_12c;
/

In 11g

SQL> select * from v$version;
 
BANNER
--------------------------------------------------------------------------------
 
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


DECLARE
   lv varchar2(10) := 'ORACLE';
   lv_subjects   pkg_plsql_12c.subject;
   lv_marks   pkg_plsql_12c.marks;
BEGIN

  BEGIN
     EXECUTE IMMEDIATE
        'BEGIN pkg_plsql_12c.input_is_boolean(:Name, :Bool); END;'
        USING lv, TRUE; 
  END;
  
  BEGIN
   lv_subjects(1):='ORACLE ASSOCIATIVE ARRAY';   
   EXECUTE IMMEDIATE
      'BEGIN pkg_plsql_12c.print_subjects (:sub); END;'
      USING lv_subjects;   
  END;
  
  BEGIN
  lv_marks.maths := 100;
  lv_marks.physics := 99;
     
     EXECUTE IMMEDIATE
      'BEGIN pkg_plsql_12c.print_marks (:mark); END;'
      USING lv_marks;    
  END;    
END;
Error at line 3
ORA-06550: line 10, column 19:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 8, column 6:
PL/SQL: Statement ignored
ORA-06550: line 17, column 13:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 15, column 4:
PL/SQL: Statement ignored
ORA-06550: line 26, column 13:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 24, column 6:
PL/SQL: Statement ignored

Script Terminated on line 3.

Until Oracle 11g, If an SQL has to be invoked from PLSQL, only SQL supported data types can be bound.The New feature in Oracle 12c is all about - " PLS-00457: expressions have to be of SQL types "

New in 12.1 -        
          Oracle as relaxed these rules, It is possible to

1. Bind a Boolean to an Anonymous Block
2. Bind Records and associative arrays to an Anonymous Block

Lets execute the same in Oracle 12c

SQL> select banner from v$version;

BANNER
-------------------------------------------------------------------------------

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE    12.1.0.1.0      Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

DECLARE
  lv VARCHAR2(10) := 'ORACLE';
  lv_subjects pkg_plsql_12c.subject;
  lv_marks pkg_plsql_12c.marks;
BEGIN
  -------BOOLEAN------------
  EXECUTE IMMEDIATE 'BEGIN pkg_plsql_12c.input_is_boolean(:Name, :Bool); END;' USING lv, TRUE;  
  
  ---ORACLE ASSOCIATIVE ARRAY---------
    lv_subjects(1):='ORACLE ASSOCIATIVE ARRAY';
    EXECUTE IMMEDIATE 'BEGIN pkg_plsql_12c.print_subjects (:sub); END;' USING lv_subjects;
  
  ----------RECORD-------------
    lv_marks.maths   := 100;
    lv_marks.physics := 99;
   EXECUTE IMMEDIATE 'BEGIN pkg_plsql_12c.print_marks (:mark); END;' USING lv_marks;    

END;
/
anonymous block completed
ORACLE
ORACLE ASSOCIATIVE ARRAY
MATHS--100
PHYSICS--99

From Oracle 12c - JDBC applications can call procedure with associative array parameters provided the associative array is declared in a package specification.. You can only index by PLS_INTEGER which must be positive and dense.


Thanks for reading, feel free to leave your comments.