display clob column in text file in sqlplus [message #549390] |
Fri, 30 March 2012 01:47 |
sundarfaq
Messages: 235 Registered: October 2007 Location: Chennai
|
Senior Member |
|
|
Hi,
I have extracted data from table and write into one text via sqlplus utility in shell scripts. i got correct output. i am having two issues on the output file
1) Outfile file size is huge high compare then table segment data.
2) last column having extra space.
The output column is clob datatype. so i have added set long 50000 and set longchunksize 50000 parameter. after adding these only i got above issues. without two options, i am not getting this isssue but lines are wrapped.
#Set the scripts Path
SCRIPTS_PATH="/usr/local/ccms/gpa/svr/scripts"
echo $SCRIPTS_PATH
#Executes SVR parameter scripts Sets all the application specific properties
. $SCRIPTS_PATH/svrparam.sh
$ORACLE_HOME/bin/sqlplus -s /nolog >/dev/null <<EOF
set feedback off
set echo off
set pagesize 0
set linesize 4000
set space 0
set serveroutput on
spool ${SVR_LOG_PATH}/solv_outbound_files.txt
connect $SOLV_DB_USER/$SOLV_DB_PASS@$SOLV_DB_SERVER
SELECT view_name||'|'||file_name||'|'||type_of_file
FROM client_outbound_files
WHERE active_ind = 'Y';
"svr_outbound_files.sh" 118 lines, 2529 characters
else
column_name="hol_footer"
fi
echo "$file_type"
echo "column name $column_name"
echo $now
echo $now_mi
$ORACLE_HOME/bin/sqlplus -s /nolog >/dev/null <<EOF
set feedback off
set echo off
set pagesize 0
set linesize 32767
set long 50000
set longchunksize 50000
set space 0
set serveroutput on
spool ${OUTBOUND_FILE_PATH}/${file_name}_${now}_${now_mi}.csv
connect $SOLV_DB_USER/$SOLV_DB_PASS@$SOLV_DB_SERVER
SELECT outbound_column
FROM $view_name;
spool off
EOF
done < ${SVR_LOG_PATH}/solv_outbound_files.txt
.
Please give an idea about it
|
|
|
|
|