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 Go to next message
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

Re: Problem in reading data from excel using oracle forms through oracle apps serever [message #435996 is a reply to message #432714] Sun, 20 December 2009 22:51 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
I see that this thread is identical or nearly identical to http://www.orafaq.com/forum/mv/msg/67472/430552/67467/#msg_430552

It is now a month later, have you solved your problem? Have you tried a simpler spreadsheet test? Have you tried running the code that is listed in any of the other 'Excel' threads in this forum?

Using 'message; pause;' pairs to separate each command, on which line does your code fail?

David
Previous Topic: Conditionally limit detail records in master-detail form
Next Topic: Sending commands to an external application
Goto Forum:
  


Current Time: Sun Dec 04 22:47:20 CST 2016

Total time taken to generate the page: 0.03843 seconds