Single quote in Dynamic sql

[rdharmar]

When I execute my Dynamic sql statement, I get an missing keyword error because it sees a single quote value.

Is there a way to replace the single quote value?

Thanks
Ram

 

[Turkbear]

Hi,
Depends..Should we guess what the Sql statement is?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[rdharmar]

The value that needs to be inserted using dynamic sql is like this: Lighting Foundations - 100' Poles

I want to preserve the single quote when I do the insert into table using Dynamic sql.

 

[SantaMufasa]

Ram,

There are multiple methods to deal with this issue. My favourite method is to us a "backward apostrophe" (`)...[i.e., an Oracle chr(96)] to represent a normal single quote/apostrophe:

select 'Lighting Foundations - 100` Poles' string from dual;

STRING
---------------------------------
Lighting Foundations - 100` Poles

If you don't like that technique, then another method (in Oracle) to represent a literal single quote with a quoted string is to code the string as:

select 'Lighting Foundations - 100'' Poles' string from dual;

STRING
---------------------------------
Lighting Foundations - 100' Poles

In Oracle, two successive single quotes within a literal result in one single quote.

Another method:

select replace('Lighting Foundations - 100^ Poles','^',chr(39)) string from dual;

STRING
---------------------------------
Lighting Foundations - 100' Poles

Let us know your thoughts about these options.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[rdharmar]

I have done some research on tek-tip on this and I've seen your solution for this before. I can't really alter the data. I am trying to do something like this but can't get it to work:

Translate(in_str, char(39),char(39)||char(39)||char(39))

 

[SantaMufasa]

Ram,

We are still "flying blind" (as Turkbear mentioned above) until you post your actual dynamic SQL statement. Please post a/the block of code that represents what you are trying to do.

Also, please post an explanation of why you are apparently attempting to code literal data (e.g., "Lighting Foundations - 100' Poles") into your dynamic SQL.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Turkbear]

Hi,
Please post the actual Insert statement
that you want to use .

Santa's methods do not require altering the data, just the sql used for the insert.







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[rdharmar]

Sorry about that, here is the actual statement that fails, because of the single quote value.


MERGE INTO DETAILS37150433 dp USING ( SELECT 199077 as id FROM dual) dp1 ON ( dp.ID = dp1.id ) WHEN MATCHED THEN UPDATE SET Category2='Lighting Foundations - 100' Poles' WHEN NOT MATCHED THEN INSERT (ID,PROGRESSTYPE,CONTRACT, Category2) VALUES (199077,185864,37150433,'Lighting Foundations - 100' Poles')

I am working on a datastore that needs hierachical data to be flattened out, there are a bunch of fields like Category2 column in the above query which is built dynamically and needs to be populated.

 

[SantaMufasa]

Thanks for posting the sample code, Ram. IMO, the simplest method for achieving your objective is to use the "double-single-quote" method as I illustrate in your code, below:

MERGE INTO DETAILS37150433 dp USING ( SELECT 199077 as id FROM dual) dp1  ON    ( dp.ID = dp1.id )  WHEN MATCHED THEN UPDATE SET Category2='Lighting Foundations - 100'' Poles' WHEN NOT MATCHED THEN   INSERT  (ID,PROGRESSTYPE,CONTRACT, Category2) VALUES (199077,185864,37150433,'Lighting Foundations - 100'' Poles')

Let us know of your findings/results using this code.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CIMTEET]

rdharmar,

To print quotes in a dynamic sql statement, I have had to place 4 quotes and concantenators on either side. I know the app is a bit different, but its a suggestion.
field_name := 'text'
variable := ''''||field_name||''''

update_string:='UPDATE temp_output SET '||variable||'where blah_blah = blah_blah';

The way the variable will print is ::
'text'

Otherwise it will say invalid identifier. Can you place the text you want in a string?

 

[karluk]

I think you were on the right track with your use of the translate function, but I'm not aware that translate can replace a single character with a double character substitute. I would try the replace function instead. Something like the following should work.

replace(in_str, '''', '''''')

 

[rdharmar]

Thanks All.

I will try both karluk and CIMTEET suggestions and update my results here.

Hope it helps anybody searching for a similar issue.

Thanks again!
Ram

 

[rdharmar]

CIMTEET suggestion of wrapping the text with quotes was pretty close to what I needed. I made a small change to it and used two single quotes as shown below:
field_name := 'Text' -- text that contains a single quote
variable := ''||field_name||''

The replace command as suggested by Kurluk appends an additional single quote to the data, I couldn't find a way to work around that.

Ram

 

[rdharmar]

I had to slip this one in to correct what I said earlier.
I used both CIMTEET and Kurluk suggestions. Here's how I made this work

v_attributeValue:= ''''||REPLACE(fieldName,'''','''''')||'''';