Solved

insert a really long string in Oracle with SQl PLUS

Posted on 2008-06-17
10
1,966 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 73

Assisted Solution

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

Assisted Solution

by:Milleniumaire
Milleniumaire earned 100 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 200 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
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 150 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 200 total points
ID: 21803474
FWIW, SET SCAN is obsolete and duplicates the SET DEFINE functionality.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 150 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 100 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle SQL queries -- Challenging question 13 64
one-way data "masking" MD5 sql 26 99
null value 15 65
PL/SQL - Leading zeros 7 41
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

706 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now