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.do main_name, it.transac tion_code, it.element _name,it.o bject_gid, SUBSTR(xml _blob, INSTR( xml_blob, '<ServiceProviderAliasValu e>',1,1)+2 7, INSTR( xml_blob, '</ServiceProviderAliasVal ue>',1,1) - (INSTR( xml_blob, '<ServiceProviderAliasValu e>',1,1)+2 7)) 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,SUB STR(xml_bl ob, INSTR( xml_blob, '<ShipmentRefnumValue>',1, 1)+21,INST R( xml_blob, '</ShipmentRefnumValue>',1 ,1) - (INSTR( xml_blob, '<ShipmentRefnumValue>',1, 1)+21) ) Shipment_refnum_value2, SUBSTR(il.i_message_text,0 ,200),it.i nsert_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_ transmissi on_no order by it.domain_name,it.insert_d ate asc
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.do
ASKER
I have it spooling out to a file. Sorry forgot to mention that.
can you please show the sql script?
ASKER
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.do main_name, it.transac tion_code, it.element _name,it.o bject_gid, SUBSTR(xml _blob, INSTR( xml_blob, '<ServiceProviderAliasValu e>',1,1)+2 7, INSTR( xml_blob, '</ServiceProviderAliasVal ue>',1,1) - (INSTR( xml_blob, '<ServiceProviderAliasValu e>',1,1)+2 7)) 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,SUB STR(xml_bl ob, INSTR( xml_blob, '<ShipmentRefnumValue>',1, 1)+21,INST R( xml_blob, '</ShipmentRefnumValue>',1 ,1) - (INSTR( xml_blob, '<ShipmentRefnumValue>',1, 1)+21) ) Shipment_refnum_value2, SUBSTR(il.i_message_text,0 ,200),it.i nsert_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_ transmissi on_no order by it.domain_name,it.insert_d ate asc
/
spool off
exit;
EOF
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
spool test.txt
SELECT it.i_transmission_no,it.do
/
spool off
exit;
EOF
try to give column formatting to all the selected columns
say like:
column SCAC format a200
say like:
column SCAC format a200
ASKER
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;
COLUMN this_column FORMAT a200;
SELECT previous_column, SUBSTR(IL.I_MESSAGE_TEXT,0
FROM your_table;
ASKER
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.do main_name, it.transac tion_code, it.element _name,it.o bject_gid, SUBSTR(xml _blob, INSTR( xml_blob, '<ServiceProviderAliasValu e>',1,1)+2 7, INSTR( xml_blob, '</ServiceProviderAliasVal ue>',1,1) - (INSTR( xml_blob, '<ServiceProviderAliasValu e>',1,1)+2 7)) 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,SUB STR(xml_bl ob, INSTR( xml_blob, '<ShipmentRefnumValue>',1, 1)+21,INST R( 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_da te 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_ transmissi on_no order by it.domain_name,it.insert_d ate asc
/
spool off
exit;
EOF
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.do
/
spool off
exit;
EOF
ASKER
Here is the output
output.JPG
output.JPG
well I don't know about notepad++ but in normal notepad you can to "word wrap" on/off
format/wordwrap
format/wordwrap
Do you have the line breaks in your data.
Looks like there are newline characters.
Looks like there are newline characters.
ASKER
The output is the same in either notepad or wordpad.
output.JPG
output.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
note that there, the command prompt itself might be "limited" to 200 characters...