2 Replies Latest reply: Jan 30, 2013 11:58 AM by rp0428 RSS

    Oracle to Java communication

    SamFisher
      Hello All,

      I'm a PL/SQL developer. I'm a rookie to Java.
      My Requirement:
      I have an Oracle procedure which has collection as input parameter. I want the array/collection to be passed from front end.

      Is it possible to pass a collection from frontend to Oracle procedure?
      Something like this
      --A collection (empArray) is created in a package.
      
      CREATE OR REPLACE PACKAGE test_pkg
      AS
         TYPE empArray IS TABLE OF emp%ROWTYPE
            INDEX BY BINARY_INTEGER;
      
         PROCEDURE emp_report (p_inputs IN empArray);
      END test_pkg;
      
      
      --Pkg Body
      CREATE OR REPLACE PACKAGE BODY test_pkg
      AS
         PROCEDURE emp_report (p_inputs IN empArray)
         IS
         BEGIN
            FOR i IN 1 .. p_inputs.COUNT
            LOOP
               DBMS_OUTPUT.put_line (
                     'Empno = '
                  || p_inputs (i).empno
                  || ' Deptno = '
                  || p_inputs (i).deptno);
            END LOOP;
         END;
      END test_pkg;
      
      
      DECLARE
         my_data   test_pkg.empArray;
      BEGIN
         -- Can front end assign the value to collection variable like?
         my_data (1).empno := 1234;
         my_data (1).deptno := 10;
      
         my_data (2).empno := 4567;
         my_data (2).deptno := 20;
         
         --Pass the collection to Oracle Procedure
         test_pkg.emp_report (my_data);     
      END;
      Is it possible in front end to pass the values in a collection to Oracle Procedure?

      Thx,
      Shank.
        • 1. Re: Oracle to Java communication
          Tolls
          JDBC (this is assumign you are talking about a JDBC call to this procedure) cannot work with PL/SQL types.
          It only understands SQL types.
          So you would have to create a SQL type instead.
          • 2. Re: Oracle to Java communication
            rp0428
            >
            I'm a PL/SQL developer. I'm a rookie to Java.
            My Requirement:
            I have an Oracle procedure which has collection as input parameter. I want the array/collection to be passed from front end.

            Is it possible to pass a collection from frontend to Oracle procedure?
            >
            Yes - but as already discussed in your SQL and PL/SQL thread yesterday (Re: Oracle to Java communication for the use case you described there to pass parameters that approach is NOT recommended.

            And unfortunately this is still the wrong forum for the question. It really belongs in the JDBC (Database Connectivity) forum
            https://forums.oracle.com/forums/category.jspa?categoryID=288

            You need to defined the types as SQL types and then use the ARRAY processing functionality of JDBC.

            See Chapter 16 Working with Oracle Collections in the JDBC Developer's Guide for extensive documentation on using collections.
            http://docs.oracle.com/cd/B28359_01/java.111/b31224/oraarr.htm#BABDHBGD

            That doc doesn't have a code example but you can find them on the net. Here is a simple ARRAY example.
            http://stackoverflow.com/questions/7878735/how-to-create-an-oracle-sql-array-object

            But, again if you are working with objects that are really table data it is far simpler and more efficient to send data from Java to a table and then let your procedure process the data from there.

            And for sending record data from Oracle to Java the preferred method is to have the procedure create a REF CURSOR and then JAVA can fetch the data using the ref cursor.