• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Oracle sql exception:

 
Ranch Hand
Posts: 40
Firefox Browser Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi I have written the following servlet program.


When I run this application, I got the following message in my server console:
java.sql.SQLException: invalid name pattern: SYSTEM.course
I am not getting what is this. I am using oracle 10g express edition and tomcat 5.5.9. I tried it with oracle 11g release2 also, but still facing the same problem. However, I used array in an standalone application in the same manner as I have used here and its working perfect, but here its not working. Please help.
 
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Moved to the JDBC forum.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Are you trying to insert several records at once? If so, it is not possible this way. You need to execute the statement repeatedly, once for every course.

If you're really trying to insert an array into a column, what is the database type of the column? The most common types certainly do not support inserting arrays into them.
 
NitishK Kumar
Ranch Hand
Posts: 40
Firefox Browser Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Martin Vajsar wrote:Are you trying to insert several records at once? If so, it is not possible this way. You need to execute the statement repeatedly, once for every course.

If you're really trying to insert an array into a column, what is the database type of the column? The most common types certainly do not support inserting arrays into them.



Why not, after all I am using prepared statement. And, as I have already mention, I tried the same with an standalone application and its working fine. My database array type is varchar2().
 
NitishK Kumar
Ranch Hand
Posts: 40
Firefox Browser Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Bear Bibeault wrote:Moved to the JDBC forum.



Sorry, by mistake I posted my query in wrong forum.
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

NitishK Kumar wrote:
Why not, after all I am using prepared statement. And, as I have already mention, I tried the same with an standalone application and its working fine. My database array type is varchar2().


Why what? What are you trying to achieve? Insert one record with array stored in a column, or insert several records? The answer depends on that.

Inserting several records: by definition INSERT ... VALUES statement creates just one record.

Inserting array: VARCHAR2 datatype cannot hold an array. In Oracle, you can create a VARRAY type and use it in a table (though I have no experience with this), but then it would not be a simple VARCHAR2 column.

Can you post the code that worked (the standalone application you mentioned), including table creation statement? What did the value looked like when you selected it back from the table using some tool (TOAD, SQL Developer)?
 
NitishK Kumar
Ranch Hand
Posts: 40
Firefox Browser Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Martin Vajsar wrote:
Why what? What are you trying to achieve? Insert one record with array stored in a column, or insert several records? The answer depends on that.

Inserting several records: by definition INSERT ... VALUES statement creates just one record.

Inserting array: VARCHAR2 datatype cannot hold an array. In Oracle, you can create a VARRAY type and use it in a table (though I have no experience with this), but then it would not be a simple VARCHAR2 column.

Can you post the code that worked (the standalone application you mentioned), including table creation statement? What did the value looked like when you selected it back from the table using some tool (TOAD, SQL Developer)?



I think there is some misunderstanding. I am inserting only one record at a time. And the array type is VARRAY of type varchar2.
Anyways I am posting my standalone application's code.
 
NitishK Kumar
Ranch Hand
Posts: 40
Firefox Browser Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


Well here's the code that you asked for. I hadn't used any tool.
I simply fired the query:
select * from stdetails1;
I had attached the output screenshot.

array type creation statement:
create type marksarray as varray(5) of number;

table creation statement:
create table stdetails1 (sr number,name varchar2(20),marks MARKSARRAY);
Filename: img.bmp
Description: output screenshot
File size: 341 Kbytes
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In that case it should work. I was under impression you're trying to insert multiple records as you print out message "Records inserted successfully" and disregarded other possibilities a bit.

I use similar technique to pass varrays into stored procedures in Oracle 10g, so yes, this should work. If you google for the error message ("java.sql.sqlexception invalid name pattern oracle"), there are some links that suggest the problem is that the name of the type in the createDescriptor call could not be resolved.

Do you have everything (table, type) in the same schema (SYSTEM)?
 
NitishK Kumar
Ranch Hand
Posts: 40
Firefox Browser Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ya, I have everything in the same schema. Earlier to this also, I applied the same methodology for two more applications. All of them worked well. Only in this one, I am facing problem. And I am not getting what is the error.
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Try to use full name (SYSTEM.COURSEARRAY) instead of COURSEARRAY in the createDescriptor call. I recall that the method has problems with unqualified names. If you need to refer to current schema without specifying its name, .COURSEARRAY (with period at the beginning) should also work.
 
NitishK Kumar
Ranch Hand
Posts: 40
Firefox Browser Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I tried it again the same problem.
But why is it giving exception saying invalid name pattern: SYSTEM.course when I am using SYSTEM.COURSEARRAY as you have suggested to me.
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Is COURSEARRAY the type, or a synonym to the type? I've found indications in some forums that the method has problems with synonyms and it might be necessary to use fully qualified real name.

Other than this, I can't think of anything. Sorry for the time you've lost with me before we got to the core of the problem.
 
NitishK Kumar
Ranch Hand
Posts: 40
Firefox Browser Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Its not so. At least you tried to help. Thanks for your cooperation. I am trying to get the solution.
 
NitishK Kumar
Ranch Hand
Posts: 40
Firefox Browser Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I solved my problem and now its working.
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

NitishK Kumar wrote:I solved my problem and now its working.


Glad you got it working. Wouldn't you mind posting your solution? It might be interesting for others should they encounter similar problems.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic