Home » Developer & Programmer » Forms » Problem in reading data from excel using oracle forms through oracle apps serever (windows,oracle forms10g,oracle apps server)
| Problem in reading data from excel using oracle forms through oracle apps serever [message #432714] |
Wed, 25 November 2009 23:24  |
rahulleven
Messages: 2
Registered: November 2009
Location: bangalore
|
Junior Member |
|
|
Dear All,
The following coding is used for reading data from excel file and saving into the database
Scenario where the code works
The setup which we are using as follows
1) windows platform
2) Development database which works independently(oracle database 10g version 10.2.0.1)
3) Forms builder installed in local system
4) excel file saved in local system
when we run the form locally with following code it works fine. That is as per path which is given in the code
it reads the data from the excel file from local system and inserted into the table specified in the code
Scenario where the code does not work
The setup which we are using as follows
1) windows platform
2) Aplliction server 10g (forms and report services)
3) Database (oracle database 10g version 10.2.0.1) which works independently on another server
4) excel file saved in local as well as application server
when we run the form through application server with following code it doesn't work.In this case the form gets hanged
and we need to kill the session.
Please refer the code written below and do the needful thing.
DECLARE
appid PLS_INTEGER;
convid PLS_INTEGER;
docid PLS_INTEGER;
conv_established BOOLEAN := FALSE;
buffer1 VARCHAR2(14);
buffer2 VARCHAR2(14);
buffer3 VARCHAR2(13);
buffer4 VARCHAR2(13);
DNO VARCHAR2(14);
DN VARCHAR2(14);
DL VARCHAR2(13);
DP VARCHAR2(13);
LC VARCHAR2(6);
I NUMBER(10) := 1;
BEGIN
APPID := DDE.APP_BEGIN('C:\Program Files\Microsoft Office\OFFICE11\excel.EXE',
DDE.APP_MODE_MINIMIZED);
WHILE NOT conv_established
LOOP
BEGIN
convid := DDE.INITIATE('excel', 'system');
conv_established := TRUE;
EXCEPTION
WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN
conv_established := FALSE;
END;
END LOOP;
DDE.EXECUTE(convid, '[Open("d:\test\book1.xls")]', 100000000);
docid := DDE.INITIATE('excel', 'd:\test\book1.xls');
LOOP
LC := 'R'||I||'C1';
DDE.REQUEST (docid, LC, buffer1, DDE.CF_TEXT, 10000);
DN := BUFFER1;
exit when SUBSTR(BUFFER1,1,LENGTH(BUFFER1)-2)= 'END' ;
IF SUBSTR(BUFFER1,1,LENGTH(BUFFER1)-2)= 'END' THEN
NULL;
END IF;
LC := 'R'||I||'C2';
DDE.REQUEST (docid, LC, buffer2, DDE.CF_TEXT, 10000);
DN := BUFFER2;
LC := 'R'||I||'C3';
DDE.REQUEST (docid, LC, buffer3, DDE.CF_TEXT, 10000);
DL := BUFFER3;
LC := 'R'||I||'C4';
DDE.REQUEST (docid, LC, buffer4, DDE.CF_TEXT, 10000);
DP := BUFFER4;
INSERT INTO fas_excel_cash_bank_dtl VALUES(SUBSTR(BUFFER1,1,LENGTH(BUFFER1)-2),
SUBSTR(DN,1,LENGTH(DN)-2),SUBSTR(DL,1,LENGTH(DL)-2),SUBSTR(DP,1,LENGTH(DP)-2));
forms_ddl('COMMIT');
I := I+1;
END LOOP;
DDE.TERMINATE(docid);
DDE.TERMINATE(convid);
DDE.APP_END(appid);
EXCEPTION
WHEN DDE.DDE_APP_FAILURE THEN
MESSAGE('WINDOWS APPLICATION CANNOT START.');
WHEN DDE.DDE_PARAM_ERR THEN
MESSAGE('A NULL VALUE WAS PASSED TO DDE');
WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN
MESSAGE('DDE CANNOT ESTABLISH A CONVERSATION');
WHEN DDE.DMLERR_NOTPROCESSED THEN
MESSAGE('A TRANSACTION FAILED');
END;
[Updated on: Wed, 25 November 2009 23:35] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Fri Sep 20 04:54:23 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
