?
Solved

Columns in sqlplus word wrapping

Posted on 2008-10-01
13
Medium Priority
?
2,586 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
[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
  • 7
  • 2
  • 2
  • +2
13 Comments
 
LVL 143

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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22613787
can you please show the sql script?
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

777 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