File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Oracle/OAS and the fly likes sqlexception: invalid name pattern: myUserName:recordTypeName Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "sqlexception: invalid name pattern: myUserName:recordTypeName" Watch "sqlexception: invalid name pattern: myUserName:recordTypeName" New topic
Author

sqlexception: invalid name pattern: myUserName:recordTypeName

Mohamed Iqzas
Ranch Hand

Joined: Jan 02, 2012
Posts: 53

This is the java code i use:



I'm getting error in this line:



In Oracle Db, i've something like this:
....

TYPE recordTypeName IS RECORD (firstVar varchar2(50),secondVar varchar2(50),aNumber number,thirdVar varchar2(50));

TYPE TableOfRecordTypeName IS TABLE OF recordTypeName INDEX BY BINARY_INTEGER;

.....

Why I'm getting error in naming pattern like this? dbuserName:recordTypeName .. I have no idea what this is about? I'm just trying the code extracted from some website. I want to pass in a table of records as an argument to a PL\SQL function from JAVA...

Please help me..

below is the stacktrace:

java.sql.SQLException: invalid name pattern: myUserName:recordTypeName
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:527)
at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:407)
at oracle.sql.StructDescriptor.initPickler(StructDescriptor.java:249)
at oracle.sql.StructDescriptor.<init>(StructDescriptor.java:204)
at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:138)
at Utilities.DBArrayType2.main(DBArrayType2.java:39)... -----------DBArrayType2 is the class I use
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3436
    
  47

Please edit your post and put the code into code tags. It will be much more readable.

This page contains advice on how to use collections of Oracle objects in Java. That page also contains links to relevant parts of Oracle documentations you really should read, and also a link to a thread in which one of our fellow ranchers successfully implemented such functionality.
Mohamed Iqzas
Ranch Hand

Joined: Jan 02, 2012
Posts: 53

Thanks Martin.

That linked really helped but not for a complete solution on how to pass PL\SQL table of records arguments from JAVA.

But my question in this thread is resolved.

Tips that helped me: (Might not be helpful for everyone)

- the pl\sql type was defined inside a package. While creating the Struct Descriptor if the type name is not prefixed with the packageName using a dot( . ), then the error throws with DB USerName Prefixed with typeName. If I use the package prefix while creating descriptor, then also i get the same exception but correctly with package name prefix instead of username in the stack trace.

- Now if the type is declared globally in the DB we connect, i.e., outside the package, then i'm able to create the struct or Array descriptor without any exceptions. Also note that the type name used for creating descriptor in JAVA code should be in Upper Case no matter how it is defined in PL\SQL statements(thats how it worked for me). Also use the schema prefix if any.

Here I have used Struct descriptor for the PL\SQL type declared as RECORD, and an ArrayDescriptor for the PL\SQL type TABLE OF that record type. Is this the correct approach? Will that work?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3436
    
  47

Mohamed Iqzas wrote:Also note that the type name used for creating descriptor in JAVA code should be in Upper Case no matter how it is defined in PL\SQL statements(thats how it worked for me). Also use the schema prefix if any.

Actually, all SQL identifiers (tables, coumns, packages, types and so on) are converted to upper case by Oracle, unless you enclose them in double quotes. When you use double quotes to create a mixed-case or lower-case identifier, you need to always specify it that way (ie. in double quotes) everywhere - in select queries and so on, so it is very rarely done.

When registering the type from Java, the conversion to upper case is apparently not done. I'll add that to the HowTo page when I get some time, or you an add it there yourself (there is an Edit button on the page).

Did you already have a look at this thread referenced from the HowTo page? There are also links directly to relevant chapters of Oracle documentation there (if all else fails ).

I'm not sure I'll be able to help, I haven't ever actually tried to pass tables of structures from Java to PL/SQL, but perhaps I'll be able to spot something if you post both the SQL code and Java code here.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: sqlexception: invalid name pattern: myUserName:recordTypeName
 
Similar Threads
invalid name patter error
invalid name pattern
Passing Array as a parameter to Stored procedures
How to pass java objects to PL/SQL procedures?
calling stored function