Solved

Columns in sqlplus word wrapping

Posted on 2008-10-01
13
2,512 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
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: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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
levels for reporting 5 74
How to return an OUT parameter from and ORACLE 3 66
case statement in where clause with not exist 15 52
Oracle Next Available Number 2 41
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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…
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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

786 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