Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Generate Excel using Pl/SQL - Getting warning message

MN RajanJun 5 2017 — edited Jun 6 2017

I am able to generate Excel file using pl/sql successfully. But when i open it, it throws a warning message - "The file format and extension don't match. The file could be corrupt or unsafe. Unless you trust its source, don't open it. Do you want to open it anyway?"

Once i say - Yes, excel opens and i can see everything as i expect.  I tried in many ways with the help of google with unsuccessful. I need to get rid of this warning message. Any help much appreciated.

Oracle version - 11.2.0.3.0

Excel version - Office 2016

Sample code:

DECLARE

  v_fh        UTL_FILE.FILE_TYPE;

  v_dir       VARCHAR2(30) := 'EXCEL_DIR';

  v_file      VARCHAR2(30) := 'Excel_in_Oracle.xls';

  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;

  BEGIN

    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;

  --

  PROCEDURE start_workbook IS

  BEGIN

    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;

  PROCEDURE end_workbook IS

  BEGIN

    UTL_FILE.PUT_LINE(v_fh,'</ss:Workbook>');

  END;

  --

  PROCEDURE start_worksheet(p_sheetname IN VARCHAR2) IS

  BEGIN

    UTL_FILE.PUT_LINE(v_fh,'<ss:Worksheet ss:Name="'||p_sheetname||'">');

    UTL_FILE.PUT_LINE(v_fh,'<ss:Table>');

  END;

  PROCEDURE end_worksheet IS

  BEGIN

    UTL_FILE.PUT_LINE(v_fh,'</ss:Table>');

    UTL_FILE.PUT_LINE(v_fh,'</ss:Worksheet>');

  END;

  --

  PROCEDURE set_date_style IS

  BEGIN

    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;

BEGIN

  v_fh := UTL_FILE.FOPEN(upper(v_dir),v_file,'w',32767);

  start_workbook;

  set_date_style;

  start_worksheet('EMP');

  run_query('select * from emp');

  end_worksheet;

  end_workbook;

  UTL_FILE.FCLOSE(v_fh);

END;

This post has been answered by BluShadow on Jun 6 2017
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 4 2017
Added on Jun 5 2017
7 comments
1,157 views