3 Replies Latest reply: Oct 5, 2012 12:13 AM by Billy~Verreynne RSS

    Joining java array with SQL table in a stored procedure

    966284
      Hey,

      I am calling a pl/sql stored procedure from a java program passing two arrays (employees) and (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;

      Where all elements in employees are stored in the employee_id column and departments are stored in the department_id column. So basically I've got a table like:

      l_employee_tbl t_emp_tbl_type;

      Looped through the arrays and stored the data like:

      for i in 1..p_employees.count loop
      l_employee_tbl(i).employee_id := p_employees(i);
      l_employee_tbl(i).deparment_id := p_departments(i);
      end loop;

      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.

      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. And I would really like to use records or some other type where define my PL/SQL data. I know that you should not mix PL/SQL and SQL but in this case I think it is more efficient to try joining these "tables". Does anyone have a solution for this or advice to try something else out? It would be most appreciated.

      Edited by: 963281 on 2012-okt-04 14:25

      Edited by: 963281 on 2012-okt-05 01:53
        • 1. Re: Joining java array with SQL table in a stored procedure
          855199
          -- You should use type objects:

          CREATE OR replace TYPE typeObject IS object
          (
          COSSSESU NUMBER,
               COSSTCON NUMBER
          )
          /

          CREATE TYPE table_typeObject IS TABLE OF typeObject
          /


          declare
          tb table_typeObject;
          nuMax number;
          BEGIN

          tb := table_typeObject();
          tb.extend(3);
          tb(1) := typeObject(1, 1);
          tb(2) := typeObject(2, 1);
          tb(3) := typeObject(3, 1);

          SELECT max(cosssesu)
          INTO nuMax
          FROM table(tb);

          dbms_output.put_Line(nuMax);

          END;
          • 2. Re: Joining java array with SQL table in a stored procedure
            rp0428
            >
            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 
            • 3. Re: Joining java array with SQL table in a stored procedure
              Billy~Verreynne
              963281 wrote:

              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;
              Why do you create an associative array?
              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).