How can I store sql statements in an oracle table?

Question

We need to store a select statement in a table

select * from table where col = 'col'

But the single quotes messes the insert statement up.

Is it possible to do this somehow?

Answer 1

How are you performing the insert? If you are using any sort of provider on the front end, then it should format the string for you so that quotes aren't an issue.

Basically, create a parameterized query and assign the value of the SQL statement to the parameter class instance, and let the db layer take care of it for you.

Answer 2

If you are using a programming language such as JAVA or C#, you can use prepared (parametrized) statements to put your values in and retrieve them.

If you are in SQLPlus you can escape the apostrophe like this:

insert into my_sql_table (sql_command) 
values ('select * from table where col = ''col''');

Answer 3

Single quotes are escaped by duplicating them:

INSERT INTO foo (sql) VALUES ('select * from table where col = ''col''')

However, most database libraries provide bind parameters so you don't need to care about these details:

INSERT INTO foo (sql) VALUES (:sql)

... and then you assign a value to :sql.

Answer 4

Hi martilyo,

you can either use two quotes '' to represent a single quote ' or (with 10g+) you can also use a new notation:

SQL> select ' ''foo'' ' txt from dual;

TXT
-------
 'foo'

SQL> select q'$ 'bar' $' txt from dual;

TXT
-------
 'bar'

Answer 5

Don't store SQL statements in a database!!

Store SQL Views in a database. Put them in a schema if you have to make them cleaner. There is nothing good that will happen ever if you store SQL Statements in a database, short of logging this is categorically a bad idea.

Also if you're using 10g, and you must do this: do it right! Per the FAQ

Use the 10g Quoting mechanism:
Syntax
 q'[QUOTE_CHAR]Text[QUOTE_CHAR]'
 Make sure that the QUOTE_CHAR doesnt exist in the text.
SELECT q'{This is Orafaq's 'quoted' text field}' FROM DUAL;

Answer 6

From Oracle 10G on there is an alternative to doubling up the single quotes:

insert into mytable (mycol) values (q'"select * from table where col = 'col'"');

I used a double-quote character ("), but you can specify a different one e.g.:

insert into mytable (mycol) values (q'@select * from table where col = 'col'@');

The syntax of the literal is:

q'<special character><your string><special character>'

It isn't obviously more readable in a small example like this, but it pays off with large quantities of text e.g.

insert into mytable (mycol) values (
   q'"select empno, ename, 'Hello' message
   from emp
   where job = 'Manager'
   and name like 'K%'"'
);