SQL from SQL insert statements 1

[mcowen]

Hi,

I am trying to create update statements using SQL from SQL. I can concatinate into the spooled file every character apart from the much needed '. So the spooled statements look like:

update table_name SET (col_name) = "value";

Is there any way of specifying that the output be a ' as opposed to the "?

Many thanks
Matt

 

[jee]

Use two single quotes. Here is an example from a script I wrote to generate SQL:

SET  feedback OFF
SET echo OFF
SET pagesize 0
SET linesize 100
spool rolequery.SQL
SELECT 'SELECT USER_ID'
FROM dual;
SELECT ', DECODE (SUM (DECODE (ROLE, ''' || ROLE || ''',1)), NULL, NULL, ''     Y     '') ' || ROLE
FROM USER_ROLE;
SELECT 'FROM USER_ROLE GROUP BY USER_ID ORDER BY USER_ID;'
FROM dual;

spool OFF
SET feedback ON
SET pagesize 80
START rolequery