Solved

Reading info from Excel into Oracle Forms application.

Posted on 2003-10-28
4
16,384 Views
Last Modified: 2008-04-23
Hi, I am new to this forum. I have a task of reading information from an excel spreadsheet into a Oracle Forms application to be inserted into a 8i database. How do I go about doing this using PL/SQL and is there any sample code that I may be able to look at? Thanks for any all all help.

TR
0
Comment
Question by:russelltp
  • 2
  • 2
4 Comments
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
DECLARE
application OLE2.OBJ_TYPE;
workbooks OLE2.OBJ_TYPE;
workbook OLE2.OBJ_TYPE;
worksheets OLE2.OBJ_TYPE;
worksheet OLE2.OBJ_TYPE;
cell OLE2.OBJ_TYPE;

args OLE2.OBJ_TYPE;

ctr NUMBER(12);
name_var VARCHAR2(2000);

BEGIN
application := OLE2.CREATE_OBJ('Excel.Application');
ole2.set_property(application,'Visible','true');
workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');
args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args,'c:\test.xls');
workbook := ole2.GET_OBJ_PROPERTY(workbooks,'Open',args);
ole2.destroy_arglist(args);
args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args,'Sheet4');
worksheet := ole2.GET_OBJ_PROPERTY(workbook,'Worksheets',args);
ole2.destroy_arglist(args);
ctr := 1;
FIRST_RECORD;
LOOP
args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args,ctr);
ole2.add_arg(args,1);
cell := ole2.GET_OBJ_PROPERTY(worksheet,'Cells',args);
ole2.destroy_arglist(args);
name_var := ole2.get_char_property(cell,'Value');
IF ctr = 1 then
:name := name_var;
ELSE
NEXT_RECORD;
:name := name_var;
END IF;
EXIT WHEN length(name_var) = 0;
ctr := ctr + 1;
END LOOP;
OLE2.INVOKE(application,'Quit');
OLE2.RELEASE_OBJ(cell);
OLE2.RELEASE_OBJ(worksheet);
OLE2.RELEASE_OBJ(worksheets);
OLE2.RELEASE_OBJ(workbook);
OLE2.RELEASE_OBJ(workbooks);
OLE2.RELEASE_OBJ(application);
END;

note: You need to call OLE2.RELEASE_OBJ(cell) after each instance of get_char_property rather then just at the end.








For Forms 4.5

DECLARE
-- Declare handles to OLE objects
application ole2.obj_type;
workbooks ole2.obj_type;
workbook ole2.obj_type;
worksheets ole2.obj_type;
worksheet ole2.obj_type;

-- Declare handles to OLE argument lists
args ole2.list_type;

BEGIN
-- Create handle to application object
application:= ole2.create_obj('Excel.Application');
OLE2.SET_PROPERTY(application, 'Visible', true);

args:=ole2.create_arglist;
ole2.add_arg(args, 'C:\excel.xls');
workbooks:= ole2.GET_OBJ_PROPERTY(application, 'Workbooks');
workbook:= ole2.get_obj_property(workbooks, 'open',args);
ole2.destroy_arglist(args);

-- Execute the macro called 'Test'
args:=OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 'Test');
OLE2.INVOKE(application,'Run',args);
OLE2.DESTROY_ARGLIST(args);

ole2.release_obj(worksheet);
ole2.release_obj(worksheets);
ole2.release_obj(workbook);
ole2.release_obj(workbooks);
ole2.release_obj(application);
END;

0
 

Author Comment

by:russelltp
Comment Utility
Thanks schwertner,

Would it be possible to explain the major steps in this process. It is the first time doing or seeing this example and not really sure how it works. How does the app know which excel file to open and which cells to extract from? Why do you have to define two workbooks and two worksheets?

Thanks
0
 

Author Comment

by:russelltp
Comment Utility
I tried the above code and I keep getting When-Button-Pressed Trigger raised unhandled exception ORA-305500. Any ideas?
0
 
LVL 47

Accepted Solution

by:
schwertner earned 50 total points
Comment Utility
Oracle Developer: OLE/DDE/VBX/OCX Common Issues
----------------------------------------------------
 
CONTENTS
========
1.  FRM-40735/ORA-305500 using OLE2.INVOKE_OBJ with Excel 97  
2.  A new instance of Excel is created each time you initiate the server even  
    though the server is already up and running
3.  Lotus 1-2-3 Inplace Activation does not work
4.  FRM-41344 setting OCX properties in When-New-Form-Instance trigger  
5.  Can you call a 16-bit OLE server from a 32-bit version Developer/2000  
    Tool?
6.  DDE.APP_END does not end Excel application  
7.  You cannot print from Microsoft Word using OLE
 
 
PROBLEMS & SOLUTIONS  
====================
1. FRM-40735/ORA-305500 using OLE2.INVOKE_OBJ with Excel 97
 
Problem
-------  
When you use the OLE2.INVOKE_OBJ function in the OLE2 package to get the handle  
of an Excel 97 object, the following error occurs:
 
  FRM-40735: <trigger name> trigger raised unhandled exception ORA-305500.
 
Your syntax resembles the following:
 
  WORKBOOKS:= OLE2.INVOKE_OBJ(APPLICATION, 'WORKBOOKS');            
 
Solution
--------
Use OLE2.Get_Obj_Property, instead of OLE2.Invoke_Obj.  
 
Example:
  WORKBOOKS:= OLE2.GET_OBJ_PROPERTY(APPLICATION, 'WORKBOOKS');
 
 
2.  A new instance of Excel is created each time you initiate the server
    even though the server is already up and running
 
Problem
-------
In Forms, you can use OLE to call Microsoft Word or Microsoft Excel.  For  
example, you can create a button to invoke Excel.  Consequently, more than one  
Excel instance will be invoked if you press the button several times, which
uses unnecessary system resources and can lead to a system crash.
 
To reproduce the problem:
 1. Create a button in a form.
 2. Create the following When-Button-Pressed trigger code:
   
 DECLARE
      appl       OLE2.OBJ_TYPE;
      workbooks      OLE2.OBJ_TYPE;
      workbook      OLE2.OBJ_TYPE;
 BEGIN
      appl := OLE2.CREATE_OBJ('Excel.Application');
      OLE2.SET_PROPERTY(appl, 'Visible', 'True');
      workbooks := OLE2.INVOKE_OBJ(appl, 'Workbooks');
      workbook := OLE2.INVOKE_OBJ(workbooks, 'Add');
 END;
 
 3. Run the form, and press the button twice.
    Note that two MS Excel instances are called.
 
Solution
--------  
It is not enough to check if the Excel server is already running
because a handle to the Excel server is required.
 
Instead, store the handle to the Excel server in a package variable that
can be used throughout the form.  Hence, OLE2.Create_Obj is performed
only once in a Forms session, which opens one instance of Excel.  
To prevent ORA-305500 from occurring if the user closes Excel
(therefore, loses the handle to the server), trap this error and
issue OLE2.Create_Obj to reacquire the handle to the server.
 
References
----------
[BUG:515507] MORE THAN ONE EXCEL INSTANCE WILL BE CALLED THROUGH OLE
 
 
3.  Lotus 1-2-3 Inplace Activation does not work
 
Problem
-------
When using Lotus 1-2-3 as the OLE server within Forms, the inplace activation  
functionality does not operate successfully.  For example, double-clicking on  
the OLE object does not insert a Lotus spreadsheet within Forms,
but initiates Lotus instead.
 
This functionality works successfully with Microsoft Excel and Microsoft Word.
 
Solution
--------
This functionality depends on the capability of the third party OLE server,  
because some OLE servers do not support Inplace Activation.  Consult the vendor
of the OLE Server to determine if the OLE server has this capability.  
 
References
----------
[BUG:439206] INPLACE ACTIVATION WIHT LOTUS 1-2-3 DOES NOT OPERATE CORRECTLY.
 
 
4.  FRM-41344 setting OCX properties in When-New-Form-Instance trigger
 
Problem
-------
FRM-41344: OLE OBJECT NOT DEFINED FOR <ole object name> IN THE CURRENT  
           RECORD
 
This error occurs when you do the following:
 o  Open a form which has an OCX item embedded in the first item on a canvas.
 o  Set OCX properties in a When-New-Form-Instance trigger.
 
Solution
--------
Use one of the following workarounds:
 
Workaround 1:
Call the SYNCHRONIZE built-in within the When-New-Form-Instance trigger
before initiating the OCX properties.  You can refer to PRE 1020265.6.
 
Workaround 2:
Add a dummy item to the canvas containing the OCX, so that focus goes
to this item first when the form is initially opened.
 
References
----------
[PR:1020265.6] FRM-41344 USING FORMS_OLE.GET_INTERFACE_POINTER
 
 
5.  Can you call a 16-bit OLE server from a 32-bit version Developer/2000  
    Tool?
 
Problem
-------
A common question is whether you can invoke a 16-bit OLE server
from a 32-bit version of Forms.
 
Solution
--------
A 16-bit OLE server can be invoked from a 32-bit version Developer/2000 Tool,
such as Forms, if the registry is set up correctly.
 
The InprocServer32 has to point to a 16-bit executable.  The executable
must be an .EXE file (not a .DLL or .OCX), so that it is started
"out of process."
 
WordPerfect is an example of an OLE server that cannot be invoked
because it implements an InprocServer32 of "wpauto.DLL",
which is not a .EXE file.
 
 
6.  DDE.APP_END does not end Excel application  
 
Problem
-------
The DDE.APP_END procedure does not end the Excel application, when an Excel  
spreadsheet is accessed from within Forms.  The Excel spreadsheet remains open.
 
This problem only occurs if you have modified the Excel spreadsheet using DDE.
 
Solution
--------
Call the DDE.APP_END procedure twice in succession.  
 
References
----------
[PRSOL:2080998.6] BUG 368301 -- ISSUE DDE.APP_END TWICE TO CLOSE MODIFIED
                  SPREADSHEET
 
 
7.  You cannot print from Microsoft Word using OLE
 
Problem
-------
A Microsoft Word file is not printed after using OLE to call Microsoft Word
from within Forms.  OLE is used to open the file and send it to the printer
from Microsoft Word, but the file is not printed.
 
Solution
--------
Under normal circumstances, you must release the handles to all the
OLE objects using OLE2.Release_Obj.  The file is not printed because
the handle to Word is released before printing is complete.  
This behavior is not a bug and is functioning as designed.
 
Workaround 1:
Do not call OLE2.Release_Obj to release the handle to the OLE object.
 
Workaround 2:
This is the better workaround.
 
Set background printing in Word to FALSE using OLE2 package
before releasing the OLE object.  Implement the following code:
 
DECLARE
   application  OLE2.OBJ_TYPE;
   document     OLE2.OBJ_TYPE;
   args         OLE2.OBJ_TYPE;
BEGIN
   /* Get handle to Word */
   application := OLE2.CREATE_OBJ('WORD.BASIC');
 
   /* Make Word visible */
   OLE2.INVOKE(application, 'AppShow');
 
   /* Provide the filename as an argument and open the file*/
   args := OLE2.CREATE_ARGLIST;
   OLE2.ADD_ARG(args, 'c:\krown\test.doc');
   document := OLE2.INVOKE_OBJ(application, 'FileOpen', args);
   OLE2.DESTROY_ARGLIST(args);       
 
   /* Set background printing to False */
   args := OLE2.CREATE_ARGLIST;
   OLE2.ADD_ARG(args, 0);
 
   /* Send the file to print */
   OLE2.INVOKE(document, 'FilePrint', args);
   OLE2.DESTROY_ARGLIST(args);
 
   /* Release handles */
   OLE2.RELEASE_OBJ(document);
   OLE2.RELEASE_OBJ(application);
END;
 
By setting background printing to FALSE, you are disallowing control to be  
returned back to Forms until the printing from Word is complete.  After
printing is complete, the OLE object can then be released.
 

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now