Скачать или смотреть Solution for Saving PDFs into Database with Oracle Apex and ORDS

Discover how to save PDF documents generated in Oracle Apex into your database using ORDS. This comprehensive guide offers step-by-step instructions and tips for effective implementation.
---
This video is based on the question https://stackoverflow.com/q/63896486/ asked by the user 'whatever' ( https://stackoverflow.com/u/13852391/ ) and on the answer https://stackoverflow.com/a/63941168/ provided by the user 'Enrique Flores' ( https://stackoverflow.com/u/4664208/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Oracle Apex save PDF into Database with ORDS?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Save PDFs into Database with Oracle Apex and ORDS

Working with PDF generation in Oracle Apex can be a challenging endeavor, especially when you want to save these generated PDFs into a database. Recently, a common issue arose regarding the use of APEX_UTIL.GET_PRINT_DOCUMENT API, resulting in corrupted PDFs and a frustrating 401 Unauthorized error. If you're grappling with similar problems, you're not alone. In this guide, we'll explore the solution to saving PDFs into your database effectively using Oracle Apex and ORDS.

Understanding the Issue

When generating PDF documents using Oracle Apex, users can face several stumbling blocks, particularly when utilizing the Print API. Here are some details to note:

PDF Generation: The function you have creates XML from SQL queries, and for styling, you're using an XSL-FO stylesheet.

Using ORDS: While APEX can generate PDF documents using the Print API, it tends to send the PDF back to the client only when ORDS is in use.

Unauthorized Errors: This could indicate issues with authentication, or that the request isn’t being handled correctly.

The Solution: Saving PDFs into the Database

Fortunately, there is a method to save PDFs in a database by converting them into a BLOB format. However, Oracle Apex doesn't have this functionality built-in by default. Below, we’ll lay out the steps needed to save your PDFs effectively.

Step 1: Generate the PDF Document

You'll first need to ensure that you can generate the PDF document from your XML data. One practical way to do this is through a reporting tool. For instance, if you are using Jasper Server Community, you can create a report that outputs the PDF.

Step 2: Use PL/SQL for HTTP Request

You’ll need to send an HTTP request to the Jasper Server and retrieve the PDF. Here’s a sample PL/SQL block that demonstrates this:

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Store the PDF in the Database

In the PL/SQL code provided, the PDF is saved as a BLOB in the database. Ensure that your database is configured to accept BLOB data types. This method uses the ability to manage temporary LOBs to hold the PDF file until it is ready to be stored.

Conclusion

In conclusion, generating and saving PDF documents into your database using Oracle Apex and ORDS requires a careful setup and understanding of your tools. By following the steps outlined above, you can successfully create, retrieve, and store PDFs in your database, improving your application's functionality without needing third-party paid solutions.

Remember to test your implementation thoroughly, especially the HTTP requests to ensure you handle authentication correctly. Happy coding!