This content has been marked as final. Show 6 replies
Unfortunately, PL/SQL table, which is the type used in the 1st argument to dbms_output.get_lines, isn't supported by JDBC at the moment.
If that's true, then I need some clarification on the JDBC documentation. Chapter 4 of the "Oracle8i JDBC Developer's Guide and Reference Release 8.1.5" (http://technet.oracle.com/doc/java.815/a64685/oraext7.htm#1040060), would seem to indicate that nested tables are supported by the Oracle JDBC drivers (at least in 8.1.5/8.1.6). Or maybe I'm not understanding the distinction between these types of tables and PL/SQL tables (I haven't done a lot of PL/SQL coding). In order for this to work, the PL/SQL procedure would have to create a database table of this data type in order for my JDBC program to access the values?
If passing and returning such data types really aren't supported by the Oracle JDBC drivers, what method does Oracle suggest as a workaround? One possibility would be to use JNI to invoke a C function with embedded SQL which would not have the JDBC limitation. However, this is not particularly appealing.
From the Collections and Records chapter of the PL/SQL documentations, it says, "Items of type TABLE are either index-by tables (Version 2 PL/SQL tables) or nested tables (which extend the functionality of index-by tables)". The current JDBC drivers does support the nested tables (your geis.chararr is one), but it doesn't support index-by tables, which is returned by get_lines. The OCI driver will have some support of index-by tables in the upcoming 8.1.7 release.
For more details, please take a look at http://technet.oracle.com/doc/server.815/a67842/04_colls.htm#24205
One work around would be to write a store procedure to wrap the output from get_lines into a nested table.
The "Supplied Packages Reference" (8.1.5) shows:
lines OUT CHARARR,
numlines IN OUT INTEGER);
CHARARR is a table of VARCHAR2(255).
It would have been helpful if it stated that CHARARR is an index-by table and that you had to define CHARARR itself. Why doesn't dbms_output define the CHARARR type?
Is there a known release date for the 8.1.7 driver?
As suggested, I'm trying to convert the index by table returned by get_lines() to a nested table. Here is my code:
create or replace type chararr is table of varchar2(255);
create or replace
(lines OUT chararr, linecnt IN OUT INTEGER) AS
type ib_chararr is table of varchar2(255) index by binary_integer;
However, I'm getting "wrong number or types of arguments in call to 'GET_LINES'. I'm passing an index-by for parm and an integer for parm2 which should be correct. What am I missing?
Ignore my previous post about wrong number or types of parameters, I have fixed the problem...