4 Replies Latest reply on Mar 10, 2008 7:57 PM by 625432

    Invoking a PL/SQL function

    625432
      Hi,

      I am trying to invoke a PL/SQL function and am getting the following exception:

      Exception in thread "main" java.sql.SQLException: ORA-06550: line 1, column 7:
      PLS-00221: 'SA_GET_COL_DATATYPE' is not a procedure or is undefined
      ORA-06550: line 1, column 7:
      PL/SQL: Statement ignored

      Here is the java code:
           String driverName = "oracle.jdbc.driver.OracleDriver";
           String tableName = "Foo";
           String url = "jdbc:oracle:thin:@sjcfsdb8:1521:irnpdev3";
           String pass = "xyz";
           String id = "xyz";
           Class.forName(driverName).newInstance();
           java.sql.Connection conn = java.sql.DriverManager.getConnection(url,id,pass);
           System.out.println("----------Created Connection-------------");
           String spCall = "{ call SA_GET_COL_DATATYPE(?) }";
           java.sql.CallableStatement cstmt = conn.prepareCall(spCall);;
           java.sql.ResultSet result;
           cstmt.setString(1, tableName);
           
      result = cstmt.executeQuery();

      The function returns a cursor and compiles fine.
      Any suggestions?
      Thanks,
      Horea
        • 1. Re: Invoking a PL/SQL function
          JustinCave
          Are you sure the stored procedure is in the XYZ schema (or whatever user you're connecting as)? Can you describe the function in SQL*Plus (i.e. desc SA_GET_COL_DATATYPE)?

          Justin
          • 2. Re: Invoking a PL/SQL function
            625432
            Hi Justin,

            Thanks for the comment. I am afraid it is not an access issue as I can invoke a different function that returns an integer. The issue is related to the way the returned cursor is defined/handled. The code is as per the Sun documentation (I believe); and it works fine for Sybase.
            I am using the oc4j 10133 jdbc driver library.
            Cheers,
            Horea
            • 3. Re: Invoking a PL/SQL function
              JustinCave
              The "different function" that you are able to call, though, may be in the XYZ schema. Or it may be that there is a synonym for the function that you can call that doesn't exist for this function. Or it may be that you don't have the same privileges on this function than you do on the function that you can call.

              The problem may also be with the definition of the function. Again, if you can describe the function within SQL*Plus, or provide the function signature, we may be able to provide more help. Does the function return a REF CURSOR, for example?

              Justin
              • 4. Re: Invoking a PL/SQL function
                625432
                Hi,

                Sorry for the late reply. There was no issue with permissions. It turns out Oracle does not support the calling format mentioned. So much for the JDBC standard...

                Here is how it works:

                     String spCall = "{ call ? := SA_GET_COL_DATATYPE(?) }";
                     java.sql.CallableStatement cstmt = conn.prepareCall(spCall);
                     
                     cstmt.registerOutParameter(1, OracleTypes.CURSOR);     
                     cstmt.setString(2, tableName);

                     cstmt.execute();
                     result = (ResultSet) cstmt.getObject(1);

                Cheers,
                Horea