Re: Calling stored proc using jdbc - TABLE OF DATE

DISCLAIMER: I work for a company that makes a product that writes JDBC Calls for stored procedures.

Ian,

>
> I created a procedure that among other types, returns TABLE OF DATE.
> When I call it using jdbc I get:
> java.sql.SQLException: Invalid PL/SQL Index Table element type
>
> If I remove TABLE OF DATE everything works. Question is: Can I execute
> proc

You could write a 'wrapper' procedure that converts the array to one of VARCHAR2 or NUMBER, both of which can be persuaded to store dates. You could then turn the contents back into a date on the client side. Currently the only data types supported for Index By tables are NUMBER and VARCHAR2. You could also load the dates into a temporary table and return them in a cursor.

If you really don't want to change your schema by adding another stored proc you could write this as an anonymous block instead. For an example of using anonymous blocks instead of conventional procedure calls in JDBC see:

http://www.orindasoft.com/public/Java2HTML/com/orindasoft/demo/generated/plsql/complexExampleFliesBetweenAll.java.html

(scroll down to the very bottom)

David Rolfe
Orinda Software
Dublin, Ireland Received on Mon Jul 19 2004 - 13:07:24 CDT