Need Expert Advice

[bobby, morkos]

I have this code below, I have to call it many times:
like: SHOW_PEG("Hiton", "5454", "14574");
is it possible to send a table of inputs
SHOW_PEG(Hotel[], dataId[],, recordId[]);
Because, for now I use the code below and I have to call it many times. I want to be able to send a result table and process all the queries within my oracle program.

create or replace package pegtypes IS
type ref_cursor is ref cursor;
end;
/
CREATE OR REPLACE Function SHOW_PEG(v_owner_id IN varchar, v_property_code IN varchar, v_chain_code in varchar)
RETURN pegtypes.ref_cursor
IS user_cursor pegtypes.ref_cursor;
BEGIN
OPEN user_cursor FOR
SELECT peg_id
FROM peg
WHERE owner_id = v_owner_id
AND property_code = v_property_code
AND chain_code = v_chain_code;
RETURN user_cursor;
END;
/

[Beksy Kurian]

Try this sample by Oracle, Bobby.

 Program
-------

import java.sql.*;
import java.io.*;
import java.util.*;
import java.math.BigDecimal;
import oracle.sql.*;
import oracle.jdbc.driver.*;
public class callInOutStructArray extends Object {
/*
   This program demonstrates how to emulate calling a stored procedure
   with PL/SQL table of record parameters via JDBC.  You cannot call a 
   PL/SQL table of records parameter directly, however, you can use an 
   Object with the same structure as your table of records.
   The Script used to create the procedure in this example is as follows:
   drop type rectab;
   create or replace type rectype as object(col1 varchar2(10),col2 varchar2(10));
   /
   create or replace type rectab as table of rectype;
   /
   create or replace package ioStructArray as

   procedure testproc(iorec in out rectab,orec out rectab);
   end ioStructArray;
   /
   create or replace package body ioStructArray as
 
   procedure testproc(iorec in out rectab,orec out rectab) is
   begin
      orec := iorec;
      for i in 1..iorec.count loop
         iorec(i).col1 := orec(i).col2;
         iorec(i).col2 := orec(i).col1;
      end loop;
   end testproc;
   end ioStructArray;
   /

*/
   public static void main(String[] args) throws SQLException {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    // Connect to the database
      Connection conn =
         DriverManager.getConnection ("jdbc :-o	 racle :-o	 ci8:@S692815.WORLD",
                                   "scott", "tiger");
   //  First, declare the Object arrays that will store the data.
      Object [] p1recobj = {"FIRST","LAST"};
      Object [] p2recobj = {"SECOND","LAST"};
      Object [] p3recobj;
      Object [] p4recobj;
   //  Declare the Object Arrays to hold the STRUCTS.
      Object [] p1arrobj;
      Object [] p2arrobj;
   //  Declare two descriptors, one for the ARRAY TYPE
   //  and one for the OBJECT TYPE.
      StructDescriptor desc1=StructDescriptor.createDescriptor("RECTYPE",conn);
      ArrayDescriptor desc2=ArrayDescriptor.createDescriptor("RECTAB",conn);
   //  Create the STRUCT objects to associate the host objects
   //  with the database records.

      STRUCT p1struct = new STRUCT(desc1,conn,p1recobj);
      STRUCT p2struct = new STRUCT(desc1,conn,p2recobj);
   //  Initialize the Input array object - to an array of STRUCT Objects.
      p1arrobj = new Object []{p1struct,p2struct};
   //  Set up the ARRAY object.
      ARRAY  p1arr = new ARRAY(desc2,conn,p1arrobj);
      ARRAY  p2arr;
   //  Declare the callable statement.
   //  This must be of type OracleCallableStatement.
      OracleCallableStatement ocs =
         (OracleCallableStatement)conn.prepareCall("{call iostructarray.testproc(?,?)}");
   //  The first parameter is in out so we have to use setARRAY to
   //  pass it to the statement
      ocs.setARRAY(1,p1arr);
   //  The first parameter is in out, so we have to Register the 
   //  parameter as well.
   //  Note the re use of the TYPE.
      ocs.registerOutParameter(1,OracleTypes.ARRAY,"RECTAB");
  //   The second paramter is out, so that has to be registered too.
  //   Note the re use of the TYPE.
      ocs.registerOutParameter(2,OracleTypes.ARRAY,"RECTAB");
   //  Execute the procedure
      ocs.execute();
   //  Associate the returned arrays with the ARRAY objects.
      p1arr = ocs.getARRAY(1);
      p2arr = ocs.getARRAY(2);
   //  Get the data back into the data arrays.
      p1arrobj = (Object [])p1arr.getArray();
      p2arrobj = (Object [])p2arr.getArray();
   //  Get the data records from each array element (which is of type STRUCT).
      p1recobj = ((STRUCT)p1arrobj[0]).getAttributes();
      p2recobj = ((STRUCT)p1arrobj[1]).getAttributes();
      p3recobj = ((STRUCT)p2arrobj[0]).getAttributes();
      p4recobj = ((STRUCT)p2arrobj[1]).getAttributes();
   //  Show the results:
      System.out.println("First Object is now "+p1recobj[0]+" and "+p1recobj[1]);
      System.out.println("                    "+p2recobj[0]+" and "+p2recobj[1]);
      System.out.println("Second Object is now "+p3recobj[0]+" and "+p3recobj[1]);
      System.out.println("                    "+p4recobj[0]+" and "+p4recobj[1]);
   }
}
 

Regards
Beksy

[bobby, morkos]

Can you help me in modifying my code above. I can then use it as a reference. I hope you can help, to get my example working.

Originally posted by Beksy Kurian:
Try this sample by Oracle, Bobby.

 Program
-------

import java.sql.*;
import java.io.*;
import java.util.*;
import java.math.BigDecimal;
import oracle.sql.*;
import oracle.jdbc.driver.*;
public class callInOutStructArray extends Object {
/*
   This program demonstrates how to emulate calling a stored procedure
   with PL/SQL table of record parameters via JDBC.  You cannot call a 
   PL/SQL table of records parameter directly, however, you can use an 
   Object with the same structure as your table of records.
   The Script used to create the procedure in this example is as follows:
   drop type rectab;
   create or replace type rectype as object(col1 varchar2(10),col2 varchar2(10));
   /
   create or replace type rectab as table of rectype;
   /
   create or replace package ioStructArray as

   procedure testproc(iorec in out rectab,orec out rectab);
   end ioStructArray;
   /
   create or replace package body ioStructArray as
 
   procedure testproc(iorec in out rectab,orec out rectab) is
   begin
      orec := iorec;
      for i in 1..iorec.count loop
         iorec(i).col1 := orec(i).col2;
         iorec(i).col2 := orec(i).col1;
      end loop;
   end testproc;
   end ioStructArray;
   /

*/
   public static void main(String[] args) throws SQLException {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    // Connect to the database
      Connection conn =
         DriverManager.getConnection ("jdbc  :-o	  racle  :-o	  ci8:@S692815.WORLD",
                                   "scott", "tiger");
   //  First, declare the Object arrays that will store the data.
      Object [] p1recobj = {"FIRST","LAST"};
      Object [] p2recobj = {"SECOND","LAST"};
      Object [] p3recobj;
      Object [] p4recobj;
   //  Declare the Object Arrays to hold the STRUCTS.
      Object [] p1arrobj;
      Object [] p2arrobj;
   //  Declare two descriptors, one for the ARRAY TYPE
   //  and one for the OBJECT TYPE.
      StructDescriptor desc1=StructDescriptor.createDescriptor("RECTYPE",conn);
      ArrayDescriptor desc2=ArrayDescriptor.createDescriptor("RECTAB",conn);
   //  Create the STRUCT objects to associate the host objects
   //  with the database records.

      STRUCT p1struct = new STRUCT(desc1,conn,p1recobj);
      STRUCT p2struct = new STRUCT(desc1,conn,p2recobj);
   //  Initialize the Input array object - to an array of STRUCT Objects.
      p1arrobj = new Object []{p1struct,p2struct};
   //  Set up the ARRAY object.
      ARRAY  p1arr = new ARRAY(desc2,conn,p1arrobj);
      ARRAY  p2arr;
   //  Declare the callable statement.
   //  This must be of type OracleCallableStatement.
      OracleCallableStatement ocs =
         (OracleCallableStatement)conn.prepareCall("{call iostructarray.testproc(?,?)}");
   //  The first parameter is in out so we have to use setARRAY to
   //  pass it to the statement
      ocs.setARRAY(1,p1arr);
   //  The first parameter is in out, so we have to Register the 
   //  parameter as well.
   //  Note the re use of the TYPE.
      ocs.registerOutParameter(1,OracleTypes.ARRAY,"RECTAB");
  //   The second paramter is out, so that has to be registered too.
  //   Note the re use of the TYPE.
      ocs.registerOutParameter(2,OracleTypes.ARRAY,"RECTAB");
   //  Execute the procedure
      ocs.execute();
   //  Associate the returned arrays with the ARRAY objects.
      p1arr = ocs.getARRAY(1);
      p2arr = ocs.getARRAY(2);
   //  Get the data back into the data arrays.
      p1arrobj = (Object [])p1arr.getArray();
      p2arrobj = (Object [])p2arr.getArray();
   //  Get the data records from each array element (which is of type STRUCT).
      p1recobj = ((STRUCT)p1arrobj[0]).getAttributes();
      p2recobj = ((STRUCT)p1arrobj[1]).getAttributes();
      p3recobj = ((STRUCT)p2arrobj[0]).getAttributes();
      p4recobj = ((STRUCT)p2arrobj[1]).getAttributes();
   //  Show the results:
      System.out.println("First Object is now "+p1recobj[0]+" and "+p1recobj[1]);
      System.out.println("                    "+p2recobj[0]+" and "+p2recobj[1]);
      System.out.println("Second Object is now "+p3recobj[0]+" and "+p3recobj[1]);
      System.out.println("                    "+p4recobj[0]+" and "+p4recobj[1]);
   }
}
 

Regards
Beksy

[bobby, morkos]

Originally posted by bobby, morkos:
[QB]Can you help me in modifying my code above. I can then use it as a reference. I hope you can help, to get my example working.

So, let me get it right, your saying that it is possible to send an array into a procedure and go through the array in that same procedure. What I have is I want to retrieve many record set and return it to my java program without going back and forth. Your code that you showed me is hard to understand. What I have is the following:
In my java program:
// Calling the Oracle procedure
String query = "begin :1 := SHOW_PEGASUS('"+ ownerId + "', '"+ propertyCode
+ "', '"+ chainCode + "'); end;";
CallableStatement cs = _con.prepareCall(query);
cs.registerOutParameter(1,OracleTypes.CURSOR);
cs.execute();
_rs=(ResultSet)cs.getObject(1);
And ownerId, property Code and chain code is a collection (An array that I loop through it).
Now, my procedure is as the following:

create or replace package pegasustypes IS
type ref_cursor is ref cursor;
end;
/
CREATE OR REPLACE Function SHOW_PEGASUS(v_owner_id IN varchar, v_property_code IN varchar, v_chain_code in varchar)
RETURN pegasustypes.ref_cursor
IS user_cursor pegasustypes.ref_cursor;
BEGIN
OPEN user_cursor FOR
SELECT pegasus_id
FROM pegasus
WHERE owner_id = v_owner_id
AND property_code = v_property_code
AND chain_code = v_chain_code;
RETURN user_cursor;
END;
/

I hope you understand what I'm trying to do. And if you could show me how to modify my code in order to function, it would really help me. I just need this as a reference. I hope you can help me and I really appreciate any help. Thanks.

[bobby, morkos]

Your a life savier. I just want my code to work so that I can use it as a reference. Thanks for taking the time to help.

Originally posted by Beksy Kurian:
let me test the code and I will get back to you soon.
Beksy

[Beksy Kurian]

Sorry Bobby! I did not get enough time to test at work. I can give you some pointers though. This code may have errors!!!Try it out!
oracle.sql.STRUCT is the most basic method of calling objects.

in Java
--------  
      Object [] p1obj = {"First","Second","Third"};
      Object [] p2obj = {};
     
      StructDescriptor desc1=StructDescriptor.createDescriptor("RECTYPE",conn);
      STRUCT p1struct = new STRUCT(desc1,conn,p1obj);
      STRUCT p2struct;   
      OracleCallableStatement ocs =
         (OracleCallableStatement)conn.prepareCall("{call pegasustypes.show_pegasus(?,?)}");
      ocs.setOracleObject(1,p1struct);   
      ocs.registerOutParameter(1,OracleTypes.STRUCT,"RECTYPE");
      ocs.registerOutParameter(2,OracleTypes.STRUCT,"RECTYPE");   
      ocs.execute();
      p1struct = ocs.getSTRUCT(1);
      p2struct = ocs.getSTRUCT(2);
      p1obj = p1struct.getAttributes();
      p2obj = p2struct.getAttributes();        

and in PL/SQL
-------------
 create or replace type rectype as object(v_owner_id NUMBER,v_property_code NUMBER,v_chain_code NUMBER);
 /
 create or replace package pegasustypes as
 
   procedure show_pegasus(iorec in out rectype,orec out rectype);
 
 end pegasustypes;
 /

 create or replace package body pegasustypes as
  
 procedure show_pegasus(iorec in out rectype,orec out rectype) is
   begin   
    ------do the array manipulation here-----
   End show_pegasus;
 End pegasustypes;
 /
  

Beksy