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;