Dynamic SQL - How to add a single quote for an INSERT statement? 1

[wellster34]

Hello All,

Does anyone know how to add a single quote to a varchar2 variable? I'm bascially creating a dynamic INSERT statement by using variables. The problem is that the column is varchar2 and needs single quotes around the value. So, when I'm creating my INSERT statement, I have to use single quotes to concat the values together.

So, is it possible to add single quotes to a variable? Below is a sample of my logic:

insert_string := 'INSERT INTO IO.TEST_TABLE VALUES(' || err_tx_IN || ',' || err_dt_IN || ')';

CID := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(CID, insert_string, DBMS_SQL.NATIVE);

n := DBMS_SQL.EXECUTE(CID);

DBMS_SQL.CLOSE_CURSOR(CID);

So in this example, the values are passed to this pl/sql procedure. The problem is that the err_tx column is varchar2 and needs single quotes around it. I tried the double quotes but it is looking for a column. I tried '','' but the '' are viewed as a null space causing an error because it does not recognize the comma...

Any ideas on how to do it or maybe a better suggestion on how to do a dynamic insert statement?

Thanks for your time

 

[LKBrwnDBA]

Try this:

insert_string :=
       'INSERT INTO IO.TEST_TABLE VALUES(''' 
        || err_tx_IN || ''','''
        || err_dt_IN || ''')';

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Wellster,

Since I'm an old man and sometimes have problems with my eyesight and with counting single quotes to ensure that I have the correct number of single quotes to result in just one quote, I use a trick as an alternative to "LK's" fine method: Instead of coding with single quotes when I need an embedded single quote, use some other character such as "^", then I ask Oracle to TRANSLATE the "^" into a single quote [a CHR(39)]. The above code example would appear alternately as:

insert_string :=
       TRANSLATE ('INSERT INTO IO.TEST_TABLE VALUES(^' 
        || err_tx_IN || '^,^'
        || err_dt_IN || '^)',
        '^',chr(39));

I hope this helps you at some point.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:25 (23Sep04) UTC (aka "GMT" and "Zulu"), 10:25 (23Sep04) Mountain Time)