This discussion is archived
11 Replies Latest reply: Mar 18, 2013 5:14 AM by PhHein RSS

"invalid name pattern"  when trying to user packaged TYPE

436586 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    738982 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    Please don't post to years old threads.

    Mod: locking.