Home » SQL & PL/SQL » Client Tools » display clob column in text file in sqlplus (oracle 10.2.0.4.0 - 64bi linux)
display clob column in text file in sqlplus [message #549390] Fri, 30 March 2012 01:47 Go to next message
holdingbe
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
Re: display clob column in text file in sqlplus [message #549393 is a reply to message #549390] Fri, 30 March 2012 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 57605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
set trimspool on

Regards
Michel
Re: display clob column in text file in sqlplus [message #549396 is a reply to message #549393] Fri, 30 March 2012 02:25 Go to previous message
holdingbe
Messages: 235
Registered: October 2007
Location: Chennai
Senior Member

You are great thaliva...
Previous Topic: create database on server
Next Topic: Insert XML with &gt;
Goto Forum:
  


Current Time: Thu Apr 17 05:51:13 CDT 2014

Total time taken to generate the page: 0.11492 seconds