1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

export excel automatically

Discussion in 'SQL PL/SQL' started by vijayspecial, Nov 14, 2013.

  1. vijayspecial

    vijayspecial Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Canyou please give some idea how to export an outcome of query result in a excel sheet..

    for example
    select * from load;

    output:

    A B
    -------
    1 s
    2 f
    3 g
    4 o

    After scheduling a job the output of the table should export as a excel.

    can you please give some idea to implement it.
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    There is a lot of options and ideas....
    In total envy from specific requirements and statements of the problem, architecture of your application.

    How the task is set to you?
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    It is a simple example of generation of xml on sql to request in pl/sql....

    Code (SQL):


    SET serveroutput ON
    DECLARE
      i dbms_xmlgen.ctxHandle;
      xm CLOB;
      sql_txt varchar2(32767) := q'{select 1 as a, 's' as b from dual union all
                                    select 2     , '
    f'      from dual union all
                                    select 3     , '
    g'      from dual union all
                                    select 4     , '
    o'      from dual
    }'
    ;
    BEGIN
      -- Test statements here
      dbms_output.put_line('Print text query : '||chr(10)||sql_txt);
      dbms_lob.createtemporary(lob_loc => xm,cache => TRUE);
      i:=dbms_xmlgen.newContext(sql_txt);
      DBMS_XMLGEN.setRowSetTag (i,'ROWS_AB');
      DBMS_XMLGEN.SETROWTAG (i,'ROW_AB');

      dbms_xmlgen.getXML(i,xm);
        -- only this  exmpale
      dbms_output.put_line ('XML is '||chr(10)||xm);  
        -- only this  exmpale  
      DBMS_XMLGEN.CLOSECONTEXT (i);
    END;
    /


    SQL>
     
    Print text query :
    SELECT 1 AS a, 's' AS b FROM dual UNION ALL
                                    SELECT 2     , 'f'      FROM dual UNION ALL
                                    SELECT 3     , 'g'      FROM dual UNION ALL
                                    SELECT 4     , 'o'      FROM dual

    XML IS
    <?xml version="1.0"?>
    <ROWS_AB>
     <ROW_AB>
      <A>1</A>
      <B>s</B>
     </ROW_AB>
     <ROW_AB>
      <A>2</A>
      <B>f</B>
     </ROW_AB>
     <ROW_AB>
      <A>3</A>
      <B>g</B>
     </ROW_AB>
     <ROW_AB>
      <A>4</A>
      <B>o</B>
     </ROW_AB>
    </ROWS_AB>

     
    PL/SQL PROCEDURE successfully completed


     
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Nice example but that isn't an Excell spreadsheet. :)

    Excel can accept flat files in CSV format:

    Code (SQL):
    SQL> SET colsep ","
    SQL> SET head off pagesize 0 feedback off
    SQL>
    SQL> SELECT * FROM emp;
          7369,SMITH     ,CLERK    ,      7902,17-DEC-80,       800,          ,        20
          7499,ALLEN     ,SALESMAN ,      7698,20-FEB-81,      1600,       300,        30
          7521,WARD      ,SALESMAN ,      7698,22-FEB-81,      1250,       500,        30
          7566,JONES     ,MANAGER  ,      7839,02-APR-81,      2975,          ,        20
          7654,MARTIN    ,SALESMAN ,      7698,28-SEP-81,      1250,      1400,        30
          7698,BLAKE     ,MANAGER  ,      7839,01-MAY-81,      2850,          ,        30
          7782,CLARK     ,MANAGER  ,      7839,09-JUN-81,      2450,          ,        10
          7788,SCOTT     ,ANALYST  ,      7566,09-DEC-82,      3000,          ,        20
          7839,KING      ,PRESIDENT,          ,17-NOV-81,      5000,          ,        10
          7844,TURNER    ,SALESMAN ,      7698,08-SEP-81,      1500,         0,        30
          7876,ADAMS     ,CLERK    ,      7788,12-JAN-83,      1100,          ,        20
          7900,JAMES     ,CLERK    ,      7698,03-DEC-81,       950,          ,        30
          7902,FORD      ,ANALYST  ,      7566,03-DEC-81,      3000,          ,        20
          7934,MILLER    ,CLERK    ,      7782,23-JAN-82,      1300,          ,        10
    SQL>
    That will open in Excel; simply choose Delimited and select the Comma delimiter and you have a spreadsheet.
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    CSV is classics. it is a simple and good example. :)

    excell understands a format of xml-tables and if according to the job it is so necessary for it, the volume can generate in such format or using xslt transformation....

    oracle is able a lot of things...

    let vijayspecial selects the decision according to requirements for the task)))

    :)
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    And I have learned something today. I don't use Excel to format query output; it's nice to learn a new 'trick' for that.
     
  7. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    set colsep can be in fact a very handy tool in sql*plus and I've also used it before, however it seems to me that sqlplus inserts extra spaces between each pair of columns and depending on the context of the application which is supposed to read the generated file, these extra (non desired) spaces may cause problems (the trailing spaces become as part of the data) unless you eliminate them after opening with either Excel or any text editor allowing to remove those spaces.

    Therefore, personally I prefer to use the primitive concatenation method, that is concatenating the specific selected columns by the delimiter character:

    So, instead of writing
    Code (SQL):
    SQL> SET colsep ","
    SELECT   col1, col2, col3, col4, . . .
     
    I write
    Code (SQL):
    SELECT   col1 || ','  || col2 || ','  || col3  || ',' || col4 . . .
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Both work; my intent was to make this as easy as possible for the original poster to generate a CSV file through SQL*Plus. The "extra spaces" come from the default column widths set by the column definitions and resetting those to more 'reasonable' values based on actual data lengths cures that:

    Code (SQL):
    SQL> col empno format 9999
    SQL> col ename format a6
    SQL> col mgr format 9999
    SQL> col job format a9
    SQL>
    SQL> SELECT empno, ename, mgr, job
      2  FROM emp;
     7369,SMITH , 7902,CLERK
     7499,ALLEN , 7698,SALESMAN
     7521,WARD  , 7698,SALESMAN
     7566,JONES , 7839,MANAGER
     7654,MARTIN, 7698,SALESMAN
     7698,BLAKE , 7839,MANAGER
     7782,CLARK , 7839,MANAGER
     7788,SCOTT , 7566,ANALYST
     7839,KING  ,     ,PRESIDENT
     7844,TURNER, 7698,SALESMAN
     7876,ADAMS , 7788,CLERK
     7900,JAMES , 7698,CLERK
     7902,FORD  , 7566,ANALYST
     7934,MILLER, 7782,CLERK
    SQL>
    It's work, either way, so it's really up to the original poster which technique will be used.
     
  9. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Yes, of course, I didn't make the remark to oppose your suggested solution, but just to indicate that the problem in terms of extra spaces may emerge. Obviously, that will not be an issue if the COLUMN sql*plus command sets properly the length for each column. Indeed I forgot about that :p, thanks for the clarification.
     
  10. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi, All.

    1) output of the simple table which will understand excel

    Code (SQL):


    SET pagesize 0
    SET longchunksize 1000
    SET feedback off
    rem it IS necessary TO USE? uncomment
    rem spool  c:\sample_xls.xls    
    WITH
    sample_xls AS
        (
               SELECT  
                   ut.TABLE_NAME,
                   ut.TABLESPACE_NAME,
                   ut.STATUS,
                   to_char(ut.LAST_ANALYZED,'dd.mm.yyyy hh24:mi:ss') LASTANALYZED
               FROM user_tables ut
               WHERE rownum < 2
        )                                
    SELECT XmlElement("table", XmlAttributes(2 AS "border")
           , XmlAgg(
               XmlElement("tr"
                 , XmlElement("td",sx.TABLE_NAME)
                 , XmlElement("td",sx.TABLESPACE_NAME)
                 , XmlElement("td",sx.STATUS)
                 , XmlElement("td",sx.LASTANALYZED)
               )
             )
           )
    FROM  sample_xls sx;
    rem rem it IS necessary TO USE? uncomment
    rem spool off;                                


    SQL > <TABLE border="2"><tr><td>TEST_DI</td><td>LS_TBLSPC_DATA</td><td>VALID</td><td>07.11.2013 22:00:07</td></tr></table>

     
    This line of an output needs to be saved in the file with extension of xls and to open it in Excel.


    2)
    Code (SQL):


    SET pagesize 0
    SET serveroutput ON
    SET feedback off
    DECLARE
     rc sys_refcursor;
     doc DBMS_XMLDOM.DOMDocument;
     docc CLOB;
     BEGIN      
          EXECUTE immediate q'{alter session set NLS_DATE_FORMAT='dd.mm.yyyy hh24:mi:ss'}';
          dbms_lob.createtemporary (lob_loc => docc,cache => TRUE);      
          OPEN rc FOR SELECT * FROM ( SELECT
                                            ut.TABLE_NAME,ut.TABLESPACE_NAME,ut.LAST_ANALYZED
                                      FROM user_tables ut
                                      WHERE
                                           rownum < 10                                  
                                    );
          doc := DBMS_XMLDOM.NewDOMDocument(xmltype(rc));
          dbms_xmldom.writeToClob(doc,docc);
          dbms_output.put_line (docc);
          dbms_lob.freetemporary (lob_loc => docc);
          close rc;
     END;
    /

    SQL>
     
    <?xml version="1.0"?>
    <ROWSET>
      <ROW>
        <TABLE_NAME>TEST_DI</TABLE_NAME>
        <TABLESPACE_NAME>LS_TBLSPC_DATA</TABLESPACE_NAME>
        <LAST_ANALYZED>07.11.2013 22:00:07</LAST_ANALYZED>
      </ROW>
    </ROWSET>


     
    These lines of an output need to be saved in the file with extension of xls and to open it in Excel.




    native documentation : http://docs.oracle.com/cd/E11882_01/appdev.112/e16659/toc.htm