GeeCON Prague 2014*
The moose likes JDBC and the fly likes Oracle sql exception: Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Oracle sql exception: " Watch "Oracle sql exception: " New topic
Author

Oracle sql exception:

NitishK Kumar
Ranch Hand

Joined: Oct 20, 2010
Posts: 40

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.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61305
    
  66

Moved to the JDBC forum.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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

Joined: Oct 20, 2010
Posts: 40

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

Joined: Oct 20, 2010
Posts: 40

Bear Bibeault wrote:Moved to the JDBC forum.


Sorry, by mistake I posted my query in wrong forum.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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

Joined: Oct 20, 2010
Posts: 40

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

Joined: Oct 20, 2010
Posts: 40



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);


[Download img.bmp] Download

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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

Joined: Oct 20, 2010
Posts: 40

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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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

Joined: Oct 20, 2010
Posts: 40

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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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

Joined: Oct 20, 2010
Posts: 40

Its not so. At least you tried to help. Thanks for your cooperation. I am trying to get the solution.
NitishK Kumar
Ranch Hand

Joined: Oct 20, 2010
Posts: 40

I solved my problem and now its working.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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.
 
GeeCON Prague 2014
 
subject: Oracle sql exception: