Showing results for 
Search instead for 
Did you mean: 

Embedded Ampersand - Oracle vs Sybase

Go to solution
David Cogley_1
Occasional Advisor

Embedded Ampersand - Oracle vs Sybase

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
Honored Contributor

Re: Embedded Ampersand - Oracle vs Sybase

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.

the future will be a lot like now, only later
A. Clay Stephenson
Acclaimed Contributor

Re: Embedded Ampersand - Oracle vs Sybase


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.
If it ain't broke, I can fix that.
Henrique Silva_3
Regular Advisor

Re: Embedded Ampersand - Oracle vs Sybase

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
"to be or not to be, what was the question ???? "
David Cogley_1
Occasional Advisor

Re: Embedded Ampersand - Oracle vs Sybase

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
Occasional Advisor

Re: Embedded Ampersand - Oracle vs Sybase

The ampersand problem is fixed.

Thanks to everyone.

Special thanks to A. Clay Stephenson for his hints.