JDBC, Oracle Stored Procedure and DBMS_SQL.VARCHAR_TABLE Type

JDBC, Oracle Stored Procedure and DBMS_SQL.VARCHAR_TABLE Type

Post by Jef » Sat, 08 Jun 2002 04:01:22



I have a stored procedure that I need to execute inside java and I am
not sure how to populate the DBMS_SQL.VARCHAR_TABLE type and then
execute the SP. The SP inputs are:

Class_In IN VARCHAR2,
NameList_In IN DBMS_SQL.VARCHAR2_TABLE
NameAge_In IN DBMS_SQL.NUMBER_TABLE

Any ideas on how to populate the VARCHAR2_TABLE and the NUMBER_TABLE
in java and execute the Stored Procedure?

 
 
 

JDBC, Oracle Stored Procedure and DBMS_SQL.VARCHAR_TABLE Type

Post by Peter Sylveste » Sat, 08 Jun 2002 05:02:30


Oracle has some extensions to JDBC for passing/retrieveing varray and
table types, which I would expect should work here. Note that PL/SQL
record types are not supported, but object types are. See the Oracle
JDBC Developor's Guide.

Note: If your intention is to make calls to the DBMS_SQL package, you
could certainly make life easier by just by doing it in Java, in which
all SQL is Dynamic.

-Peter


> I have a stored procedure that I need to execute inside java and I am
> not sure how to populate the DBMS_SQL.VARCHAR_TABLE type and then
> execute the SP. The SP inputs are:

> Class_In IN VARCHAR2,
> NameList_In IN DBMS_SQL.VARCHAR2_TABLE
> NameAge_In IN DBMS_SQL.NUMBER_TABLE

> Any ideas on how to populate the VARCHAR2_TABLE and the NUMBER_TABLE
> in java and execute the Stored Procedure?


 
 
 

1. How to handle return type of Table%RowType in Store Procedure by JDBC

Hi,
I Got a Function which return row of table,
is it possible to retrieve those return into a ResultSet ?

OracleCallableStatement l_stmt = (OracleCallableStatement)
connection.prepareCall("{ call ?:=GET_HOTELS(?) }");

l_stmt.registerOutParameter(4, ??? ,???);
^ ^

What I should put in above parameters???
There is no OracleType.RowType!!

======== Store Procedure Code ===============
create or replace
function get_hotelS(pi_id in number)
return hotels%rowtype
is
cursor cr_hotels is
select ID
,NAME
,LOCATION
,ADDRESS
,CTY_ID
,PHONE
,FAX
,PHOTO_FILENAME
,HOTEL_URL
,HOC_CODE
,HOTEL_AIRPORT
,ADDRESS2
,ADDRESS3
,ADDRESS4
,FINDATA_OK
from hotels
where id = pi_id;
rec_hotels hotels%rowtype;
begin
open cr_hotels;
fetch cr_hotels into rec_hotels;
if cr_hotels%notfound then
rec_hotels.ID := pi_id;
rec_hotels.name := null;
end if;
close cr_hotels;
return rec_hotels;
end;
/

2. bloated transaction log

3. JDBC Type 4 and MS SQL 2000 stored procedure problem

4. Overlapping Records

5. dynamically executing stored procedures using dbms_sql

6. Need some trigger help

7. dbms_sql and stored procedures

8. Can Transactional Repl be bi-directional?

9. Privileges of DBMS_SQL in a stored procedure

10. How to pass an integer array/userdef type array into an Oracle Stored procedure

11. Stored Procedure Data Type Problem with Oracle

12. Oracle stored procedures and TYPEs

13. Oracle, Stored Procedures, and large data types