This discussion is archived
2 Replies Latest reply: Jan 30, 2013 9:58 AM by rp0428 RSS

Oracle to Java communication

SamFisher Explorer
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points