This content has been marked as final. Show 3 replies
-- You should use type objects:
CREATE OR replace TYPE typeObject IS object
CREATE TYPE table_typeObject IS TABLE OF typeObject
tb := table_typeObject();
tb(1) := typeObject(1, 1);
tb(2) := typeObject(2, 1);
tb(3) := typeObject(3, 1);
I am calling a pl/sql stored procedure from a java program passing two arrays (p_employees) and (p_departments) as parameters to the procedure.
Why are you using this methodology instead of just executing the query from Java to join the tables?
SQL is ALWAYS faster than PL/SQL.
PL/SQL types cannot be used in SQL; as another responder indicated you need to declare SQL types and then you can query them as tables.
This is sample code that will work in the SCOTT schema to do something similar to what you say you want to do.
But I again suggest that you NOT use PL/SQL and should use a SQL solution instead as it is much more scalable and doesn't require additional types to be created.
-- type to match emp record create or replace type emp_scalar_type as object (EMPNO NUMBER(4) , ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7, 2), COMM NUMBER(7, 2), DEPTNO NUMBER(2) ) / -- table of emp records create or replace type emp_table_type as table of emp_scalar_type / declare tb emp_table_type; deptnoList sys.OdciNumberList; BEGIN select emp_scalar_type(empno, ename, job, mgr, hiredate, sal, comm, deptno) bulk collect into tb from emp; SELECT deptno bulk collect INTO deptnoList FROM dept where deptno not in (select deptno from table(tb)); for i in 1..deptnoList.count loop dbms_output.put_Line(deptnoList(i)); end loop; END; 40
963281 wrote:Why do you create an associative array?
I am calling a pl/sql stored procedure from a java program passing two arrays (p_employees) and (p_departments) as parameters to the procedure. Within the procedure I have stored the arrays in a table of records like this:
type t_emp_type is record (employee_id number, department_id number);
type t_emp_tbl_type is table of t_emp_type index by binary_integer;
Now I would like to compare my l_employee_tbl with a SQL table in the database. Basically I would like to join my l_employee_tbl table with a SQL table named departments on department_id, returning all department_id:s that was not found in table departments.Of course, not possible as the SQL engine does not support PL/SQL user defined types. PL/SQL however support user defined SQL types. Which makes SQL defined types a lot more flexible.
However, it is not possible to select from a user-defined table type. I have also tried to use the table() function and to cast l_employee_tbl, but found out it can't be done with records.Never mind SQL and PL/SQL - as a generic programming data structure principle. How do you expect being able to cast an associative array (name-value pairs) to a standard array? The two data structures are very different. So I'm puzzled in how you expect to move a non-scalar name-value pair data structure into a non-scalar value only data structure?
And I would really like to use records or some other type where define my PL/SQL data.Why exactly? If the Java or PL/SQL data structure is populated using SQL data and database data, and wanting to use that data structure in SQL, what is the point? Why pull SQL data into a client data structure at all then - surely it is far more performant and scalable to rather keep that data in the database, and do the joins/selects/filters/etc using SQL?
There is also the issue of scalability of local data structures in PL/SQL. The PL/SQL engine runs inside an Oracle server process, consuming private process memory on the server. The bigger the data structures used in PL/SQL, the more server memory needs to be allocated to that server process. This does not scale. Especially not if 10 or more such server processes are running the same PL/SQL code and each server needs to grab large chunks of server memory.
If the data from Java comes from another source (e.g. keyboard, etc), and you need a means of storing this data server-side for use by PL/SQL and SQL. There are 2 basic choices. PL/SQL arrays for smallish amounts of data - and basing these arrays preferable on SQL data types allowing the array to be used by both SQL and PL/SQL engines. If the amount of data is not smallish, then it should be stored in the SQL engine (database) as that is designed for that exact purpose. And if the data is transient, then a GTT (global temp table) structure can be used (and indexed for optimal access).