Solved

Columns in sqlplus word wrapping

Posted on 2008-10-01
13
2,498 Views
Last Modified: 2011-10-19
Hello,

I'm running the following query below. I have set the linsize to 8000 the long to 200 and the longc to 200 yet the column which has 200 chracters is getting word wrapped at 100 chracters. Am I missing another option to get it to display on the same line? The column headers are showing correctly at 200 chracters.

Thanks,
Nick


SELECT it.i_transmission_no,it.domain_name,it.transaction_code,it.element_name,it.object_gid,SUBSTR(xml_blob, INSTR( xml_blob, '<ServiceProviderAliasValue>',1,1)+27, INSTR( xml_blob, '</ServiceProviderAliasValue>',1,1) - (INSTR( xml_blob, '<ServiceProviderAliasValue>',1,1)+27)) SCAC,SUBSTR(xml_blob, INSTR( xml_blob, '<ShipmentRefnumValue>',1,2)+21, INSTR( xml_blob, '</ShipmentRefnumValue>',1,2) - (INSTR( xml_blob, '<ShipmentRefnumValue>',1,2)+21) ) Shipment_Refnum_value1,SUBSTR(xml_blob, INSTR( xml_blob, '<ShipmentRefnumValue>',1,1)+21,INSTR( xml_blob, '</ShipmentRefnumValue>',1,1) - (INSTR( xml_blob, '<ShipmentRefnumValue>',1,1)+21) ) Shipment_refnum_value2, SUBSTR(il.i_message_text,0,200),it.insert_date FROM I_TRANSACTION it, i_log il WHERE  it.domain_name!='ED' and it.i_transmission_no in(select DISTINCT(I_TRANSMISSION_NO) from i_transmission where status = 'ERROR' and insert_date between TO_DATE('$date 00:00:00','mm-dd-yyyy hh24:mi:ss') and TO_DATE('$date 23:59:59','mm-dd-yyyy hh24:mi:ss')) and it.i_transmission_no=il.i_transmission_no order by it.domain_name,it.insert_date asc
0
Comment
Question by:cwplough
  • 7
  • 2
  • 2
  • +2
13 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22613722
is this in a windows batch command prompt?
note that there, the command prompt itself might be "limited" to 200 characters...
0
 

Author Comment

by:cwplough
ID: 22613740
I have it spooling out to a file. Sorry forgot to mention that.

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22613787
can you please show the sql script?
0
 

Author Comment

by:cwplough
ID: 22613855
Here is the entire script


sqlplus -s x/x@x << EOF
set linesize 8000
set pagesize 50000
set long 200
set longc 200
column SUBSTR(IL.I_MESSAGE_TEXT,0,200) for a200
spool test.txt


SELECT it.i_transmission_no,it.domain_name,it.transaction_code,it.element_name,it.object_gid,SUBSTR(xml_blob, INSTR( xml_blob, '<ServiceProviderAliasValue>',1,1)+27, INSTR( xml_blob, '</ServiceProviderAliasValue>',1,1) - (INSTR( xml_blob, '<ServiceProviderAliasValue>',1,1)+27)) SCAC,SUBSTR(xml_blob, INSTR( xml_blob, '<ShipmentRefnumValue>',1,2)+21, INSTR( xml_blob, '</ShipmentRefnumValue>',1,2) - (INSTR( xml_blob, '<ShipmentRefnumValue>',1,2)+21) ) Shipment_Refnum_value1,SUBSTR(xml_blob, INSTR( xml_blob, '<ShipmentRefnumValue>',1,1)+21,INSTR( xml_blob, '</ShipmentRefnumValue>',1,1) - (INSTR( xml_blob, '<ShipmentRefnumValue>',1,1)+21) ) Shipment_refnum_value2, SUBSTR(il.i_message_text,0,200),it.insert_date FROM I_TRANSACTION it, i_log il WHERE  it.domain_name!='ED' and it.i_transmission_no in(select DISTINCT(I_TRANSMISSION_NO) from i_transmission where status = 'ERROR' and insert_date between TO_DATE('$date 00:00:00','mm-dd-yyyy hh24:mi:ss') and TO_DATE('$date 23:59:59','mm-dd-yyyy hh24:mi:ss')) and it.i_transmission_no=il.i_transmission_no order by it.domain_name,it.insert_date asc
/

spool off

exit;

EOF
0
 
LVL 27

Expert Comment

by:sujith80
ID: 22615356
try to give column formatting to all the selected columns
say like:

column SCAC format a200
0
 

Author Comment

by:cwplough
ID: 22616366
Still no luck..same issue. I'm attaching a screenshot of what I see using notepad++.
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.

 
LVL 9

Expert Comment

by:joebednarz
ID: 22616519
You need to alias the column and use the "column" command on the alias:

COLUMN this_column FORMAT a200;

SELECT previous_column, SUBSTR(IL.I_MESSAGE_TEXT,0,200)  this_column, next_column
FROM your_table;
0
 

Author Comment

by:cwplough
ID: 22616722
Here is my script modified. Still no luck and I've attached the picture for reference.

sqlplus -s x/x@x << EOF
set linesize 8000
set pagesize 50000
set long 200
set longc 200
column error_message  format a200
spool test.txt


SELECT it.i_transmission_no,it.domain_name,it.transaction_code,it.element_name,it.object_gid,SUBSTR(xml_blob, INSTR( xml_blob, '<ServiceProviderAliasValue>',1,1)+27, INSTR( xml_blob, '</ServiceProviderAliasValue>',1,1) - (INSTR( xml_blob, '<ServiceProviderAliasValue>',1,1)+27)) SCAC,SUBSTR(xml_blob, INSTR( xml_blob, '<ShipmentRefnumValue>',1,2)+21, INSTR( xml_blob, '</ShipmentRefnumValue>',1,2) - (INSTR( xml_blob, '<ShipmentRefnumValue>',1,2)+21) ) Shipment_Refnum_value1,SUBSTR(xml_blob, INSTR( xml_blob, '<ShipmentRefnumValue>',1,1)+21,INSTR( xml_blob, '</ShipmentRefnumValue>',1,1) - (INSTR( xml_blob, '<ShipmentRefnumValue>',1,1)+21) ) Shipment_refnum_value2, SUBSTR(il.i_message_text,0,200) error_message,it.insert_date FROM I_TRANSACTION it, i_log il WHERE  it.domain_name!='ED' and it.i_transmission_no in(select DISTINCT(I_TRANSMISSION_NO) from i_transmission where status = 'ERROR' and insert_date between TO_DATE('$date 00:00:00','mm-dd-yyyy hh24:mi:ss') and TO_DATE('$date 23:59:59','mm-dd-yyyy hh24:mi:ss')) and it.i_transmission_no=il.i_transmission_no order by it.domain_name,it.insert_date asc
/

spool off

exit;

EOF
0
 

Author Comment

by:cwplough
ID: 22616725
Here is the output
output.JPG
0
 
LVL 10

Expert Comment

by:dbmullen
ID: 22620711
well I don't know about notepad++ but in normal notepad you can to "word wrap" on/off

format/wordwrap

0
 
LVL 27

Expert Comment

by:sujith80
ID: 22621226
Do you have the line breaks in your data.
Looks like there are newline characters.
0
 

Author Comment

by:cwplough
ID: 22623065
The output is the same in either notepad or wordpad.
output.JPG
0
 

Accepted Solution

by:
cwplough earned 0 total points
ID: 22701622
I had our DBA help me out and finally got it working:

sqlplus -s x/x@x << EOF
set linesize 10000
set pagesize 50000
set long 200
set longc 200
set wrap off
column SCAC format a200
column ERROR_MESSAGE format a200
column Shipment_Refnum_value1 format a200
column Shipment_Refnum_value2 format a200
column ELEMENT_NAME format a50
column OBJECT_GID format a50
spool test.txt
set colsep '    '

SELECT it.i_transmission_no,it.i_transaction_no,it.domain_name,it.transaction_code,it.element_name, it.object_gid,SUBSTR(xml_blob, INSTR( xml_blob, '<ServiceProviderAliasValue>',1,1)+27, INSTR( xml_blob, '</ServiceProviderAliasValue>',1,1) - (INSTR( xml_blob, '<ServiceProviderAliasValue>',1,1)+27)) SCAC,SUBSTR(xml_blob, INSTR( xml_blob, '<ShipmentRefnumValue>',1,2)+21, INSTR( xml_blob, '</ShipmentRefnumValue>',1,2) - (INSTR( xml_blob, '<ShipmentRefnumValue>',1,2)+21) ) Shipment_Refnum_value1,SUBSTR(xml_blob, INSTR( xml_blob, '<ShipmentRefnumValue>',1,1)+21,INSTR( xml_blob, '</ShipmentRefnumValue>',1,1) - (INSTR( xml_blob, '<ShipmentRefnumValue>',1,1)+21) ) Shipment_Refnum_value2,replace( replace(il.i_message_text,chr(10), chr(32)),chr(13),chr(32)) ERROR_MESSAGE ,it.insert_date FROM I_TRANSACTION it, i_log il WHERE  it.i_transmission_no in(select DISTINCT(I_TRANSMISSION_NO) from i_transmission where status = 'ERROR' and insert_date between TO_DATE('10-07-2008 00:00:00','mm-dd-yyyy hh24:mi:ss') and TO_DATE('10-07-2008 23:59:59','mm-dd-yyyy hh24:mi:ss')) and it.i_transmission_no=il.i_transmission_no and il.i_message_class='E' order by it.domain_name,it.insert_date asc
/



spool off

exit;

EOF
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

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

705 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

15 Experts available now in Live!

Get 1:1 Help Now