Home » SQL & PL/SQL » SQL & PL/SQL » Oracle: sql update on field containing sql itself (Oracle Database 11g )
Oracle: sql update on field containing sql itself [message #683899] Wed, 03 March 2021 05:44 Go to next message
apisto
Messages: 2
Registered: March 2021
Junior Member
Hello @all,

may be someone of our community can help regarding a special update-sql.

I have a table/field named for example SQL_TAB/SQL_FIELD (varchar 3500)
The SQL_FIELD itself contains a sql-statement like "select * from anytab where field1 = 'ABC'".
This sql statement within SQL_FIELD i want to change using sql. Problem is the " ' " within the sql-statement. If i want to change field1 to "where field1 in ('ABC','FTG','2ab')" i have a conflict with the string sign.
My first idea was to change a oracle environment variable from ' into § or something like that. But i did'nt find such variable.

Any idea, how i can proceed?

Many thanks for any idea,
Jens
Re: Oracle: sql update on field containing sql itself [message #683900 is a reply to message #683899] Wed, 03 March 2021 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 67815
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

You can use several ways to handle quote:
1/ double them:
SQL> select 'string with a quote ('')' from dual;
'STRINGWITHAQUOTE('')'
-----------------------
string with a quote (')
2/ Using Q syntax:
SQL> select q'[string with a quote (')]' from dual;
Q'[STRINGWITHAQUOTE(')]
-----------------------
string with a quote (')
[] can replaced by any couple of characters that does not appear in the string; for instance:
SQL> select q'( ' )' q, q'{ ' }' q, q'/ ' /' from dual;
Q   Q   Q'/
--- --- ---
 '   '   '
Re: Oracle: sql update on field containing sql itself [message #683901 is a reply to message #683900] Wed, 03 March 2021 06:38 Go to previous message
apisto
Messages: 2
Registered: March 2021
Junior Member
Hallo und viel Dank,

die Antwort hat gepasst, ich bin begeistert.

Mit Gruß von der Küste,
Jens
Previous Topic: Reading text from binary data in a blob
Next Topic: schema level dml trigger
Goto Forum:
  


Current Time: Thu Apr 22 11:30:11 CDT 2021