Skip to Main Content

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Connor McDonald

Thanks for the question, vignesh.

Asked: November 29, 2016 - 8:02 pm UTC

Last updated: November 30, 2016 - 2:39 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi,

Iam exporting a simple select command into a csv file.

but the resulting csv file is incomplete ie. amon 44 columns it is displaying only 3 columns.

please find my code below,


set headsep on
set term off
set newpage 0
set space 0
set colsep ;
col name format a1024
set feedback off
set termout off
set heading on
set echo off
set pagesize 0 embedded on
set trimspool on
set linesize 10240
set numw 3
set escape #
col today new_value mydate

select to_char(sysdate, 'YYMMDD') today from dual ;
spool GSO_QUOTE_&mydate#.csv

SELECT
ID ||';'||
AGENCY_ID ||';'||
CREATED_BY ||';'||
CREATED_DATE ||';'||
UPDATED_BY ||';'||
UPDATED_DATE ||';'||
GROUP_TYPE ||';'||
QUOTE_REQUEST ||';'||
QUOTE_STATE ||';'||
EXPIRES ||';'||
GROUP_NAME ||';'||
REFERENCE_CODE ||';'||
REQUEST_TYPE ||';'||
LOCK_VERSION ||';'||
GROUP_ID ||';'||
TRAVEL_REASON ||';'||
TRIP_TYPE ||';'||
PARENT_ID ||';'||
QUOTE_DISC ||';'||
ADHOC_INDEX ||';'||
ORIGIN ||';'||
DESTINATION ||';'||
COMPARTMENT ||';'||
DEPARTURE_DATE ||';'||
RETURN_DATE ||';'||
GROUP_SIZE ||';'||
EVENT_NAME ||';'||
STATUS ||';'||
SERIES_PATTERN_ID||';'||
REJECT_REASON ||';'||
ORIGINAL_OFFER_ID||';'||
FAILURE_CODE ||';'||
FAILURE_MESSAGE ||';'||
PNR_LOCATOR ||';'||
PNR_CREATION_DATE||';'||
INTERNATIONAL ||';'||
MAP_INBOUND_TAX_ITEMS||';'||
WORKFLOW_TYPE ||';'||
AUTH_CODE_ID ||';'||
QUOTE_PURPOSE ||';'||
GROUP_SIZE_MIN ||';'||
GROUP_SIZE_MAX ||';'||
ESCALATION_ID ||';'||
NUM_OF_CHANGES_ALLOWED FROM GSO_QUOTE;

spool off;


and Connor said...

I dont have your data, but I see nothing wrong with your script. eg, I did the following here 

set headsep on
set term off
set newpage 0
set space 0
set colsep ;
col name format a1024
set feedback off
set termout off
set heading on
set echo off
set pagesize 0 embedded on
set trimspool on
set linesize 10240
set numw 3
set escape #

spool c:\temp\xyz.lst

select 
TABLE_NAME||':'||
TABLESPACE_NAME||':'||
CLUSTER_NAME||':'||
IOT_NAME||':'||
STATUS||':'||
PCT_FREE||':'||
PCT_USED||':'||
INI_TRANS||':'||
MAX_TRANS||':'||
INITIAL_EXTENT||':'||
NEXT_EXTENT||':'||
MIN_EXTENTS||':'||
MAX_EXTENTS||':'||
PCT_INCREASE||':'||
FREELISTS||':'||
FREELIST_GROUPS||':'||
LOGGING||':'||
BACKED_UP||':'||
NUM_ROWS||':'||
BLOCKS||':'||
EMPTY_BLOCKS||':'||
AVG_SPACE||':'||
CHAIN_CNT||':'||
AVG_ROW_LEN||':'||
AVG_SPACE_FREELIST_BLOCKS||':'||
NUM_FREELIST_BLOCKS||':'||
DEGREE||':'||
INSTANCES||':'||
CACHE||':'||
TABLE_LOCK||':'||
SAMPLE_SIZE||':'||
LAST_ANALYZED||':'||
PARTITIONED||':'||
IOT_TYPE||':'||
TEMPORARY||':'||
SECONDARY||':'||
NESTED||':'||
BUFFER_POOL||':'||
FLASH_CACHE||':'||
CELL_FLASH_CACHE||':'||
ROW_MOVEMENT||':'||
GLOBAL_STATS||':'||
USER_STATS||':'||
DURATION||':'||
SKIP_CORRUPT||':'||
MONITORING||':'||
CLUSTER_OWNER||':'||
DEPENDENCIES||':'||
COMPRESSION||':'||
COMPRESS_FOR||':'||
DROPPED||':'||
READ_ONLY||':'||
SEGMENT_CREATED||':'||
RESULT_CACHE
from user_tables;

spool off


and the output was definitely more then 3 columns

TABLE_NAME||':'||TABLESPACE_NAME||':'||CLUSTER_NAME||':'||IOT_NAME||':'||STATUS||':'||PCT_FREE||':'||PCT_USED||':'||INI_TRANS||':'||MAX_TRANS||':'||INITIAL_EXTENT||':'||NEXT_EXTENT||':'||MIN_EXTENTS|
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORDERS:USERS:::VALID:10::1:255:65536:1048576:1:2147483645::::YES:N:20:5:0:0:0:27:0:0:         1:         1:    N:ENABLED:20:11-MAR-16:NO::N:N:NO:DEFAULT:DEFAULT:DEFAULT:DISABLED:YES:NO::DISABLED:YES::DISABLED:DISABLED::NO:NO:YES:DEFAULT
PARTS:USERS:::VALID:10::1:255:65536:1048576:1:2147483645::::YES:N:9991:202:0:0:0:132:0:0:         1:         1:    N:ENABLED:9991:15-MAR-16:NO::N:N:NO:DEFAULT:DEFAULT:DEFAULT:DISABLED:YES:NO::DISABLED:YES::DISABLED:DISABLED::NO:NO:YES:DEFAULT
MLOG$_PARTS:USERS:::VALID:10::1:255:65536:1048576:1:2147483645::::YES:N:::::::::         1:         1:    N:ENABLED:::NO::N:N:NO:DEFAULT:DEFAULT:DEFAULT:DISABLED:NO:NO::DISABLED:YES::DISABLED:DISABLED::NO:NO:YES:DEFAULT
TEST_VARRAY:USERS:::VALID:10::1:255:65536:1048576:1:2147483645::::YES:N:4:5:0:0:0:13:0:0:         1:         1:    N:ENABLED:4:07-APR-16:NO::N:N:NO:DEFAULT:DEFAULT:DEFAULT:DISABLED:YES:NO::DISABLED:YES::DISABLED:DISABLED::NO:NO:YES:DEFAULT
EMP:USERS:::VALID:10::1:255:65536:1048576:1:2147483645::::YES:N:14:5:0:0:0:37:0:0:         1:         1:    N:ENABLED:14:15-APR-16:NO::N:N:NO:DEFAULT:DEFAULT:DEFAULT:DISABLED:YES:NO::DISABLED:YES::DISABLED:DISABLED::NO:NO:YES:DEFAULT
FINANCIAL_RESULTS:USERS:::VALID:10::1:255::::::::YES:N:0:0:0:0:0:0:0:0:         1:         1:    N:ENABLED:0:12-MAY-16:NO::N:N:NO:DEFAULT:DEFAULT:DEFAULT:DISABLED:YES:NO::DISABLED:YES::DISABLED:DISABLED::NO:NO:NO:DEFAULT
ACCOUNT_EXTRACT:USERS:::VALID:10::1:255::::::::YES:N:0:0:0:0:0:0:0:0:         1:         1:    N:ENABLED:0:12-MAY-16:NO::N:N:NO:DEFAULT:DEFAULT:DEFAULT:DISABLED:YES:NO::DISABLED:YES::DISABLED:DISABLED::NO:NO:NO:DEFAULT
LOCAL_BLOB_COPY:USERS:::VALID:10::1:255:65536:1048576:1:2147483645::::YES:N:1:4:0:0:0:244:0:0:         1:         1:    N:ENABLED:1:30-JUN-16:NO::N:N:NO:DEFAULT:DEFAULT:DEFAULT:DISABLED:YES:NO::DISABLED:YES::DISABLED:DISABLED::NO:NO:YES:DEFAULT
...
...


so my guess is that you've got a data issue.

Try doing a dump() around your entire string and see if anything (chr(0) etc) could be messing things up.

Hope this helps.

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here