PASSING RECORD TYPE PARAMETERS TO STORED PROCEDURE WITH JAVA

From: Franck <lampapetrol_at_chez.com>
Date: 3 Oct 2001 07:42:17 -0700
Message-ID: <13870eb4.0110030642.3c1c54c1@posting.google.com>

Hi all, I would like to pass a record type as an input/output parameters to a stored procedure (running on Oracle 8.16 server). I often see in newsgroups sample code passing ref cursor from jdbc to databases and what I want appears far more easier but all my tries failed...

Any ideas ?

Thanks,
Franck

###### The test package: ######

CREATE OR REPLACE PACKAGE XXHP_TEST_PKG IS         PROCEDURE get_system_item( system_item_id IN VARCHAR2, my_sys_record IN OUT XXHP_TEST_PKG.SITE_RECORD );

	TYPE SITE_RECORD IS RECORD (
		MY_STRING	VARCHAR2(20),
		MY_NUMBER	NUMBER
	);

END XXHP_TEST_PKG;
/

CREATE OR REPLACE PACKAGE BODY XXHP_TEST_PKG IS         PROCEDURE get_item( system_item_id IN VARCHAR2, my_sys_record IN OUT XXHP_TEST_PKG.SITE_RECORD ) IS

		CURSOR tmp_cur IS
			SELECT MY_STRING, MY_NUMBER FROM XXHP.XXHP_SYSTEM_ITEMS
				WHERE SYSTEM_ITEM_ID = system_item_id
					OR (MY_STRING = my_sys_record.MY_STRING AND MY_NUMBER =
my_sys_record.MY_NUMBER );
		tmp_rec	tmp_cur%ROWTYPE;
	BEGIN
		OPEN tmp_cur;
		FETCH tmp_cur INTO tmp_rec;
		IF tmp_cur%NOTFOUND THEN
			my_sys_record.MY_STRING := NULL;
			my_sys_record.MY_NUMBER := NULL;
		ELSE
			my_sys_record.MY_STRING := tmp_rec.PFM_PRM_PRODUCT_ID;
			my_sys_record.MY_NUMBER := tmp_rec.SIT_SITE_ID;
		END IF;
		CLOSE tmp_cur;
	END;

END XXHP_TEST_PKG;
/

###### The jdbc statements: #####
I tried to build a record with registerOutParameter with other datatypes but I always have a "parameters type conflict" exception

import java.util.*;
import java.io.*;
import java.sql.*;

import oracle.jdbc.driver.*;

public class Test {

	public static void main( String[] args ) {
	try {
		String url = "jdbc:oracle:thin:@testdb:1731:DEV01";
		String user = "toto";
		String password = "titi";

		Statement stmt;
		CallableStatement cstmt;
		Connection con;
		String query;
		ResultSet result;

		DriverManager.registerDriver( new oracle.jdbc.driver.OracleDriver()
);
		con = DriverManager.getConnection (url, user, password);

		/******************************************************************************************/

		//calling get_system_item( system_item_id IN VARCHAR2, my_sys_record
IN OUT XXHP_TEST_PKG.SITE_RECORD )
		query = "{CALL XXHP_TEST_PKG.GET_SYSTEM_ITEM(?,?)}";
			cstmt = con.prepareCall(query);
		cstmt.setString(1, "650");
		cstmt.registerOutParameter(2, OracleTypes.STRUCT);
		cstmt.execute();

		result = (ResultSet)cstmt.getObject(2);
		if (result.next ()) {
	 		System.out.println( result.getString("MY_STRING") + "\t" +
result.getInt("MY_NUMBER") );
	 	}
		cstmt.close();

		/******************************************************************************************/
	} catch(Exception e) {
		System.out.println("exec" + e);
	}
	}

} Received on Wed Oct 03 2001 - 09:42:17 CDT