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

    "invalid name pattern"  when trying to user packaged TYPE

    436586

      Hi,

      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
          Michal,
          You asked:
          Is it limitation of JDBC (Oracle 9i) not to allow using packaged types?
          Yes.

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

            Thanks in advance
            Dinesh
            • 4. Re: "invalid name pattern"  when trying to user packaged TYPE
              622920
              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?!
              Tom
              • 5. Re: "invalid name pattern"  when trying to user packaged TYPE
                627537
                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
                  644587
                  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.
                  
                  Steps:
                  
                  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
                    BEGIN
                      FOR i IN 1 .. irows
                      LOOP
                        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,
                                      "test_type.avarchartabletype");

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

                            // 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>");
                            }
                            stmt.close();
                            con.close();
                       }
                  }
                  Result:
                  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.
                  declare
                    thepage   Test_Type.aVarCharTableType;
                    irows     integer       := 10;
                    cl        clob;
                    begin
                    Test_Type.getVCTTData(thepage, irows);
                     for i in 1 .. irows
                     loop
                       cl := thepage (i);
                       dbms_output.PUT_LINE(cl);
                     end loop;
                  end;
                  Message was edited by:
                  bonjonbovi
                  • 7. Re: "invalid name pattern"  when trying to user packaged TYPE
                    user12036461
                    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
                      767651
                      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
                        830318
                        Hi,
                        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
                          997496
                          Mod: zombie resurrection removed.

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

                            Mod: locking.