insert a really long string in Oracle with SQl PLUS

Hi guys,

I have a small problem. I have developed a SQL script that will insert a  long string into a table. I have to mention that the string is about 1300 characters, and that it contains the & character. What I have did is something like what you have shown in the code snippet.
My problem is that when running this script in a development enviroment, using Toad 7.3 to run the script, all goes well.
When this script is executed in production enviroment with SQLPLus , well in the table, my string is getting broken by a space wich somehow is beeing insert in the string. It apears as if the statement modifies the string somehow:

             insert into table(id, string) values (1, 'asasdasdsa&dsad asdsadsadsad&sad');

I really need your help, because I'm clueless.


set define off;
set linesize 2500;
insert into table(id, string) values (1, 'asasdasdsa&dsadasdsadsadsad&sad');

Open in new window

phobossAsked:
Who is Participating?
 
DavidConnect With a Mentor Senior Oracle Database AdministratorCommented:
FWIW, SET SCAN is obsolete and duplicates the SET DEFINE functionality.
0
 
sdstuberConnect With a Mentor Commented:
what happens if you run it in development with sqlplus?
0
 
MilleniumaireConnect With a Mentor Commented:
What do you mean by "broken by a space"?

Do you mean a space is inserted into the string, so your example above would look like this (i.e. has a space in the middle):

asasdasdsa&dsa dasdsadsadsad&sad

Or is the string being split over multiple lines like this:

asasdasdsa&dsa
dasdsadsadsad&sad

How are you interrogating the string that has been inserted into the table?  Are you viewing it through toad or are you using sqlplus to select the value out of the table?  If the latter, are you sure the data isn't being affected by retrieving it i.e. the stored table value is okay?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
DavidConnect With a Mentor Senior Oracle Database AdministratorCommented:
Please confirm your prod environment is keeping the SET DEFINE OFF prior to execution, as you have in your example.  Don't just assume it.  My two cents.
0
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
set scan off
set define off

run the above before you run your script in sql*plus production.
0
 
phobossAuthor Commented:
Milleniumaire:
yes the string, after executing the script willl be like that xxxxxxxxxxxxxxSPACE_HEREyyyyyyyyyyyyyyyy, not like this
xxxxxxxxxxxxxxNEW_LINEyyyyyyyyyyyyyyyyyyy

My string is actually an URL, so you can understand why it is important for me not to broke that URL.
I can see that the space is present in the string because, the data is feteched using java code, then printed in an HTML, and also after checking the logs of my application that uses that string, I can see exactly that my string (URL) is beeing broken

0
 
phobossAuthor Commented:
i have forgot, in my env when using SQL plus the space does not insert himself in the string. It that just happend a coupple of times in production env, wich i can.t really control. Sometimes it wasn.t even a space, was some char Unicode hexa like 0x00A1 or something similar, do not remember exactly.
0
 
phobossAuthor Commented:
come on guys, doesn't anybody ever encountered a situation like this?
0
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
did you try ?

set scan off
set define off

run the above before you run your script in sql*plus production.

what is your issue now because you initially said & is getting changed to something and you last update is confusing.....
0
 
MilleniumaireConnect With a Mentor Commented:
phoboss, to be honest, no I haven't encountered a situation like this!!!

From your last description, it isn't the code that's the problem as it works 99.9% of the time.  If it only happens infrequently and randomly on your production system is it possibly a problem with the data that is being processed.

Where does the string that is being built up come from?  If taken from tables, is it possible that the source data contains the space/special character?  Is it possibly a network issue, causing additional characters to be added to the data?

Could it be a NLS issue?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.