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.
