This discussion is archived
9 Replies Latest reply: Mar 20, 2007 11:47 AM by 568051 RSS

Using PLSQL Indexed Tables 9i

280391 Newbie
Currently Being Moderated
I am tring to return a PLSQL Index table from a stored procedure. I use the latest classes12.zip for Oracle 9i 9.0.3

By call
ostmnt.registerIndexTableOutParameter
(3,maxTablLen,OracleTypes.INTEGER,0);

i gett the following error message:
java.sql.SQLException: Unsupported feature

Does "PLSQL Index By Tables Support Sample" [04-Mar-2003]
(http://otn.oracle.com/sample_code/tech/java/sqlj_jdbc/files/9i_jdbc/PlsqlTablesSample/Readme.html)
work ??
  • 1. Re: Using PLSQL Indexed Tables 9i
    280391 Newbie
    Currently Being Moderated
    The Sample "PLSQL Index By Tables Support Sample [04-Mar-2003]" CAN NOT WORK !!

    Oracle classes12.zip JDBC driver does NOT implement registerIndexTableOutParameter feature. OracleLog points it out:

    DRVR FUNC OracleCallableStatement.registerIndexTableOutParameter(paramIndex=3, maxLen=100, elemSqlType=4, elemMaxLength=0)

    DBAC FUNC DBDataSet.checkPlsqlIndexTableBindTypes(dbset2=oracle.jdbc.dbaccess.DBDataSetImpl@209d0889, index=2, maxLen=100, elemMaxLen=0, elemSqlType=6): this method should be overriden at Subclass. returned -1 here

    The oracle.jdbc.dbaccess.DBSetImpl class (which implements
    the abstract DBSet) does NOT overides the checkPlsqlIndexTableBindTypes method.

    I hope, i am wrong and there is a workaround for this problem
  • 2. Re: Using PLSQL Indexed Tables 9i
    3330 Newbie
    Currently Being Moderated
    Hello,
    Please provide the following details.

    1) What is the version of the database you are using?
    2) If the database is not installed on the client where the sample is running, then please provide the version of your sqlplus client.
    3) What is the JDK Version?

    It seems to me that there is a mismatch between the libraries on your client system and the database.

    Regards.
  • 3. Re: Using PLSQL Indexed Tables 9i
    280391 Newbie
    Currently Being Moderated
    Hello ,

    1)Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
    2)The the database is installed on local
    3)
    Java(TM) 2 Runtime Environment, Standard Edition (build 1.3.0)
    Classic VM (build 1.3.0, J2RE 1.3.0 IBM build cn130-20010502 (JIT enabled: jitc)
    )
    4)The error occurrs with all Oracle9i 9.0.1.4,9.2.0.3 and 9.2.0.1 drivers (classes12.zip, classes12_g.zip)
  • 4. Re: Using PLSQL Indexed Tables 9i
    3330 Newbie
    Currently Being Moderated
    Hello,
    I am not able to reproduce your problem here.

    On my system, I have a 9.2.0.1 client installed and am using the JDBC libraries from <ORACLE_HOME>\jdbc\lib folder. The sample is working perfectly on this without any error. My OS is Windows 2000.

    Probably, the problem is due to the JDK version. You are using an IBM build. Please use the JDK downloaded from Sun and see if the problem resurfaces.

    A new version of this sample is uploaded at http://otn.oracle.com/sample_code/tech/java/sqlj_jdbc/files/oracle10g/plsqltables/Readme.html. This sample also demonstrates the same feature in Oracle10g and uses the thin driver to connect to the database. You might check out this sample too.

    Hope this helps.
  • 5. Re: Using PLSQL Indexed Tables 9i
    280391 Newbie
    Currently Being Moderated
    I have tried
    1. Sun JDK 1.4
    Java(TM) 2 Runtime Environment, Standard Edition
    (build 1.4. Java HotSpot(TM) Client VM (build
    1.4.0_03-b04, mixed mode)

    2.Oracle JDK (<ORACLE_HOME>\jdk\bin)
    Java(TM) 2 Runtime Environment, Standard Edition
    (build 1 Java HotSpot(TM) Client VM (build 1.3.1_01,
    mixed mode)

    Unfortunately, the error
    java.sql.SQLException: Unsupported feature
    still persists.

    If you turns Oracle logging on
    oracle.jdbc.driver.OracleLog.startLogging();
    oracle.jdbc.driver.OracleLog.setLogVolume(3);
    ,does the call
    oracle.jdbc.dbaccess.DBDataSetImpl.checkPlsqlIndexTableBindTypes
    appear in the output ?

  • 6. Re: Using PLSQL Indexed Tables 9i
    3330 Newbie
    Currently Being Moderated
    Hi,
    Did you try the other version of the sample that was provided? Maybe there is a problem with OCI and this is the reason the sample is not working. The other version uses thin driver to connect to the database.

    As I said above, I have'nt been able to reproduce your problem in my environment. I would also suggest you to ask your question in the database forum. Please mention the feature you are trying to use and the version of your database. Your problem seems to be with the environment and probably the database gurus might have more information on that.

    Hope this helps.
  • 7. Re: Using PLSQL Indexed Tables 9i
    280391 Newbie
    Currently Being Moderated
    Hi,

    i have questioned in the JDBC Forum.
    Using PLSQL Index Tables (urgent)..

    The short answer for my problem: "I was wrong"

    All the time i have tried to use the "thin" JDBC driver, not the OCI. The sample works with the OCI (JDBC) driver only.
  • 8. Re: Using PLSQL Indexed Tables 9i
    3330 Newbie
    Currently Being Moderated
    Hi,
    I am glad you could solve the problem.

    Yes. As I had mentioned previously in my post, the sample you are referring to works with OCI Driver.

    For a similar sample app working on the thin driver, please see the other sample link that I had posted. This other sample also demonstrates the same feature but uses a thin driver for connecting to the database. For more information, please have a look at http://otn.oracle.com/sample_code/tech/java/sqlj_jdbc/files/oracle10g/plsqltables/Readme.html.

    Regards.
  • 9. Re: Using PLSQL Indexed Tables 9i
    568051 Newbie
    Currently Being Moderated
    What are the jars I need to solve this problem?
    Thanks,
    Melina Alberio(melinaalberio@yahoo.com)