Re: Problem w/ sql update, escape char?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 05 Jun 1999 20:26:55 GMT
Message-ID: <375d8756.10297256@newshost.us.oracle.com>

A copy of this was sent to jmac1_at_us.ibm.com (if that email address didn't require changing) On Sat, 05 Jun 1999 19:17:04 GMT, you wrote:

>Greetings,
>This problem is a little aggravating. Here is the statement I'm trying
>to execute (sorry for the formatting, dejanews doesn't give much leeway
>there):
>
>update TEST_0091_SQL
> set STR_WHERE = 'userCity in ( select TEST_CITIES_REGIONS.city from
>TEST_CITIES_REGIONS where TEST_CITIES_REGIONS.city =
>SOS_AVAILABILITY.usercity and TEST_CITIES_REGIONS.city = ''#1'') and
>dbsos = ''yes'''
> where NOME = '0091_REGIONQUERY';
>
>The database stores future queries to be executed and the architecture
>has already been frozen by 'higher authorities' :-) so the whys and
>what-fors aren't going to help. What I need is a little nudge to tell
>me why this statement is producing errors.
>
>This is the error I get:
>ORA-01756: quoted string not properly terminated
>

we will need more info. I tried your statement verbaitim in sqlplus:

SQL> create table test_0091_sql ( str_where varchar2(2000), nome   2 varchar2(2000) );

Table created.

SQL> l

  1  update TEST_0091_SQL
  2      set STR_WHERE = 'userCity in ( select TEST_CITIES_REGIONS.city from
  3  TEST_CITIES_REGIONS where TEST_CITIES_REGIONS.city =

  4 SOS_AVAILABILITY.usercity and TEST_CITIES_REGIONS.city = ''#1'') and   5 dbsos = ''yes'''
  6* where NOME = '0091_REGIONQUERY' SQL> / 0 rows updated.

it is syntactically correct.

o what environment are you using to submit the query? odbc, oci, pro*c, sqlplus??

o are there any other pieces of information we might be able to use? (eg: is there a trigger on the table test_0091_sql that uses dbms_sql to dynamically execute another query and its the thing that is failing?

o versions of stuff (database, os, platform, sqlnet, etc)

>Thanks in advance,
>Jim
>Philips Speech Processing
>jim.macgregor_at_psp-atl.be.philips.com
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Jun 05 1999 - 15:26:55 CDT