[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2195
  • Last Modified:

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

0
phoboss
Asked:
phoboss
  • 3
  • 2
  • 2
  • +2
7 Solutions
 
sdstuberCommented:
what happens if you run it in development with sqlplus?
0
 
MilleniumaireCommented:
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
 
DavidSenior 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
set scan off
set define off

run the above before you run your script in sql*plus production.
0
 
DavidSenior Oracle Database AdministratorCommented:
FWIW, SET SCAN is obsolete and duplicates the SET DEFINE functionality.
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 KumarProduction 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
 
MilleniumaireCommented:
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now