Hi Riccardo,
This is what I have used in my project.Kindly see if this helps.
regards,
Amit
=============================================================================
=============================
This is a sample program which sends an array of objects to the stored proc.
----------------------------------------------------------------------------
import java.sql.*;
import java.io.*;
import java.util.*;
import java.math.BigDecimal;
import oracle.sql.*;
import oracle.jdbc.driver.*;
/*
CREATE OR REPLACE TYPE RAWREC AS OBJECT(
p_registrant_id NUMBER(10),
p_survey_id NUMBER(10),
p_question_id NUMBER(10),
p_response_id NUMBER(10),
p_response_type_cd VARCHAR2(10),
p_vendor_cd VARCHAR2(10),
p_client_column_name VARCHAR2(40),
p_column_name VARCHAR2(40),
p_column_id NUMBER(10),
p_column_sort_order NUMBER(4),
p_source_cd VARCHAR2(10),
p_data_type_cd VARCHAR2(10),
p_lang_cd VARCHAR2(10),
p_length NUMBER(4),
p_value VARCHAR2(2000)
)
/
CREATE OR REPLACE TYPE RAWTAB AS TABLE OF RAWREC
/
CREATE OR REPLACE PACKAGE PKGRAW
AS
PROCEDURE rawdata ( p_rawtab IN rawtab);
END pkgraw;
/
CREATE OR REPLACE PACKAGE BODY PKGRAW
AS
PROCEDURE rawdata (p_rawtab IN rawtab)
IS
BEGIN
for i in 1..p_rawtab.count loop
INSERT INTO RAW_DATA
(REGISTRANT_ID,
SURVEY_ID,
QUESTION_ID,
RESPONSE_ID,
RESPONSE_TYPE_CD,
VENDOR_CD,
CLIENT_COLUMN_NAME,
COLUMN_NAME,
COLUMN_ID,
COLUMN_SORT_ORDER,
SOURCE_CD,
DATA_TYPE_CD,
LANG_CD,
LENGTH,
VALUE,
CREATED_DT)
VALUES
(p_rawtab(i).p_registrant_id,
p_rawtab(i).p_survey_id,
p_rawtab(i).p_question_id,
p_rawtab(i).p_response_id,
p_rawtab(i).p_response_type_cd,
p_rawtab(i).p_vendor_cd,
p_rawtab(i).p_client_column_name,
p_rawtab(i).p_column_name,
p_rawtab(i).p_column_id,
p_rawtab(i).p_column_sort_order,
p_rawtab(i).p_source_cd,
p_rawtab(i).p_data_type_cd,
p_rawtab(i).p_lang_cd,
p_rawtab(i).p_length,
p_rawtab(i).p_value,
SYSDATE);
end loop;
EXCEPTION
WHEN OTHERS THEN
RAISE;
COMMIT;
END rawdata;
END pkgraw;
*/
public class forfill extends Object {
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:@reg",
"webreg2k", "webreg2k");
// First, declare the Object arrays that will store the data.
Object [] p1recobj =
{"10000","10000","10000","10000","TEST","TEST","TEST","TEST","3","3","TEST","
TEST","ENU","3","TEST"};
Object [] p2recobj =
{"10001","10000","10000","10000","TEST","TEST","TEST","TEST","3","3","TEST","
TEST","ENU","3","TEST"
};
// 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("RAWREC",conn);
ArrayDescriptor
desc2=ArrayDescriptor.createDescriptor("RAWTAB",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
pkgraw.rawdata(?)}");
// The first parameter is in out so we have to use setARRAY to
// pass it to the statement
ocs.setARRAY(1,p1arr);
// Execute the procedure
ocs.execute();
}
}
----------------------------------------------------------------------------
=============================================================================
=======================================
-----Original Message-----
From: Riccardo F. [mailto:[EMAIL PROTECTED]]
Sent: Thursday, February 22, 2001 11:47 PM
To: [EMAIL PROTECTED]
Subject: Handling SQL ARRAY within Java
Hello,
I'd like to know how to pass an ARRAY as input parameter to a stored function
from java code.
If someone knows if are present some examples about such question on line,
please inform me!
(OracleCallableStatement) conn.prepareCall("begin :1 := GENERAL.PROVA;
end;");
How can i pass the ARRAY to function? ARRAY and ArrayDescriptor has been
created correctly ...
Thanks in advance for any advice!
Riccardo.
----------------------------------------------
Virgilio Mail - Il tuo indirizzo E-mail gratis
http://mail.virgilio.it
===========================================================================
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff EJB-INTEREST". For general help, send email to
[EMAIL PROTECTED] and include in the body of the message "help".
===========================================================================
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff EJB-INTEREST". For general help, send email to
[EMAIL PROTECTED] and include in the body of the message "help".
