Home » Developer & Programmer » Forms » sending & reading data from excel,word
sending & reading data from excel,word [message #186455] Tue, 08 August 2006 01:02 Go to next message
toufiq_raja
Messages: 39
Registered: May 2005
Location: Islamabad
Member

hi, to all
i have been asked from many ppl that how to read data from excel into oracle forms and how to send data from oracle forms to excel same is the case for MS Word

i have developed two forms to accomplish this task

for all those who require help can get guide line from these forms

regards

Toufiq Raja
Re: sending & reading data from excel,word [message #186456 is a reply to message #186455] Tue, 08 August 2006 01:02 Go to previous messageGo to next message
toufiq_raja
Messages: 39
Registered: May 2005
Location: Islamabad
Member

hi, to all
i have been asked from many ppl that how to read data from excel into oracle forms and how to send data from oracle forms to excelsame is the case for MS Word

i have developed two forms to accomplish this task

for all those who require help can get guide line from these forms

regards

Toufiq Raja
  • Attachment: r5.fmb
    (Size: 88.00KB, Downloaded 1614 times)
Re: sending & reading data from excel,word [message #186473 is a reply to message #186456] Tue, 08 August 2006 01:53 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Does this work in a 3 tier environment or is it client/server only? Version information would be useful.

MHE
Re: sending & reading data from excel,word [message #186498 is a reply to message #186455] Tue, 08 August 2006 04:34 Go to previous messageGo to next message
anna marie
Messages: 26
Registered: June 2006
Location: Philippines
Junior Member
I can't open properly the file on Oracle 5 r2.1, program units don't display and button event doesn't show. Can you post or attached the code for me? Thank you in advance.
Re: sending & reading data from excel,word [message #320066 is a reply to message #186455] Wed, 14 May 2008 00:02 Go to previous messageGo to next message
jale
Messages: 15
Registered: May 2008
Location: TURKEY
Junior Member
'fromtoexcel.fmb' works on my PC, but when I open it from AppServer, I get ORA-106561 error, I can't find this error is why,

It didn't work, when I added webutil to the same form.


We use Forms [32 Bit] Version 10.1.2.0.2 (Production)
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production With the Partitioning, OLAP and Data Mining options


Any tips will be great..

Thanks
Re: sending & reading data from excel,word [message #320109 is a reply to message #320066] Wed, 14 May 2008 02:13 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Okay, and what is an ORA-106561 when its at home?

David
Re: sending & reading data from excel,word [message #320114 is a reply to message #320109] Wed, 14 May 2008 02:21 Go to previous messageGo to next message
jale
Messages: 15
Registered: May 2008
Location: TURKEY
Junior Member
I searched for error but I have found no explanation.

And also I am trying with ole2 code:
DECLARE
-- Declare handles to OLE objects
  application   client_ole2.OBJ_TYPE;
  workbooks     client_ole2.OBJ_TYPE;
  workbook      client_ole2.OBJ_TYPE;
  worksheets    client_ole2.OBJ_TYPE;
  worksheet     client_ole2.OBJ_TYPE;
  cell          client_ole2.OBJ_TYPE;
  args          client_ole2.OBJ_TYPE;
  Check_file    text_io.file_type;
  no_file       exception;
  PRAGMA exception_INIT (no_file, -302000);
  cell_value    varchar2 (2000);
BEGIN
/*-- Check the file can be found, if not exception no_file will be raised
Check_file := TEXT_IO.FOPEN('c:\test.xls','R');
TEXT_IO.FCLOSE(Check_file);
Message('hellllllllllooo');*/

  -- ***********************************
  application  := client_ole2.CREATE_OBJ ('Excel.Application');
--client_ole2.set_property(application,'Visible','true');
  workbooks    := client_ole2.GET_OBJ_PROPERTY (application, 'Workbooks');
  args         := client_ole2.CREATE_ARGLIST;
  client_ole2.add_arg (args, 'c:\test.xls');
  workbook     := client_ole2.GET_OBJ_PROPERTY (workbooks, 'Open', args);
  client_ole2.destroy_arglist (args);
  worksheets   := client_ole2.GET_OBJ_PROPERTY (workbook, 'Worksheets');
  worksheet    := client_ole2.GET_OBJ_PROPERTY (application, 'activesheet');
  client_ole2.SET_PROPERTY (worksheet, 'Value', 'Sheet1');
-- ***********************************

  /*-- PASS VALUE INTO Excel CELL (ROW 371, COL 3)
args := client_ole2.CREATE_ARGLIST;
client_ole2.ADD_ARG(args, 371);
client_ole2.ADD_ARG(args, 3);
cell := client_ole2.GET_OBJ_PROPERTY(worksheet,'Cells', args);
client_ole2.DESTROY_ARGLIST(args);
client_ole2.SET_PROPERTY(cell, 'Value', '97FI01'); */

  -- Get value of cell (372,3) of worksheet Sheet1
  args         := client_ole2.create_arglist;
  client_ole2.add_arg (args, 1);
  client_ole2.add_arg (args, 1);
  cell         := client_ole2.get_obj_property (worksheet, 'Cells', args);
  client_ole2.destroy_arglist (args);
  cell_value   := client_ole2.get_char_property (cell, 'Value');
  message (cell_value);
-- save document as test.xls
  client_ole2.INVOKE (worksheet, 'Save');
  client_ole2.INVOKE (workbook, 'Save');
  client_ole2.INVOKE (workbook, 'CLOSE');
-- Release the client_ole2 object handles
  client_ole2.release_obj (cell);
  client_ole2.release_obj (worksheet);
  client_ole2.release_obj (worksheets);
  client_ole2.release_obj (workbook);
  client_ole2.release_obj (workbooks);
  client_ole2.invoke (application, 'Quit');
  client_ole2.release_obj (application);
-- ASSIGN RETURN VALUE FROM EXCEL TO Text Field
--:Block2.RETURN_VAL := cell_value;
exception
  WHEN no_file THEN
    MESSAGE ('file not found.');
  WHEN OTHERS THEN
    MESSAGE (sqlerrm);
    PAUSE;
    FOR i IN 1 .. tool_err.nerrors
    LOOP
      MESSAGE (tool_err.message);
      PAUSE;
      tool_err.pop;
    END LOOP;
END;

It works on my PC.

but at APPServer, I get no file error.

Upd mod: Reformat code.

[Updated on: Wed, 14 May 2008 19:09] by Moderator

Report message to a moderator

Re: sending & reading data from excel,word [message #320118 is a reply to message #320114] Wed, 14 May 2008 02:26 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Me neither!! How about taking a screen shot of the error code and error message as it was displayed to you, saving it as a 'jpg', and posting it.

David
Re: sending & reading data from excel,word [message #320121 is a reply to message #320118] Wed, 14 May 2008 02:34 Go to previous messageGo to next message
jale
Messages: 15
Registered: May 2008
Location: TURKEY
Junior Member
dde code

Maybe it is about webutil version, java or forms version
but we can upload txt datas to database with webutil, also it works on my PC.

Re: sending & reading data from excel,word [message #320134 is a reply to message #320121] Wed, 14 May 2008 03:04 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Sorry, but you will have to translate the last three words of the error message into English for me.

David
Re: sending & reading data from excel,word [message #320138 is a reply to message #320134] Wed, 14 May 2008 03:06 Go to previous messageGo to next message
jale
Messages: 15
Registered: May 2008
Location: TURKEY
Junior Member
exception occured, exception raised
Re: sending & reading data from excel,word [message #320145 is a reply to message #320138] Wed, 14 May 2008 03:14 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
What operating system does your AS use?

David
Re: sending & reading data from excel,word [message #320149 is a reply to message #320145] Wed, 14 May 2008 03:17 Go to previous messageGo to next message
jale
Messages: 15
Registered: May 2008
Location: TURKEY
Junior Member
unix, solaris something, not microsoft windows
Re: sending & reading data from excel,word [message #320368 is a reply to message #320149] Wed, 14 May 2008 19:12 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have a look at http://www.orafaq.com/forum/m/318294/67467/?srch=client_ole#msg_318294

Can you run the 'webutil' 'test' form from your AS?

David
Re: sending & reading data from excel,word [message #320398 is a reply to message #320368] Thu, 15 May 2008 00:10 Go to previous messageGo to next message
jale
Messages: 15
Registered: May 2008
Location: TURKEY
Junior Member
We use text_io for txt uploading to database from clientside with forms in AppServer, webutil is attached right.

But I want to test it, how can I do the test?

Thanks

Jal
Re: sending & reading data from excel,word [message #320399 is a reply to message #320368] Thu, 15 May 2008 00:15 Go to previous messageGo to next message
jale
Messages: 15
Registered: May 2008
Location: TURKEY
Junior Member


I have read sentences linked from online help of form builder, I have given up ole2.

I try to upload data from client excel file to database with forms 10g working from AppServer, client_ole2 doesn't support it, I am trying DDE, if DDE won't work then ODBC.

Thank you David


Jal
  • Attachment: ole.txt
    (Size: 3.50KB, Downloaded 871 times)
Re: sending & reading data from excel,word [message #430552 is a reply to message #186455] Wed, 11 November 2009 00:41 Go to previous message
rahulleven
Messages: 2
Registered: November 2009
Location: bangalore
Junior Member
Dear Sir,

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;
Previous Topic: Opening an unknown document type from forms
Next Topic: HOST command not working
Goto Forum:
  


Current Time: Fri Sep 30 10:46:37 CDT 2016

Total time taken to generate the page: 0.09126 seconds