Insert SQL statement into CLOB cell 1

[dtrahokie]

I'm trying to insert a SQL statement into a CLOB datatype formatted column. I believe the problem originates from the fact that the SQL statement has quote marks within it. Has anyone ever had this problem before? I have attached the Insert statement below.

Thanks in advance

-Matt

INSERT INTO COPY_OF_CNTR_ID.IAVA_SQL_SENTENCES (SENTENCE, IAVA, THREATASST, RELEASEDATE, ACKSUSPENSE, COMPSUSPENSE, STATE, NOTES) VALUES ('SELECT DISTINCT COPY_OF_CNTR_ID.SOFTINV.IP_ADDRESS FROM COPY_OF_CNTR_ID.SOFTINV WHERE (COPY_OF_CNTR_ID.SOFTINV.SID_) IN (SELECT COPY_OF_CNTR_ID.SOFTWARE.SID_ FROM COPY_OF_CNTR_ID.SOFTWARE WHERE COPY_OF_CNTR_ID.SOFTWARE.MFG Like 'Symantec' AND COPY_OF_CNTR_ID.SOFTWARE.TITLE Like 'Norton Personal Firewall' AND COPY_OF_CNTR_ID.SOFTWARE.VERSION >'2002') OR (COPY_OF_CNTR_ID.SOFTINV.SID_) IN (SELECT COPY_OF_CNTR_ID.SOFTWARE.SID_ FROM COPY_OF_CNTR_ID.SOFTWARE WHERE COPY_OF_CNTR_ID.SOFTWARE.MFG Like 'AAA' AND COPY_OF_CNTR_ID.SOFTWARE.TITLE Like 'Street Atlas' AND COPY_OF_CNTR_ID.SOFTWARE.VERSION='6')', '2003-A-0012', 'High', '12/03/2003', '02/20/2004', '10/14/2004', 'In Progress', 'Notes')

 

[SantaMufasa]

Hokie,

There are several ways to deal with this issue. One method is to use/embed two single quotes ('') for every one single quote you wish to remain in your resulting string.

Another method that I prefer is to embed a "back-quote" (`) [an ASCII 96) (usually under the tilde in the upper left of your keyboard) in place of a standard single quote/apostrophe [ASCII 39). Then whenever you wish to use the CLOB cell, refer to it in this fashion:

SELECT translate(<CLOB-column-name>,chr(96),chr(39))
from <table-name>;

The above code then translates any backward quotes to apostrophes.

Let us know what you decide.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:05 (20Jul04) UTC (aka "GMT" and "Zulu"), 14:05 (20Jul04) Mountain Time)

 

[dtrahokie]

Dave: You're a huge help! Thanks so much.

-Matt