Forum Stats

  • 3,871,982 Users
  • 2,266,360 Discussions
  • 7,911,018 Comments

Discussions

export data from oracle forms 10g to excel

malikadeel
malikadeel Member Posts: 86 Red Ribbon
edited Sep 2, 2019 4:54PM in SQL & PL/SQL

I am trying to Export data from Oracle forms to excel on client side. I have a button on which i am calling below code but my all data goes to one columns.

PROCEDURE Create_Excel_File(cSQL in varchar2)

Is

   source_cursor    Integer;

   l_colCnt         Number            Default 0;

   l_descTbl        Dbms_sql.desc_tab;

   newval1          Varchar2 (4000);

   application      CLIENT_OLE2.OBJ_TYPE;

   workbooks        CLIENT_OLE2.OBJ_TYPE;

   workbook         CLIENT_OLE2.OBJ_TYPE;

   worksheets       CLIENT_OLE2.OBJ_TYPE;

   worksheet        CLIENT_OLE2.OBJ_TYPE;

   colour           CLIENT_OLE2.OBJ_TYPE;

   cell             CLIENT_OLE2.OBJ_TYPE;

   RANGE            CLIENT_OLE2.OBJ_TYPE;

   range_col        CLIENT_OLE2.OBJ_TYPE;

   range_row        CLIENT_OLE2.OBJ_TYPE;

   args             CLIENT_OLE2.LIST_TYPE;

   rows_processed   Number;

   row_n            Number;

   VAL              Varchar2 (100);

   x                Number;

   filename         Varchar2 (200);

BEGIN

message('inside');

BEGIN         

message('inside2');

      source_cursor := Dbms_Sql.open_Cursor;

      Dbms_Sql.parse (source_cursor, cSql, 2);

      Dbms_Sql.describe_Columns (c => source_cursor, col_cnt => l_colCnt, desc_t => l_descTbl);

   EXCEPTION

      When Others

      Then

         message (SQLERRM);

         RETURN;

   END;

   application := CLIENT_OLE2.CREATE_OBJ ('Excel.Application');

   CLIENT_OLE2.SET_PROPERTY (application, 'Visible', 'False');

   workbooks := CLIENT_OLE2.GET_OBJ_PROPERTY (application, 'Workbooks');

   workbook := CLIENT_OLE2.GET_OBJ_PROPERTY (workbooks, 'Add');

   worksheets := CLIENT_OLE2.GET_OBJ_PROPERTY (workbook, 'Worksheets');

   args := CLIENT_OLE2.CREATE_ARGLIST;

   CLIENT_OLE2.ADD_ARG (args, 1);

   worksheet := CLIENT_OLE2.GET_OBJ_PROPERTY (worksheets, 'Item', args);

   CLIENT_OLE2.DESTROY_ARGLIST (args);

   For T In 1 .. l_colCnt

   LOOP

      BEGIN

         Dbms_Sql.define_Column (source_cursor, T, newval1, 4000);

         args := CLIENT_OLE2.CREATE_ARGLIST;

         CLIENT_OLE2.ADD_ARG (args, 1);

         CLIENT_OLE2.ADD_ARG (args, T);                                                      --Next column

         cell := CLIENT_OLE2.GET_OBJ_PROPERTY (worksheet, 'Cells', args);

         CLIENT_OLE2.DESTROY_ARGLIST (args);

         colour := CLIENT_OLE2.get_obj_property (cell, 'Borders');

         CLIENT_OLE2.set_property (colour, 'ColorIndex', 1);

         CLIENT_OLE2.Release_obj (colour);

         colour := CLIENT_OLE2.get_obj_property (cell, 'Interior');

         CLIENT_OLE2.set_property (colour, 'ColorIndex', 15);

         CLIENT_OLE2.Release_obj (colour);

         CLIENT_OLE2.SET_PROPERTY (cell, 'Value', l_descTbl (T).col_name);

         CLIENT_OLE2.Release_obj (cell);

      EXCEPTION

         When Others

         Then

            Null;

      END;

   END LOOP;

   Rows_processed := Dbms_Sql.EXECUTE (source_cursor);

   row_n := 1;

   LOOP

      IF Dbms_Sql.fetch_Rows (source_cursor) > 0

      Then

         For T In 1 .. l_colCnt

         LOOP

            BEGIN

               Dbms_Sql.column_Value (source_cursor, T, newval1);

               args := CLIENT_OLE2.CREATE_ARGLIST;

               CLIENT_OLE2.ADD_ARG (args, row_n + 1);

               CLIENT_OLE2.ADD_ARG (args, T);                                                --Next column

               cell := CLIENT_OLE2.GET_OBJ_PROPERTY (worksheet, 'Cells', args);

               CLIENT_OLE2.DESTROY_ARGLIST (args);

               colour := CLIENT_OLE2.get_obj_property (cell, 'Borders');

               CLIENT_OLE2.set_property (colour, 'ColorIndex', 1);

               CLIENT_OLE2.Release_obj (colour);

               CLIENT_OLE2.SET_PROPERTY (cell, 'Value', newval1);

               CLIENT_OLE2.Release_obj (cell);

            EXCEPTION

               When Others

               Then

                  EXIT;

            END;

            newval1 := Null;

         END LOOP;

      Else

         EXIT;

      END IF;

      row_n := row_n + 1;

   END LOOP;

   Dbms_Sql.close_Cursor (source_cursor);

   -- Autofit columns

   RANGE := CLIENT_OLE2.GET_OBJ_PROPERTY (worksheet, 'UsedRange');

   range_col := CLIENT_OLE2.GET_OBJ_PROPERTY (RANGE, 'Columns');

   range_row := CLIENT_OLE2.GET_OBJ_PROPERTY (RANGE, 'Rows');

   CLIENT_OLE2.INVOKE (range_col, 'AutoFit');

   CLIENT_OLE2.INVOKE (range_row, 'AutoFit');

   CLIENT_OLE2.Release_obj (RANGE);

   CLIENT_OLE2.Release_obj (range_col);

   CLIENT_OLE2.Release_obj (range_row);

   -- Get filename and path

   filename :=  'NOCF_FC_UNPROCESSABLE_ERRORS.xls';

   -- Save as worksheet

   IF Nvl (filename, '0') <> '0'

   Then

      CLIENT_OLE2.SET_PROPERTY (application, 'Visible', 'True');

      args := CLIENT_OLE2.CREATE_ARGLIST;

      CLIENT_OLE2.ADD_ARG (args, filename);

      --CLIENT_OLE2.INVOKE (worksheet, 'SaveAs', args);

      CLIENT_OLE2.DESTROY_ARGLIST (args);

   END IF;

  -- CLIENT_OLE2.INVOKE( workbook ,'Close');

   CLIENT_OLE2.Release_obj (worksheet);

   CLIENT_OLE2.Release_obj (worksheets);

   CLIENT_OLE2.Release_obj (workbook);

   CLIENT_OLE2.Release_obj (workbooks);

   CLIENT_OLE2.Release_obj (application);

END;

Please review the above code suggest the solutions

Tagged:
malikadeel

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Sep 2, 2019 3:23PM
    malikadeel wrote:I am trying to Export data from Oracle forms to excel on client side. I have a button on which i am calling below code but my all data goes to one columns.PROCEDURE Create_Excel_File(cSQL in varchar2)Is source_cursor Integer; l_colCnt Number Default 0; l_descTbl Dbms_sql.desc_tab; newval1 Varchar2 (4000); application CLIENT_OLE2.OBJ_TYPE; workbooks CLIENT_OLE2.OBJ_TYPE; workbook CLIENT_OLE2.OBJ_TYPE; worksheets CLIENT_OLE2.OBJ_TYPE; worksheet CLIENT_OLE2.OBJ_TYPE; colour CLIENT_OLE2.OBJ_TYPE; cell CLIENT_OLE2.OBJ_TYPE; RANGE CLIENT_OLE2.OBJ_TYPE; range_col CLIENT_OLE2.OBJ_TYPE; range_row CLIENT_OLE2.OBJ_TYPE; args CLIENT_OLE2.LIST_TYPE; rows_processed Number; row_n Number; VAL Varchar2 (100); x Number; filename Varchar2 (200);BEGINmessage('inside');BEGIN message('inside2'); source_cursor := Dbms_Sql.open_Cursor; Dbms_Sql.parse (source_cursor, cSql, 2); Dbms_Sql.describe_Columns (c => source_cursor, col_cnt => l_colCnt, desc_t => l_descTbl); EXCEPTION When Others Then message (SQLERRM); RETURN; END; application := CLIENT_OLE2.CREATE_OBJ ('Excel.Application'); CLIENT_OLE2.SET_PROPERTY (application, 'Visible', 'False'); workbooks := CLIENT_OLE2.GET_OBJ_PROPERTY (application, 'Workbooks'); workbook := CLIENT_OLE2.GET_OBJ_PROPERTY (workbooks, 'Add'); worksheets := CLIENT_OLE2.GET_OBJ_PROPERTY (workbook, 'Worksheets'); args := CLIENT_OLE2.CREATE_ARGLIST; CLIENT_OLE2.ADD_ARG (args, 1); worksheet := CLIENT_OLE2.GET_OBJ_PROPERTY (worksheets, 'Item', args); CLIENT_OLE2.DESTROY_ARGLIST (args); For T In 1 .. l_colCnt LOOP BEGIN Dbms_Sql.define_Column (source_cursor, T, newval1, 4000); args := CLIENT_OLE2.CREATE_ARGLIST; CLIENT_OLE2.ADD_ARG (args, 1); CLIENT_OLE2.ADD_ARG (args, T); --Next column cell := CLIENT_OLE2.GET_OBJ_PROPERTY (worksheet, 'Cells', args); CLIENT_OLE2.DESTROY_ARGLIST (args); colour := CLIENT_OLE2.get_obj_property (cell, 'Borders'); CLIENT_OLE2.set_property (colour, 'ColorIndex', 1); CLIENT_OLE2.Release_obj (colour); colour := CLIENT_OLE2.get_obj_property (cell, 'Interior'); CLIENT_OLE2.set_property (colour, 'ColorIndex', 15); CLIENT_OLE2.Release_obj (colour); CLIENT_OLE2.SET_PROPERTY (cell, 'Value', l_descTbl (T).col_name); CLIENT_OLE2.Release_obj (cell); EXCEPTION When Others Then Null; END; END LOOP; Rows_processed := Dbms_Sql.EXECUTE (source_cursor); row_n := 1; LOOP IF Dbms_Sql.fetch_Rows (source_cursor) > 0 Then For T In 1 .. l_colCnt LOOP BEGIN Dbms_Sql.column_Value (source_cursor, T, newval1); args := CLIENT_OLE2.CREATE_ARGLIST; CLIENT_OLE2.ADD_ARG (args, row_n + 1); CLIENT_OLE2.ADD_ARG (args, T); --Next column cell := CLIENT_OLE2.GET_OBJ_PROPERTY (worksheet, 'Cells', args); CLIENT_OLE2.DESTROY_ARGLIST (args); colour := CLIENT_OLE2.get_obj_property (cell, 'Borders'); CLIENT_OLE2.set_property (colour, 'ColorIndex', 1); CLIENT_OLE2.Release_obj (colour); CLIENT_OLE2.SET_PROPERTY (cell, 'Value', newval1); CLIENT_OLE2.Release_obj (cell); EXCEPTION When Others Then EXIT; END; newval1 := Null; END LOOP; Else EXIT; END IF; row_n := row_n + 1; END LOOP; Dbms_Sql.close_Cursor (source_cursor); -- Autofit columns RANGE := CLIENT_OLE2.GET_OBJ_PROPERTY (worksheet, 'UsedRange'); range_col := CLIENT_OLE2.GET_OBJ_PROPERTY (RANGE, 'Columns'); range_row := CLIENT_OLE2.GET_OBJ_PROPERTY (RANGE, 'Rows'); CLIENT_OLE2.INVOKE (range_col, 'AutoFit'); CLIENT_OLE2.INVOKE (range_row, 'AutoFit'); CLIENT_OLE2.Release_obj (RANGE); CLIENT_OLE2.Release_obj (range_col); CLIENT_OLE2.Release_obj (range_row); -- Get filename and path filename := 'NOCF_FC_UNPROCESSABLE_ERRORS.xls'; -- Save as worksheet IF Nvl (filename, '0') <> '0' Then CLIENT_OLE2.SET_PROPERTY (application, 'Visible', 'True'); args := CLIENT_OLE2.CREATE_ARGLIST; CLIENT_OLE2.ADD_ARG (args, filename); --CLIENT_OLE2.INVOKE (worksheet, 'SaveAs', args); CLIENT_OLE2.DESTROY_ARGLIST (args); END IF; -- CLIENT_OLE2.INVOKE( workbook ,'Close'); CLIENT_OLE2.Release_obj (worksheet); CLIENT_OLE2.Release_obj (worksheets); CLIENT_OLE2.Release_obj (workbook); CLIENT_OLE2.Release_obj (workbooks); CLIENT_OLE2.Release_obj (application);END;Please review the above code suggest the solutions

    All THREE of the EXCEPTION handlers are flawed & BUGS that just need to be removed, deleted, & never used ever again anywhere.

    click on URL to learn why

    http://www.orafaq.com/wiki/WHEN_OTHERS

    Show us using COPY & PASTE what results after eliminating the obvious bugs.

    Excel has the capability to issue SQL directly against Oracle table & obtain the result set.

    Click on URL below

    How do I read or write an Excel file?

    malikadeelmalikadeel
  • malikadeel
    malikadeel Member Posts: 86 Red Ribbon
    edited Sep 2, 2019 4:54PM

    Hi John

    Thanks for Update , Code doesn't showing any exception if i Put sqlerrm it doesn't raise any things however data is exported to excel in single columns that is the issue in code

    THanks

    ADeel