Error while inserting using JDBC prepared statement

Question

I am trying to insert some values into Oracle DB from Java using the following JDBC statement:

String SQL_PREP_INSERT = "INSERT INTO ABC.TEST (LOG_ID, SESSION_ID,USER_ID) VALUES"
            + " (ABC.logid_seq.nextval, ?, ?)";

stmt = con.prepareStatement(SQL_PREP_INSERT);
stmt.setString(1, sessionId);
stmt.setString(2, userid);
stmt.execute();
stmt.close();

The sequence is created as follows:

create sequence  ABC.logid_seq
minvalue 1 maxvalue 9999999999999999999999 
increment by 10 start with 10 cache 20 noorder  nocycle ;

I am getting the following error,

java.sql.SQLException: ORA-00942: table or view does not exist

But when I try to insert into the table manually, it's successful.

insert into ABC.test(LOG_ID,SESSION_ID,USER_ID) values 
    (VZPPTL.logid_seq.nextval,'test_session', '001');

What's the problem?

http://stackoverflow.com/questions/6561650/getting-an-exception-ora-00942-table-or-view-does-not-exist-when-inserting-in — Samir Mangroliya, Apr 25 '12 at 09:39

score 2 · Accepted Answer · answered Apr 25 '12 at 10:29

2

Possibly looking at the wrong table or database. Are you sure your looking at the right database from the code?

answered Apr 25 '12 at 10:29

seeker

6,841
24
64
100

yes, its right database and right table, since I was able to insert previously before adding sequence into the insert statement. I just dropped the table and recreated by adding new column log_id. And now getting this error. – Gopi Apr 25 '12 at 10:51
Have you tried running query generated by `preparedstatement`, on the database? – seeker Apr 25 '12 at 11:07
No. I cant generate Prepared Statement query now since the code is in different box and I need to drop a code change. But in local machine I cannot try since having pointbase and not Oracle DB. I am getting the below on trying with pointbase, java.sql.SQLException: Column "NEXTVAL" not found in table at position 211. – Gopi Apr 25 '12 at 12:20
I got it working now. The problem was privilege problem to the sequence. Had to grant "Select" privilege to the role which is accessed by the application. Thanks. – Gopi Apr 25 '12 at 12:39

Sathish Santhosam · Answer 2 · 2012-04-25T10:35:34.723

1

In prepare statement no need to give schema name(In this case ABC).

Try this, it might work.

String SQL_PREP_INSERT = "INSERT INTO TEST (LOG_ID, SESSION_ID,USER_ID) VALUES" + " (logid_seq.nextval, ?, ?)";

edited Apr 25 '12 at 10:35

answered Apr 25 '12 at 10:26

Sathish Santhosam

185
10

Hmm ok, I will try without schema name. But previously I was able to insert using schema.table_name. But in that case, I was not having sequence. String SQL_PREP_INSERT = "INSERT INTO ABC.TEST (SESSION_ID,USER_ID) VALUES" + " (?, ?)"; – Gopi Apr 25 '12 at 10:37
I just added a new column log_id. And inserting using sequence. After adding it, its throwing that error. – Gopi Apr 25 '12 at 11:00

Error while inserting using JDBC prepared statement

2 Answers2