Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2731
  • Last Modified:

Columns in sqlplus word wrapping

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
cwplough
Asked:
cwplough
  • 7
  • 2
  • 2
  • +2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
is this in a windows batch command prompt?
note that there, the command prompt itself might be "limited" to 200 characters...
0
 
cwploughAuthor Commented:
I have it spooling out to a file. Sorry forgot to mention that.

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you please show the sql script?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
cwploughAuthor Commented:
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
 
SujithData ArchitectCommented:
try to give column formatting to all the selected columns
say like:

column SCAC format a200
0
 
cwploughAuthor Commented:
Still no luck..same issue. I'm attaching a screenshot of what I see using notepad++.
0
 
joebednarzCommented:
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
 
cwploughAuthor Commented:
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
 
cwploughAuthor Commented:
Here is the output
output.JPG
0
 
dbmullenCommented:
well I don't know about notepad++ but in normal notepad you can to "word wrap" on/off

format/wordwrap

0
 
SujithData ArchitectCommented:
Do you have the line breaks in your data.
Looks like there are newline characters.
0
 
cwploughAuthor Commented:
The output is the same in either notepad or wordpad.
output.JPG
0
 
cwploughAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 7
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now