9 Replies Latest reply: Mar 20, 2007 1:47 PM by 568051 RSS

    Using PLSQL Indexed Tables 9i

    280391
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          What are the jars I need to solve this problem?
                          Thanks,
                          Melina Alberio(melinaalberio@yahoo.com)