Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

insert a really long string in Oracle with SQl PLUS

Posted on 2008-06-17
10
Medium Priority
?
2,173 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
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month21 days, 1 hour left to enroll

810 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