insert query into oracle database [duplicate]

Question

Possible Duplicate:
How to handle a single quote in Oracle SQL

My Senario: I want to save the query(not result set) in the database. I am using Java as the front end; My Table(Querytab) has the following Fields:

sno VARCHAR2(1024)
QUERY VARCHAR2(4000)

The problem is when I insert a query which has conditions e.g insert into querytab values('100','select * from querytab where sno ='100'');

ERROR at line 1: ORA-00933: SQL command not properly ended

Problem is: The Queryvalue gets terminated when it finds the ' How to solve this. Thanks in advance.

@Mat : This is not duplicate of what you are saying... He want to store the query... — Fahim Parkar
– Fahim Parkar, Commented Jan 21, 2012 at 9:08
The problem is the same. You need to escape your strings properly. (And if you're doing this from code, you should be using prepared statements and bind variables.) — Mat
– Mat, Commented Jan 21, 2012 at 9:09

user330315 · Accepted Answer · 2012-01-21 09:11:34Z

2

To insert single quotes, you need to escape them. Escaping is done by simply duplicating them:

insert into querytab 
(sno, query)
values
('100','select * from querytab where sno = ''100'' ');

This is explained in the manual:

http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements003.htm#i42617

answered Jan 21, 2012 at 9:11

user330315

Sign up to request clarification or add additional context in comments.

Comments

KV Prajapati · Accepted Answer · 2012-01-21 09:07:17Z

1

Use PreparedStatement to escape the string.

Something like this:

sql="insert into querytab values (?,?)";
PreparedStatement ps=conn.prepareStatement(sql);
ps.setString(1,"100");
ps.setString(2,"select * from querytab where sno ='100'");
ps.executeUpdate();

answered Jan 21, 2012 at 9:07

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

Comments

Lucifer · Accepted Answer · 2012-01-21 09:07:58Z

0

try this,

insert into querytab values('100','select * from querytab where sno =100');

answered Jan 21, 2012 at 9:07

Lucifer

29.7k25 gold badges93 silver badges144 bronze badges

1 Comment

JB Nizet Over a year ago

You're storing a different string. That's not the string that the OP wants to store.

Collectives™ on Stack Overflow

insert query into oracle database [duplicate]

3 Answers 3

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Linked

Related