Oracle Application Express

APEX & SQLWord integration explained

The SQLWord Apex demo application is based on Oracle HR-tables and demonstrates how to integrate SQLWord with Apex. In this post the implemention of a Job offer letter in an Apex page is explained. The MSWord template “HR_Employee_Job_Offer.docx” is the source document for this letter. We assume that you have compiled this template to stored procedure HR_EMPLOYEE_JOB_OFFER.

Start the SQLWord HR Demo application and choose Employees from the menu

Employees

Select employee David Austin to go to the Detail page 3:

 

Employee-page3

Apex-button-JobOffer 

Click on button Job Offer to generate the Microsoft Word document

JobOffer

How does it work?

Open page 3

Apex-page-3

Examine the source code from the On Demand process CREATE_JOB_OFFER_DOCX

Apex-Create_Job_Offer_Docx

 

Explanation 

  • Stored procedure HR_EMPLOYEE_JOB_OFFER is called with parameter value :P3_EMPLOYEE_ID.
  • The output document is stored in a local variable L_BLOB by calling function SQLWORD.GET_OUPUT_DOCX.
  • An Apex collection is used to store the data.
  • After processing a branch to page 500 is done

Apex-Goto-Page-500

Open page 500

This is a generic empty page for downloading prepared documents.

Apex-page-500

Examine the source code from (On Load – Before Header) process DOWNLOAD_DOCX

Apex-page-500-download_docx

Explanation

  • The output document and filename is retrieved from the Apex collection.
  • The HTML header is prepared.
  • By calling Apex procedure WPG_DOCLOAD.DOWNLOAD_FILE the download will start.
  • Page 500 is closed an the browser returns back to page 3.
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *