Newbie help 2

[shuesty]

Hey, I'm trying to enter the following string into a database

dd_cd=02_00060_00003&format=rdb&period=10&site_no=14105700

ok so the actualy string is 5 times longer and it's a website but this is the part that's acting up. Oracle is changing this section to

dd_cd=02_00060_00003=rdb=10=14105700

As well as complaining that 'format', 'period' and 'site_no'. Now I know that the error is on the & character because talking them out allows it to be inserted. I've tried escaping the string but apparantly it doesn't work ... or I'm using bad syntax. If someone could lend me a moment of their time teach me how to get past this I'd be very happy!!


Shuesty

 

[SantaMufasa]

Shuesty,

Yes, you isolated the problem: it results from Oracle's use of "&" as a "prompt-definition" character. You can avoid this problem and use your original string(s) by entering the following command at the SQL*Plus prompt just prior to your running your commands:

set define off

The SQL*Plus "define" setting defaults back to "&" upon re-connect to SQL*Plus, or, if you wish to reset to the original setting during your same SQL*Plus session, you can issue the command:

set define "&"

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:15 (18Aug04) UTC (aka "GMT" and "Zulu"), 13:15 (18Aug04) Mountain Time)

 

[shuesty]

Well it does work while using SQL*Plus. What I forgot to mention was that I'm using a TOracleQuery component from inside Delphi to run the sql lines. And when I try to execute the 'set define off' line I get a 'ORA-00922: missing or invalid option'. Any thoughts as to why?

Shuesty

 

[Turkbear]

Hi,
Sql and SqlPlus are different beasts..the 'set define off' is a SqlPlus option, not 'pure' Sql, so the Delphi interface may not recognize it ( probably will not, based on the error)..

You could try inserting it using the acscii value instead of the literal by using chr(38)
so the string would be

'dd_cd=02_00060_00003'||chr(38)||'format=rdb'||chr(38)||'period=10'||chr(38)||'site_no=14105700'



Some variant of this should allow ypu to build the needed string

 

[shuesty]

Well guys thanks for the help. I'm guessing both are gonna be quite helpful in the future since I'll be working with both environments. And thanks Turkbear ... amazing how often the easiest solution is the one you don't think of.


Shuesty