Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

passing multiple records from java to oracle procedure

Posted on 2002-05-27
5
1,344 Views
Last Modified: 2012-06-27
i want to pass multiple records from java to oracle procedures.
how to do it
will pl/sql tables help??
if yes then how to populate pl/sql tables from java
0
Comment
Question by:shaileshpaldiwal
5 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 7037171
Program
-------

import java.sql.*;
import java.io.*;
import java.util.*;
import java.math.BigDecimal;
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class callInOutArray extends Object {

/*
   This program demonstrates how to emulate calling a stored procedure
   with PL/SQL table parameters via JDBC.  You cannot call a PL/SQL table
   parameter directly, but you can use an Object of Type Table.

   The Script used to create the procedure used in this example is as
   follows:

   create or replace type tabtype as table of varchar2(30);
   /

   create or replace package ioArray as

   procedure testproc(iotab in out tabtype,otab out tabtype);

   end ioArray;
   /

   create or replace package body ioArray as

   procedure testproc(iotab in out tabtype,otab out tabtype) is
   begin
      otab := iotab;
      if iotab.count = 0 then
         iotab.extend;
         iotab(1) := 'Record 1';
      else
         iotab.extend;
         iotab(iotab.count) := 'Second'||iotab(1);
      end if;
   end testproc;
   end ioArray;
   /
*/
   public static void main(String[] args) throws SQLException {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database

      Connection conn =
         DriverManager.getConnection ("jdbc:oracle:oci8:@S692815.WORLD",
                                   "scott", "tiger");

   //  First, declare the data arrays that will store the data.

      String [] p1obj = {"First"};
      String [] p2obj = {};

   //  Now, declare a descriptor to associate the host array type with the
   //  array type in the database.

      ArrayDescriptor desc1=ArrayDescriptor.createDescriptor("TABTYPE",conn);

   //  Create the ARRAY objects to associate the host array
   //  with the database array.

      ARRAY p1arr = new ARRAY(desc1,conn,p1obj);
      ARRAY p2arr = new ARRAY(desc1,conn,p2obj);

   //  Declare the callable statement.
   //  This must be of type OracleCallableStatement to use:
   //     setArray(ARRAY)
   //         and
   //     registerOutParameter(position,type,oracletype)

      OracleCallableStatement ocs =
         (OracleCallableStatement)conn.prepareCall("{call ioarray.testproc(?,?)}");

   //  The first parameter is in out, so you must use setArray to
   //  pass it to the statement.

      ocs.setArray(1,p1arr);

   //  The first parameter is in out, so you must Register the parameter as
   //  well.
   //  Note the reuse of the TYPE.

      ocs.registerOutParameter(1,OracleTypes.ARRAY,"TABTYPE");

  //   The second parameter is out, so that must be registered too.
  //   Note the reuse of the TYPE.

      ocs.registerOutParameter(2,OracleTypes.ARRAY,"TABTYPE");

   //  Execute the procedure

      ocs.execute();

   //  Associate the returned arrays with the ARRAY objects.

      p1arr = (ARRAY)ocs.getArray(1);
      p2arr = (ARRAY)ocs.getArray(2);

   //  Get the data back into the data arrays.

      p1obj = (String[])p1arr.getArray();
      p2obj = (String[])p2arr.getArray();

   //  Show the results:

      System.out.println("p1obj[p1obj.length-1] is " + p1obj[p1obj.length-1]);
      System.out.println("p2obj[0] is " + p2obj[0]);

   }
0
 
LVL 1

Expert Comment

by:Computer101
ID: 7037363
I have deleted the duplicate question.

Computer101
E-E Moderator
0
 

Author Comment

by:shaileshpaldiwal
ID: 7037669
this works for j2se 1.4
but what about jdk 1.3
is there any subsitute for this in jdk 1.3
0
 
LVL 5

Expert Comment

by:jpkemp
ID: 9016985
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
PAQ'ed and points refunded
Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
jpkemp
EE Cleanup Volunteer
0
 

Accepted Solution

by:
YensidMod earned 0 total points
ID: 9114455
Question is PAQ'd and points refunded.

YensidMod
Community Support Moderator @Experts Exchange
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

838 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question