Forum Stats

  • 3,769,695 Users
  • 2,253,009 Discussions
  • 7,875,155 Comments

Discussions

Generating excel(xls) using plsql

Prashant Dabral
Prashant Dabral Member Posts: 858 Silver Badge
edited Sep 20, 2013 4:03AM in SQL & PL/SQL
Hi,

I have a requirement in which my procedure is dumping data into a final table TBL_REPORT, Now data inside this table has to be shared with end users in excel (xls) format.
Is there any utility file or plsql program which can fulfill this requirement, guidance will be much appreciated.

version Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit PL/SQL Release 11.2.0.3.0+

Thanks in advance,
Prashant

Edited by: Prashant on May 28, 2013 6:02 PM
Mark Cooperuser11972374Tamir Lazover2995730Prashant DabralMigs_IsipSrini.Ramanujam

Best Answer

«1

Answers

  • Paul  Horth
    Paul Horth Member Posts: 3,402 Gold Trophy
    Prashant wrote:
    Hi,

    I have a requirement in which my procedure is dumping data into a final table TBL_REPORT, Now data inside this table has to be shared with end users in excel (xls) format.
    Is there any utility file or plsql program which can fulfill this requirement, guidance will be much appreciated.

    version Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit PL/SQL Release 11.2.0.3.0+

    Thanks in advance,
    Prashant

    Edited by: Prashant on May 28, 2013 6:02 PM
    There's a wealth of information in the FAQ.

    Read {message:id=9360007}
  • Prashant Dabral
    Prashant Dabral Member Posts: 858 Silver Badge
    Hi KPR,

    Thanks for showing interest. got below mentioned errors while executing.
    Any possible fixes.

    ORA-29282: invalid file ID
    ORA-06512: at "SYS.UTL_FILE", line 878
    ORA-06512: at "Local Schema .START_WORKBOOK", line 4
    ORA-06512: at line 7

    Regards,
    Prashant
  • rahulras
    rahulras Member Posts: 876
    All end-users like Excel. The PL/SQL solution given above is ideal. However...

    I use to give users CSV (comma delimited ) files, which normally open in MS-Excel without doing anything. My users were pretty happy with that, for one more reason, ".csv" file works/opens the same across any version of Excel.
    Generating csv file is very easy and it can be done in many-many ways. My prefered way was/is,
    sqlplus username/[email protected]
    .....
    SQL> set colsep ,
    SQL> set lines 500 pages 0 trimspool on
    SQL> set heading off   --- or keep this ON if you need/want it
    SQL> spool file_name.csv
    SQL> select * from tablename ;
    SQL> spool off
    "set colsep ," will change the default column saperator to comma instead of space.

    You can also write PL/SQL which will UTL_FILE or DBMS_OUTPUT.PUT_LINE comma saperated lines.
  • BluShadow
    BluShadow Member, Moderator Posts: 41,491 Red Diamond
    Methods for writing CSV (character seperated values) were already included in the link that Paul provided above.

    The problem with CSV is that it does not allow any formatting to be included in the data, and only allows a single spreadsheet to be generated.
    The advantage of CSV is that it's not just something that can be read by Excel, it is compatible with many tools, due to it's simplistic nature.
  • Prashant Dabral
    Prashant Dabral Member Posts: 858 Silver Badge
    Hi KPR,


    I have modified code as in below mentioned procedures.
    and it got successfully completed. but Excel file is blank.


    CREATE OR REPLACE PROCEDURE start_workbook IS
    v_fh UTL_FILE.FILE_TYPE;
    V_DIR VARCHAR2(30) := 'DIR_NAME';
    V_FILE VARCHAR2(30) := 'MyExcel.xls';
    BEGIN
    v_fh := UTL_FILE.FOPEN(upper(v_dir),v_file,'w');
    UTL_FILE.PUT_LINE(v_fh,'<?xml version="1.0"?>');
    UTL_FILE.PUT_LINE(v_fh,'<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">');
    END;
    /

    CREATE OR REPLACE PROCEDURE set_date_style IS
    v_fh UTL_FILE.FILE_TYPE;
    V_DIR VARCHAR2(30) := 'DIR_NAME';
    V_FILE VARCHAR2(30) := 'MyExcel.xls';
    BEGIN
    v_fh := UTL_FILE.FOPEN(upper(v_dir),v_file,'w');
    UTL_FILE.PUT_LINE(v_fh,'<ss:Styles>');
    UTL_FILE.PUT_LINE(v_fh,'<ss:Style ss:ID="OracleDate">');
    UTL_FILE.PUT_LINE(v_fh,'<ss:NumberFormat ss:Format="dd/mm/yyyy\ hh:mm:ss"/>');
    UTL_FILE.PUT_LINE(v_fh,'</ss:Style>');
    UTL_FILE.PUT_LINE(v_fh,'</ss:Styles>');
    END;
    /

    CREATE OR REPLACE PROCEDURE start_worksheet(p_sheetname IN VARCHAR2) IS
    v_fh UTL_FILE.FILE_TYPE;
    V_DIR VARCHAR2(30) := 'DIR_NAME';
    V_FILE VARCHAR2(30) := 'MyExcel.xls';
    BEGIN
    v_fh := UTL_FILE.FOPEN(upper(v_dir),v_file,'w');
    UTL_FILE.PUT_LINE(v_fh,'<ss:Worksheet ss:Name="'||p_sheetname||'">');
    UTL_FILE.PUT_LINE(v_fh,'<ss:Table>');
    END;
    /

    CREATE OR REPLACE PROCEDURE run_query(p_sql IN VARCHAR2) IS
    v_v_val VARCHAR2(4000);
    v_n_val NUMBER;
    v_d_val DATE;
    v_ret NUMBER;
    c NUMBER;
    d NUMBER;
    col_cnt INTEGER;
    f BOOLEAN;
    rec_tab DBMS_SQL.DESC_TAB;
    col_num NUMBER;
    v_fh UTL_FILE.FILE_TYPE;
    v_dir VARCHAR2(30) := 'DIR_NAME';
    V_FILE VARCHAR2(30) := 'MyExcel.xls';
    BEGIN
    v_fh := UTL_FILE.FOPEN(upper(v_dir),v_file,'w');

    c := DBMS_SQL.OPEN_CURSOR;
    -- parse the SQL statement
    DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
    -- start execution of the SQL statement
    d := DBMS_SQL.EXECUTE(c);
    -- get a description of the returned columns
    DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
    -- bind variables to columns
    FOR j in 1..col_cnt
    LOOP
    CASE rec_tab(j).col_type
    WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,4000);
    WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
    WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
    ELSE
    DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,4000);
    END CASE;
    END LOOP;
    -- Output the column headers
    UTL_FILE.PUT_LINE(v_fh,'<ss:Row>');
    FOR j in 1..col_cnt
    LOOP
    UTL_FILE.PUT_LINE(v_fh,'<ss:Cell>');
    UTL_FILE.PUT_LINE(v_fh,'<ss:Data ss:Type="String">'||rec_tab(j).col_name||'</ss:Data>');
    UTL_FILE.PUT_LINE(v_fh,'</ss:Cell>');
    END LOOP;
    UTL_FILE.PUT_LINE(v_fh,'</ss:Row>');
    -- Output the data
    LOOP
    v_ret := DBMS_SQL.FETCH_ROWS(c);
    EXIT WHEN v_ret = 0;
    UTL_FILE.PUT_LINE(v_fh,'<ss:Row>');
    FOR j in 1..col_cnt
    LOOP
    CASE rec_tab(j).col_type
    WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
    UTL_FILE.PUT_LINE(v_fh,'<ss:Cell>');
    UTL_FILE.PUT_LINE(v_fh,'<ss:Data ss:Type="String">'||v_v_val||'</ss:Data>');
    UTL_FILE.PUT_LINE(v_fh,'</ss:Cell>');
    WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
    UTL_FILE.PUT_LINE(v_fh,'<ss:Cell>');
    UTL_FILE.PUT_LINE(v_fh,'<ss:Data ss:Type="Number">'||to_char(v_n_val)||'</ss:Data>');
    UTL_FILE.PUT_LINE(v_fh,'</ss:Cell>');
    WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
    UTL_FILE.PUT_LINE(v_fh,'<ss:Cell ss:StyleID="OracleDate">');
    UTL_FILE.PUT_LINE(v_fh,'<ss:Data ss:Type="DateTime">'||to_char(v_d_val,'YYYY-MM-DD"T"HH24:MI:SS')||'</ss:Data>');
    UTL_FILE.PUT_LINE(v_fh,'</ss:Cell>');
    ELSE
    DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
    UTL_FILE.PUT_LINE(v_fh,'<ss:Cell>');
    UTL_FILE.PUT_LINE(v_fh,'<ss:Data ss:Type="String">'||v_v_val||'</ss:Data>');
    UTL_FILE.PUT_LINE(v_fh,'</ss:Cell>');
    END CASE;
    END LOOP;
    UTL_FILE.PUT_LINE(v_fh,'</ss:Row>');
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(c);
    END;
    /
    CREATE OR REPLACE PROCEDURE end_worksheet IS
    v_fh UTL_FILE.FILE_TYPE;
    v_dir VARCHAR2(30) := 'DIR_NAME';
    V_FILE VARCHAR2(30) := 'MyExcel.xls';
    BEGIN
    v_fh := UTL_FILE.FOPEN(upper(v_dir),v_file,'w');
    UTL_FILE.PUT_LINE(v_fh,'</ss:Table>');
    UTL_FILE.PUT_LINE(v_fh,'</ss:Worksheet>');
    END;
    /

    CREATE OR REPLACE DROP PROCEDURE end_workbook IS
    v_fh UTL_FILE.FILE_TYPE;
    v_dir VARCHAR2(30) := 'DIR_NAME';
    V_FILE VARCHAR2(30) := 'MyExcel.xls';
    BEGIN
    v_fh := UTL_FILE.FOPEN(upper(v_dir),v_file,'w');
    UTL_FILE.PUT_LINE(v_fh,'</ss:Workbook>');
    END;

    CREATE OR REPLACE PROCEDURE PR_UTIL
    AS
    V_DIR VARCHAR2(30) := 'ING_UMR';
    V_FILE VARCHAR2(30) := 'TEST_2.xls';
    V_FH UTL_FILE.FILE_TYPE;
    VC_EXP VARCHAR2(30);
    BEGIN

    VC_EXP := 'P0';
    v_fh := UTL_FILE.FOPEN(upper(v_dir),v_file,'w',32767);
    VC_EXP := 'P1';
    start_workbook;
    VC_EXP := 'P2';
    set_date_style;
    VC_EXP := 'P3';
    start_worksheet('EMP');
    VC_EXP := 'P4';
    run_query('select * from emp');
    VC_EXP := 'P5';
    end_worksheet;
    VC_EXP := 'P6';
    start_worksheet('DEPT');
    VC_EXP := 'P7';
    run_query('select * from dept');
    VC_EXP := 'P8';
    end_worksheet;
    VC_EXP := 'P9';
    end_workbook;
    VC_EXP := 'P10';
    UTL_FILE.FCLOSE(v_fh);
    VC_EXP := 'PS';
    DBMS_OUTPUT.PUT_LINE('SUCCESSFULLY COMPLETED');
    exception
    when others then
    DBMS_OUTPUT.PUT_LINE(VC_EXP||'-----'||SQLCODE||' '||SQLERRM);
    END;
    /



    12:45:01 SQL> EXEC PR_UTIL;
    SUCCESSFULLY COMPLETED

    PL/SQL procedure successfully completed.
  • Marwim
    Marwim Member Posts: 3,648 Gold Trophy
    but Excel file is blank.
    Do you mean, that it is blank when you open the file in Excel?
    Or ist the file empty?
    If the file is not empty: can you show us the content of the file?

    Regards
    Marcus
  • Prashant Dabral
    Prashant Dabral Member Posts: 858 Silver Badge
    edited Jun 5, 2013 7:36AM
    Hi KPR,

    This post is correctly answered by you and its much appreciated, there is no editing feature of marking this as correct!!

    Regards,
    Prashant.

    Edited by: Prashant on Jun 5, 2013 5:06 PM
  • BluShadow
    BluShadow Member, Moderator Posts: 41,491 Red Diamond
    Prashant wrote:
    Hi KPR,

    This post is correctly answered by you and its much appreciated, there is no editing feature of marking this as correct!!
    Correct. Once points are awarded they cannot be removed or re-allocated. That's why it's always a good idea to ensure you're definitely happy you've got the correct answer before marking posts.
  • 968681
    968681 Member Posts: 23
    edited Aug 30, 2013 7:33AM

    Hi KPR,

    It's really very helpful, Thanks for the code.

    But i am struck when client asked for a particular format of the data to be displayed in first spreadsheet of the Excel.

    I am able to get the XML code from the Excel but how to Implement that and where to add those XML Tags in the Procedure which you given above.

    can you please give me some idea.......

    Thanks & Regards

    Vamsi Krishna

This discussion has been closed.