This week's book giveaways are in the Refactoring and Agile forums.
We're giving away four copies each of Re-engineering Legacy Software and Docker in Action and have the authors on-line!
See this thread and this one for details.
Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

sqlexception: invalid name pattern: myUserName:recordTypeName

 
Mohamed Iqzas
Ranch Hand
Posts: 72
Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 3751
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 72
Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 3751
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic