Skip to Main Content

Oracle Forms

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

jawed31Jun 22 2022

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;

This post has been answered by jawed31 on Jun 29 2022
Jump to Answer

Comments

Post Details

Added on Jun 22 2022
7 comments
1,428 views