Embedded Ampersand - Oracle vs Sybase

[David Cogley_1]

Using a Sybase source and an Oracle target, I encounter a problem with a field containing a "single quoted phrase with an ampersand".

A Perl script is used to pull field values from the Sybase records and then insert them into Oracle records on a different server. The problem value is 'HP IPS Group & Marketing'. The Perl script hangs. It is waiting for a value for "Marketing".

If I cut the insert statement from trace.log and paste it into a SQL*Plus window, Oracle responds with "Enter value for marketing: ".

So much for a Perl script written for Sybase and Oracle but only tested with Sybase. Is there a solution other than parsing each field value and replacing "&" with "ampersand"?

[Mark Greene_1]

can you post the portion of the perl script in question? It's not clear whether this is a problem with perl dealing with quotes and special characters, or a problem with sql.

mark

[A. Clay Stephenson · Accepted solution]

Hi:

I can think of three easy solutions on the Oracle side: 1) set escape some_character - this defines a character you can place immediately in front of the ampersand so that SQL will treat the & as a literal. 2) set scan off - turns off variable substitution for the SQL statement. 3) set define some_char - some_char will define another character to denote a vaiable.

[Henrique Silva_3]

also, you can parse the line coming from the sybase output and replace each special characters with whatever you need on the oracle inpu OR replace the special characters with their octal or hex representation in ASCII code !!!

David what ? I think I know this guy :-)))

Henrique Silva

[David Cogley_1]

I've assigned points before fully working through the problem.

I have been unable to set environment variables in Oracle from Perl using a stored procedure. Of course, I tried to develop the stored procedure in Oracle before implementing it in Perl. Couldn't get it to work in Oracle.

Next, I have entered "SET DEF OFF" and "SET SCAN OFF" into the login.sql file for the user called by the Perl script. I put a copy of the login.sql script in "G:\Oracle\Ora81\DBS\" and set the WIN2K environment variable SQLPATH to "G:\Oracle\Ora81\DBS\". Now, I need to see if login.sql is read when Perl opens the DBI connection.

If this doesn't work, I'll try glogin.sql. That might work but would remove the flexibility of scanning for input variables for another script by logging in as a different user.

If none of this works, I will post a short test script and log files which document the problem.

[David Cogley_1]

The ampersand problem is fixed.

Thanks to everyone.

Special thanks to A. Clay Stephenson for his hints.