CODEWITHSUNDEEP
oracle-databaseplsqltoad
GreenyMcDuff
GreenyMcDuff

Reputation: 3622

Oracle SQL updating HUGECLOB

I am using TOAD (11.0 R2) for Oracle.

I need to update one of the columns in a table that holds data of type NCLOB. My UPDATE statement looks like this:

UPDATE table1
SET    string_content = 'SELECT my_string FROM table2 WHERE name = 'identifier''
WHERE  id = 10000

The problem is with the ' around the word identifier in my SELECT statement. The ' that immediately preceeds the word identifier ends the string.

I hope there is just a simple syntax error that I need to correct. Can someone show me how I can get the above statement to work please?

Upvotes: 0

Views: 6252

Answers (5)

Jeff
Jeff

Reputation: 674

In Oracle you can use a function for quoting string literals. It treats anything in the exclamation marks like a string literal. No need to double quote. Link for reference

DECLARE
    var_Updated_sql CLOB := q'!SELECT * FROM aTable WHERE name = 'aName';!';
BEGIN
    UPDATE fakeTable SET sqlStatement = var_Updated_sql WHERE sql_id = 1;
END;

Upvotes: 1

Andrea
Andrea

Reputation: 12355

If you don't like double quoting and you are using Oracle >= 10g you can use quote operator q (more info here):

UPDATE table1
SET string_content = 'SELECT my_string FROM table2 WHERE name = ' || q'['identifier']'
WHERE  id = 10000

Upvotes: 0

Linga
Linga

Reputation: 10555

Try this. Use two single quotes ''

UPDATE table1
SET    string_content = (SELECT my_string FROM table2 WHERE name = ''identifier'')
WHERE  id = 10000

Upvotes: 1

Dba
Dba

Reputation: 6639

Try like this,

UPDATE table1
SET    string_content = 'SELECT my_string FROM table2 WHERE name = ''identifier'''
WHERE  id = 10000

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

Should be this one:

UPDATE table1
SET    string_content = 'SELECT my_string FROM table2 WHERE name = ''identifier'''
WHERE  id = 10000

Upvotes: 1

Related Questions