11 Replies Latest reply: Mar 18, 2013 7:14 AM by PhHein RSS

    "invalid name pattern"  when trying to user packaged TYPE



      I have problem with creating ArrayDescriptor instance of packaged DB TYPE.
      There is declared TYPE

         type OBJ_ARRAY_TYPE is table of number(30)

      in a DB package specification,
      when i try to create description through jdbc, it fails on "java.sql.SQLException: invalid name pattern: PKG_NAME.OBJ_ARRAY_TYPE":

         desc = ArrayDescriptor.createDescriptor ("PKG_NAME.OBJ_ARRAY_TYPE", conn);

      When OBJ_ARRAY_TYPE defined globally, outside the package, everything works fine. Is it limitation of JDBC(classes12.jar)/OracleDB9i not to allow using packaged types?
      Thx for any suggestion.

        • 1. Re: "invalid name pattern"  when trying to user packaged TYPE
          Avi Abrami
          You asked:
          Is it limitation of JDBC (Oracle 9i) not to allow using packaged types?

          Good Luck,
          • 3. Re: "invalid name pattern"  when trying to user packaged TYPE
            I too have the same problem. But I m using Oracle 10g.
            Is this problem there in oracle 10g also?

            Thanks in advance
            • 4. Re: "invalid name pattern"  when trying to user packaged TYPE
              Thought I'd add my name to this list to help give it some weight...

              I'm using Oracle 10g attempting to call stored procedures defined inside packages with types also defined in packages. I get an error from the thin driver "invalid name pattern" and "object not found" when using the oci driver.

              This is a big problem when using 3rd party developed PL/SQL application and no option to define types and procedures at the schema level.

              Any word from Oracle on this?!
              • 5. Re: "invalid name pattern"  when trying to user packaged TYPE
                Hi all,

                Could anybody solve this problem? Is there any way to create descriptors for types defined in packages?
                • 6. Re: "invalid name pattern"  when trying to user packaged TYPE
                  I have same hiccup with 10g JDBC Driver (ojdbc14.jar) and 9i Database.
                  Test Case: Create the package with the type and a procedure that use this type as OUT parameter. Create a java application to invoke this procedure and consume the type.
                  Create following package.
                  CREATE OR REPLACE PACKAGE test_type AS
                    type avarchartabletype IS TABLE OF VARCHAR2(256) INDEX BY binary_integer;
                    PROCEDURE getvcttdata(avcttdata OUT avarchartabletype,   irows IN OUT INTEGER);
                  END test_type;

                  CREATE OR REPLACE PACKAGE BODY test_type AS
                    PROCEDURE getvcttdata(avcttdata OUT avarchartabletype,   irows IN OUT INTEGER) AS
                      FOR i IN 1 .. irows
                        avcttdata(i) := 'Yo ' || i;
                      END LOOP;
                    END getvcttdata;
                  END test_type;
                  2. Try to execute the above procedure using following Java code:-
                  public class TestType {
                       public static void main(String[] args) throws Exception {
                            java.sql.DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
                            java.sql.Connection con = java.sql.DriverManager.getConnection(args[0],
                                      args[1], args[2]);

                            oracle.jdbc.driver.OracleCallableStatement stmt = (oracle.jdbc.driver.OracleCallableStatement) con
                                      .prepareCall("call Test_Type.getVCTTData(?,?)");
                             stmt.registerOutParameter(1, oracle.jdbc.driver.OracleTypes.ARRAY,

                            stmt.registerOutParameter(2, oracle.jdbc.driver.OracleTypes.INTEGER);

                            // Get the ARRAY object and print the meta data assosiated with it
                            oracle.sql.ARRAY simpleArray = stmt.getARRAY(1);
                            System.out.println("SQLTypeName=" + simpleArray.getSQLTypeName());
                            System.out.println("BaseType=" + simpleArray.getBaseType());
                            System.out.println("length=" + simpleArray.length());
                            String[] values = (String[]) simpleArray.getArray();

                            for (int i = 0; i < values.length; i++) {
                                 System.out.println("row[" + i + "]=" + values[i] + "<BR>");
                  Exception in thread "main" java.sql.SQLException: invalid name pattern: test_type.avarchartabletype
                       at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
                       at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
                       at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:463)
                       at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:362)
                       at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1756)
                       at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:272)
                       at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:196)
                       at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:165)
                       at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:150)
                       at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:115)
                       at oracle.jdbc.driver.NamedTypeAccessor.otypeFromName(NamedTypeAccessor.java:71)
                       at oracle.jdbc.driver.TypeAccessor.initMetadata(TypeAccessor.java:79)
                       at oracle.jdbc.driver.T4CCallableStatement.allocateAccessor(T4CCallableStatement.java:630)
                       at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:133)
                       at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:179)
                       at TestType.main(TestType.java:32)
                  This works fine when execute as PL/SQL using following snippet.
                    thepage   Test_Type.aVarCharTableType;
                    irows     integer       := 10;
                    cl        clob;
                    Test_Type.getVCTTData(thepage, irows);
                     for i in 1 .. irows
                       cl := thepage (i);
                     end loop;
                  Message was edited by:
                  • 7. Re: "invalid name pattern"  when trying to user packaged TYPE
                    I have had a similar problem. I managed to solve it after passing type name in upper case. In your case try AVARCHARTABLETYPE instead of avarchartabletype .
                    • 8. Re: "invalid name pattern"  when trying to user packaged TYPE
                      I had the same issue. Managed to solve it by creating public synonym and giving grants.
                      • 9. Re: "invalid name pattern"  when trying to user packaged TYPE
                        I am experiencing the same problem.
                        How did you create public synonym for types inside package?
                        By Oracle documentation: "The schema object cannot be contained in a package".

                        Thank you all.
                        • 10. Re: "invalid name pattern"  when trying to user packaged TYPE
                          Mod: zombie resurrection removed.

                          Edited by: PhHein on 18.03.2013 13:14
                          • 11. Re: "invalid name pattern"  when trying to user packaged TYPE
                            Please don't post to years old threads.

                            Mod: locking.