shaileshpaldiwal
asked on
passing multiple records from java to oracle procedure
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
how to do it
will pl/sql tables help??
if yes then how to populate pl/sql tables from java
I have deleted the duplicate question.
Computer101
E-E Moderator
Computer101
E-E Moderator
ASKER
this works for j2se 1.4
but what about jdk 1.3
is there any subsitute for this in jdk 1.3
but what about jdk 1.3
is there any subsitute for this in jdk 1.3
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
-------
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.registerDriv
// Connect to the database
Connection conn =
DriverManager.getConnectio
"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.crea
// 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(posit
OracleCallableStatement ocs =
(OracleCallableStatement)c
// 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
// The second parameter is out, so that must be registered too.
// Note the reuse of the TYPE.
ocs.registerOutParameter(2
// 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[
System.out.println("p2obj[
}