Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

insert a really long string in Oracle with SQl PLUS

Posted on 2008-06-17
10
Medium Priority
?
2,158 Views
Last Modified: 2013-12-19
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
Comment
Question by:phoboss
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 100 total points
ID: 21802837
what happens if you run it in development with sqlplus?
0
 
LVL 16

Assisted Solution

by:Milleniumaire
Milleniumaire earned 200 total points
ID: 21802879
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
 
LVL 23

Assisted Solution

by:David
David earned 400 total points
ID: 21803167
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 300 total points
ID: 21803303
set scan off
set define off

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

Accepted Solution

by:
David earned 400 total points
ID: 21803474
FWIW, SET SCAN is obsolete and duplicates the SET DEFINE functionality.
0
 

Author Comment

by:phoboss
ID: 21810570
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
 

Author Comment

by:phoboss
ID: 21810640
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
 

Author Comment

by:phoboss
ID: 21820489
come on guys, doesn't anybody ever encountered a situation like this?
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 300 total points
ID: 21820514
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
 
LVL 16

Assisted Solution

by:Milleniumaire
Milleniumaire earned 200 total points
ID: 21820677
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question