• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Junilu Lacar
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Knute Snortum
  • Tim Cooke
  • Devaka Cooray
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Ganesh Patekar

sqlexception: invalid name pattern: myUserName:recordTypeName

 
Ranch Hand
Posts: 77
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
 
Sheriff
Posts: 3752
62
Netbeans IDE Oracle Chrome
  • 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: 77
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
Posts: 3752
62
Netbeans IDE Oracle Chrome
  • Likes 1
  • 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.
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


Yes,its working !!!
 
What's that smell? I think this tiny ad may have stepped in something.
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!