oracle – how to dump query output into csv file via cli [solved]

A way to do it is to use sqlplus script.

  1. create file to execute with below content (vi my_query.sql):
    SET ECHO OFF;
    SET LINESIZE 32767;
    SET TAB OFF;
    SET NEWPAGE NONE;
    SET PAGESIZE 32767;
    SET LONG 100000000;
    SET HEADING ON;
    SET WRAP OFF;
    SET SCAN OFF;
    SET FEEDBACK OFF;
    SET SERVEROUTPUT ON;
    SET HEADSEP OFF;
    SET PAGESIZE 0;
    SET TRIMSPOOL ON;
    SET COLSEP ',';
    ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDD';
    
    SPOOL /path/to/my_query.out
    
    <paste_sql_query_here>
    
    COMMIT;
    EXIT;
  2. call sqlplus as per below:
    $ORACLE_HOME/bin/sqlplus -S -l <user/password@server> @<path/to/my_query.sql>

That’s it!

Ouptut of run should be available under /path/to/my_query.out as specified in SPOOL variable.

Tip: run SPOOL OFF; to turn off writing to disk

sources

Post a Comment

Your email is never published nor shared. You're allow to say what you want...


Warning: fsockopen() [function.fsockopen]: php_network_getaddresses: getaddrinfo failed: Name or service not known in /home/remydamo/websites/qc4blog/www/wp-content/plugins/sweetcaptcha-revolutionary-free-captcha-service/library/sweetcaptcha.php on line 81

Warning: fsockopen() [function.fsockopen]: unable to connect to www.sweetcaptcha.com:80 (php_network_getaddresses: getaddrinfo failed: Name or service not known) in /home/remydamo/websites/qc4blog/www/wp-content/plugins/sweetcaptcha-revolutionary-free-captcha-service/library/sweetcaptcha.php on line 81