Ask the Expert

Moving data from Oracle report to MS Excel

Can you help me with Oracle error ORA-41020? I could not find the error message in the Oracle error messages documentation. I am trying to move data from an Oracle report into MS Excel. So, I am using the DDE poke function to put the report values into the Excel file. Then, I encountered the error message.

    Requires Free Membership to View

I can't find that error number either. Are you sure that you did not see an ORA-04? I did find some additional information using DDE.POKE to access Excel files. Is it possible that the error was a ORA-04012 error? This would indicate that there may be a spelling problem in an Oracle object (procedure name, table, whatever) that you have been trying to reference.

However, I've included an example of a DDE POKE sequence for writing to Excel for you to compare. The following is a sample trigger for using DDE to an MS Excel Spreadsheet.

/* Declare variables */ 
DECLARE 
     appid            PLS_INTEGER; 
     convid           PLS_INTEGER; 
     docid            PLS_INTEGER; 
     conv_established BOOLEAN := FALSE; 
     buffer           CHAR(100);  /* This determines the maximum size of 
                                     of the values being returned from 
                                     Excel.  Adjust it if your values exceed 
                                     this. */ 
 
/* Begin trigger DDE sample */ 
BEGIN 
 
/* Start Excel */ 
/* This line assumes that Excel is in the directory e:msofficeexcel */ 
   APPID := DDE.APP_BEGIN('E:MSOFFICEexcelexcel.EXE', 
                          DDE.APP_MODE_MINIMIZED); 
 
/* Establish a conversation with Excel */ 
/* The following loop will not end until a conversation with Excel 
   has been established.  Therefore, it can result in a endless loop, 
   so use with caution. */ 
   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; -- loop 
 END LOOP; 
 
/* Open Excel document */ 
/* This assumes that you have an Excel spreadsheet named ddetest.xls 
   in the root of e: */ 
     DDE.EXECUTE(convid, '[Open("E:ddeTEST.xls")]', 10000); 
 
/* Initiate conversation with Excel document */ 
     docid := DDE.INITIATE('excel', 'E:ddetest.xls'); 
 
/* Begin transfer to Excel */ 
     DDE.POKE(docid, 'R2C2', 'Text', DDE.CF_TEXT, 10000); 
 
/* Begin transfer from Excel to Forms */ 
     DDE.REQUEST (docid, 'R2C2:R2C3', buffer, DDE.CF_TEXT, 1000); 
 
/* Set a text item equal to the value returned from the buffer */ 
     :TEXT_ITEM := buffer; 
 
/*End transfer to Excel */ 
     DDE.TERMINATE(docid); 
     DDE.TERMINATE(convid); 
     DDE.APP_END(appid); 
 
/* Handle exceptions */ 
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 of trigger */ 
END; 

Creating a default form
-----------------------
1. Startup Oracle Forms Designer, and create a default form.
2. Add a button using the Layout Editor.
3. Create a When-Button-Pressed trigger. Add the code listed in this document in the trigger.
4. Compile the trigger.
5. Add a text item named Text_item. This will display the value requested from Excel.
6. Save and generate the form.

Creating an MS-Excel spreadsheet
-------------------------------
Once you have successfully generated the form, you need to create an Excel spreadsheet.

1. Start up MS Excel.
2. Add the text and formatting to the spreadsheet.
3. Save the new spreadsheet with the name ddetest.xls.

You are now ready to run the DDE sample form. Run the form and engage the trigger by pressing on the button. This should start up MS Excel and transfer the data from Oracle Forms to MS Excel and back to Oracle Forms text item.

If you have access to MetaLink, check out Notes 37660.1 and 1018631.6.

This was first published in December 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: