Forum Stats

  • 3,824,920 Users
  • 2,260,440 Discussions
  • 7,896,347 Comments

Discussions

How to Read an Excel file into an Oracle Form 12c

jawed31
jawed31 Member Posts: 26 Blue Ribbon

Hi All,

How to read an excel file into oracle forms 12c.

i have a procedure to read an excel file into oracle forms but it is not working.

  PROCEDURE load_excel_block IS


 app_id      PLS_INTEGER;

 str       VARCHAR2(200);

 doc_id      PLS_INTEGER;

 conv_id     PLS_INTEGER;

 buff       VARCHAR2(300);

 filename     VARCHAR2(2000);

 k        BINARY_INTEGER;

 crows      VARCHAR2(30);

 in_file     text_io.file_type;

 fromrow     NUMBER(5) := :blk1.from_row;

 torow      NUMBER(5) := :blk1.to_row;

 n_cnt      NUMBER;

 n_rec      NUMBER := 0;

 n_amount     NUMBER := 0;

 xls_path     VARCHAR2(1000);

 lc        VARCHAR2(6);

 buffer1     VARCHAR2(200);

 buffer2     VARCHAR2(200);

 buffer3     VARCHAR2(200);

 buffer4     VARCHAR2(200);

 buffer5     VARCHAR2(200);

 buffer5dt    DATE;

 buffer6     VARCHAR2(200);

 buffer7     VARCHAR2(200);

 buffer8     VARCHAR2(200);

 buffer9     VARCHAR2(200);

 buffer10     VARCHAR2(200);

 buffer11     VARCHAR2(200);

 buffer12     VARCHAR2(200);

 buffer13     VARCHAR2(200);

 buffer14     VARCHAR2(200);

 buffer15     VARCHAR2(200);


BEGIN

 fblock_plsqltab;

 n_cnt := fpg_plsql.dde_plsqltab.count;

 IF n_cnt > 0 THEN

  IF filename IS NULL THEN

   filename := client_get_file_name('',

                    file_filter => 'Excel Files (*.xls)|*.xls|');

   

   :nbt_blk1.file_name := '\' || filename;

  END IF;

  

  IF filename IS NOT NULL THEN

   :blk1.file_name := '\' || filename;

   app_id       := dde.app_begin(:blk1.excel_path,

                      dde.app_mode_minimized);

   

   conv_id := dde.initiate('EXCEL', 'SYSTEM');

   

   str := '[OPEN("' || filename || '"' || ')]';

   

   dde.execute(conv_id, str, 10000);

   

   doc_id := dde.initiate('EXCEL', filename);

   

   go_block('TEMP_BULK_CANCEL');

   

   FOR j IN fromrow .. torow LOOP

    n_rec         := n_rec + 1;

    :temp_bulk_claim.slno := n_rec;

    FOR k IN 1 .. n_cnt LOOP

     crows := 'R' || to_char(j) || fpg_plsql.dde_plsqltab(k).col_name;

     

     dde.request(doc_id, crows, buff, dde.cf_text, 1000);

     buff := substr(buff, 1, length(buff) - 2);

     copy(buff, ':' || fpg_plsql.dde_plsqltab(k).col_value);

    END LOOP;

    

    IF j < torow THEN

     create_record;

    ELSE

     next_record;

    END IF;

   END LOOP;

   

   first_record;

   dde.app_end(app_id);

   go_block('CTRL_XL');

  ELSE

   :blk1.excel_status := 1;

  END IF;

  

 END IF;


EXCEPTION

 When form_trigger_failure then

  :BLK1.EXCEL_STATUS := 1;

  raise form_trigger_failure;

  

 WHEN OTHERS THEN

  

  IF SQLCODE = -302000 THEN

   :BLK1.EXCEL_STATUS := 1;

   Msg_Alert('File is already opened or not selected ', 'I', false);

  ELSE

   :BLK1.EXCEL_STATUS := 1;

   Msg_Alert(' (Others) Error : ' || :BLK1.EXCEL_STATUS, 'I', false);

  END IF;

  

 --DDE.APP_END(APP_ID);


END;

Answers

  • Michael Ferrante-Oracle
    Michael Ferrante-Oracle Senior Principal Product Manager USMember Posts: 7,194 Employee

    Two comments:

    1 Using the old "dde" calls may not work.

    2 Even if the DDE calls worked, they would be executed on the server and not the user machine. To access Excel on the user machine you need to WebUtil enable your form(s).

    There are a variety of MyOracleSupport notes that share example code for using WebUtil to read/write to Excel. Similar code could be used to do similar on the server although removing the WebUtil specific parts would be necessary.


    Michael Ferrante

    Senior Principal Product Manager

    Oracle

    Twitter: @OracleFormsPM

  • jawed31
    jawed31 Member Posts: 26 Blue Ribbon

    removing the WebUtil specific parts?

    what are the WebUtil parts need to remove from the form(s)?

  • jawed31
    jawed31 Member Posts: 26 Blue Ribbon

    dde.execute(conv_id, str, 10000);

    the DDE.EXECUTE() procedure is not working. i put the message before and after DDE.EXECUTE() procedure but only before message is showing then it is going to the exception section.

    message('Before DDE_EXECUTE');

    message('Before DDE_EXECUTE');

    DDE.EXECUTE(CONV_ID,STR, 10000);

    message('After DDE_EXECUTE');

    message('After DDE_EXECUTE');

    EXCEPTION /* Exception section*/

    WHEN OTHERS THEN

     IF SQLCODE = -302000 THEN

       :BLK1.EXCEL_STATUS := 1;

       Msg_Alert('File is already opened or not selected ', 'I', false);

      ELSE

       :BLK1.EXCEL_STATUS := 1;

       Msg_Alert(' (Others) Error : ' || :BLK1.EXCEL_STATUS, 'I', false); /*This Message alert is showing on the screen*/

      END IF;

    END;