Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2007
    Posts
    3

    Unanswered: Expoting data from oracle to CSV

    Hi.
    I want to export data from oracle to CSV file.

    Spool backup.csv;
    SET HEADING OFF;
    Select cellno,calldate,answertime from temp;
    Spool off;

    The problems i am facing are
    1) All the three fields are written into one column in backup.csv.Is there any option so that these fields can be written in separate columns in CSV file(like in mysql--> fields terminated by ",")

    2) After running "Spool backup.csv" command, all other commands are also
    written to csv file.Is there any option to avoid writting these lines to csv file.
    Regards
    Adnan

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    set echo off
    set feedback off
    set pagesize 0
    Spool backup.csv;
    Select cellno||','||calldate||','||answertime from temp;
    Spool off;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jul 2007
    Posts
    3

    it worked 50%

    Thanks.
    It worked 50% i.e problem # 1 is solved but problem # 2 is still there.

    Set echo off;
    still does not works.Commands are still written to the csv file.
    is ther any other way out?
    regards
    Adnan

  4. #4
    Join Date
    Jul 2007
    Posts
    4
    set heading off

    Try this too along with other set commands

    --Raja

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You have to store all the commands in a file and then run the script using the

    @myscript.sql

    If you type them in, they will be echoed to the screen.

    Raja,
    the "set pagesize 0" will suppress all the headings.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jul 2007
    Posts
    3

    Thanks it worked 100%

    Thanks alot beilstwh.
    it worked 100%.

    thanks to Raja also for replying.
    Have a good time.
    Bye

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Although you have solved the problem, leet me add a word or two.

    Instead of concatenating a separator (semi-column, as Bill showed in his example)
    Code:
    Select cellno||','||calldate ... from ...
    you could tell Oracle to do it for you using the SET COLSEP (column separator) command:
    Code:
    SQL> set colsep ";"
    SQL> set heading off
    SQL>
    SQL> select empno, ename, job, deptno
      2  from emp
      3  where deptno = 10;
    
          7782;CLARK     ;MANAGER  ;        10
          7839;KING      ;PRESIDENT;        10
          7934;MILLER    ;CLERK    ;        10
    
    SQL>

  8. #8
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Here is another solution.

    I adapted it to our internal code formatting standards (procedure and variable names are in french ) and added a first line with the column names thanks to DBMS_SQL :

    Code:
        FUNCTION Exporter(
            strRequete IN VARCHAR2,
            strSeparateur IN VARCHAR2 DEFAULT ';') RETURN VARCHAR2_TABLE PIPELINED IS
          
        ctx DBMS_XMLGEN.CTXHANDLE;
        rcResultat NUMBER;
        iRetour NUMBER;
        iNbColonnes INTEGER;
        recTable DBMS_SQL.DESC_TAB;
        strColonnes VARCHAR2(4000);
        
        BEGIN
        
            rcResultat := DBMS_SQL.OPEN_CURSOR();
            DBMS_SQL.PARSE(rcResultat, strRequete, DBMS_SQL.NATIVE);
            iRetour := DBMS_SQL.EXECUTE(rcResultat);
            DBMS_SQL.DESCRIBE_COLUMNS(rcResultat, iNbColonnes, recTable);
            
            FOR j IN 1..iNbColonnes LOOP
            
              strColonnes := strColonnes||recTable(j).col_name;
              
              IF (j < iNbColonnes) THEN
                  strColonnes := strColonnes||strSeparateur;
              END IF;
              
            END LOOP;
            
            DBMS_SQL.CLOSE_CURSOR(rcResultat);
            PIPE ROW(strColonnes);
            
            ctx := DBMS_XMLGEN.NEWCONTEXT(strRequete);
            DBMS_XMLGEN.SETNULLHANDLING(ctx, DBMS_XMLGEN.EMPTY_TAG);
            FOR Colonnes IN (
                SELECT 
                    CAST(
                        XMLTRANSFORM(
                            COLUMN_VALUE,
                            XMLTYPE('<xsl:stylesheet version="1.0" xmlns:xsl'||
                            '="http://www.w3.org/1999/XSL/Transform">'||
                            '<xsl:output method="text"/><xsl:variable name'||
                            '="new_line" select="''&#xA;''" /><xsl:template'||
                            ' match="ROWSET"><xsl:apply-templates select="'||
                            'ROW"/></xsl:template><xsl:template match="ROW"'||
                            '><xsl:for-each select="*"><xsl:value-of select'||
                            '="."/><xsl:if test="position() != last()"><xsl'||
                            ':value-of select="'''||strSeparateur||'''"/></xsl:if></xsl:for-'||
                            'each><xsl:value-of select="$new_line" /></xsl:'||
                            'template></xsl:stylesheet>'
                        )
                    ) AS VARCHAR2(4000)) ValeurColonne
                 FROM TABLE(XMLSEQUENCE(DBMS_XMLGEN.GETXMLTYPE(ctx).EXTRACT('ROWSET/ROW'))))
          
            LOOP
                PIPE ROW(Colonnes.ValeurColonne);
            END LOOP;
            
        END Exporter;
    VARCHAR2_TABLE is defined as TABLE OF VARCHAR2(4000).

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •