Home » Developer & Programmer » JDeveloper, Java & XML » Retrieving Custom Oracle Types ala Java JDBC (Oracle 10g)
Retrieving Custom Oracle Types ala Java JDBC [message #449088] Fri, 26 March 2010 10:04 Go to next message
lutherbaker
Messages: 2
Registered: March 2010
Junior Member
I am a Java developer working with an Oracle JDBC connection. Given this Oracle type definition:

create or replace TYPE integer_array_type AS TABLE OF INTEGER;


How do I invoke the following Function:

create or replace PACKAGE BODY customer AS
    FUNCTION getall(p_Acct_Number INTEGER_ARRAY_TYPE, p_Site_Id INTEGER_ARRAY_TYPE) RETURN FULL_CUSTOMER_TYPE_T_T


In other words, I'm not sure how to create an INTEGER_ARRAY_TYPE on the Java side. For simpler argument types, I've got the following working:

final String func = "{? = call sdm.get_work_order_key(?,?)}";
final OracleCallableStatement call = (OracleCallableStatement) conn.prepareCall(func);
call.setInt(2, workOrderNumber);
call.setInt(3, siteId);
call.registerOutParameter(1, Types.INTEGER);
call.executeUpdate();


but in this simpler case, everything is an INTEGER. For what it is worth, I will also eventually need to apply this answer to the out parameter which is recursively defined as:

create or replace TYPE full_customer_type_t_t AS TABLE OF full_customer_type_t;

create or replace TYPE full_customer_type_t AS TABLE OF full_customer_type;

create or replace TYPE full_customer_type AS OBJECT
(
    --
    -- Customer Master
    --
    CUSTOMER_KEY                NUMBER(38),
    BILL_SYS_SITE_ID            NUMBER(3),
    ACCT_NO                     NUMBER(9),
    TITLE                       VARCHAR2(4),
    FIRST_NAME                  VARCHAR2(10),

   ... etc

);


I assume I need to setup some type of mapping and at first glance, the JDBC provides what looks to be a framework around an interface known as SQLData but I'm not just not sure if this is the correct approach - and looking for some examples of it. I'm just not sure how to map a list of Java collection to this Oracle defined TABLE OF INTEGER ... etc.

-Luther

[Updated on: Fri, 26 March 2010 10:26]

Report message to a moderator

Re: Retrieving Custom Oracle Types ala Java JDBC [message #449098 is a reply to message #449088] Fri, 26 March 2010 11:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
JDBC Developer's Guide and Reference
Chapter 18 Working with Oracle Collections


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand
Re: Retrieving Custom Oracle Types ala Java JDBC [message #449111 is a reply to message #449098] Fri, 26 March 2010 13:29 Go to previous message
lutherbaker
Messages: 2
Registered: March 2010
Junior Member
Got it - and got it working.

Thanks very much. Great link.
Previous Topic: JSF right to left
Next Topic: insert command
Goto Forum:
  


Current Time: Fri Mar 29 09:07:38 CDT 2024