Link to home
Start Free TrialLog in
Avatar of cwplough
cwplough

asked on

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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

is this in a windows batch command prompt?
note that there, the command prompt itself might be "limited" to 200 characters...
Avatar of cwplough
cwplough

ASKER

I have it spooling out to a file. Sorry forgot to mention that.

can you please show the sql script?
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
try to give column formatting to all the selected columns
say like:

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

format/wordwrap

Do you have the line breaks in your data.
Looks like there are newline characters.
The output is the same in either notepad or wordpad.
output.JPG
ASKER CERTIFIED SOLUTION
Avatar of cwplough
cwplough

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial