In this section, we'll learn how to work with CLOB (Character Large Object) data in Oracle APEX. We'll walk through a practical example of converting plain text into a PDF file using JavaScript and then saving that PDF into the Oracle database as a CLOB. This is especially useful for applications that need to store generated reports, dynamic content, or user input in document format without using BLOB storage. To implement this, we need to follow the below steps. For a visual assistance, you can find a tutorial from here.
STEP 1- Create a blank page with neccessary items:
i. Create a region and set it properties as like below-
ii. Create page items under that region
P34_REPORT_ID
TYPE: Text
Field
Template: Optional
Template Options: Check Stretch Form Item
P34_PAGE_ID
TYPE: Text Field
Start New Row: No (Toggle Off)
Template: Optional
Template Options:
Check
Stretch Form Item
P34_CLOB_TYPE
Type: Radio
Group
Number of Columns: 2
Template: Optional
Template Options:
Check Stretch Form Item
List of Values:
Type: Static Values
Static Values: Display Return
File F
Text T
Default:
Type: Static
Static Value: F
P34_UPLOAD_FILE
Type: File Upload
Template: Optional
P34_OUTPUT_TEXT
Type: Textarea
Template: Optional
Template Options:
Check Stretch Form Item
Height: 2
STEP 2- Create a dynamic action under the "P34_CLOB_TYPE" item:
Name: Set Input Item
Client Side Condition:
Type: Item = Value
Item: P34_CLOB_TYPE
Value: F
Select Newly created True Action and set it properties as like-
Action: Show
Selection Type: Items
Item(s): P34_UPLOAD_FILE
Create another True Action and set it properties as like-
Action: Hide
Selection Type: Items
Item(s): P34_OUTPUT_TEXT
Create False Action and set it properties as like-
Action: Show
Selection Type: Items
Item(s): P34_OUTPUT_TEXT
Create another False Action and set it properties as like-
Action: Hide
Selection Type: Items
Item(s): P34_UPLOAD_FILE
STEP 3- Select Process tab from the left side navigation pane. Right Click on the process and then select Create Process and set it propertise like-
Name: Save Data
Location: Local Database
Language: PL/SQL
PL/SQL Code:
DECLARE
l_clob CLOB;
BEGIN
SELECT apex_web_service.blob2clobbase64(blob_content)
INTO l_clob
FROM apex_application_temp_files
WHERE name = :P34_UPLOAD_FILE;
INSERT INTO report_output_pages(report_id, page_id, report_text)
VALUES(:P34_REPORT_ID, :P34_PAGE_ID, l_clob);
END;
Server Side Condition:
Type: Item = Value
Item: P34_CLOB_TYPE
Value: F
STEP 4- Create Ajax Callback Process. Set it properties as like:
Name: SAVE_DATA
Location: Local Database
Language: PL/SQL
PL/SQL Code:
apex_json.open_array;
apex_json.open_object;
INSERT INTO report_output_pages(report_id, page_id, report_text)
VALUES(apex_application.g_x01, apex_application.g_x02, apex_application.g_clob_01);
apex_json.write('MSG', 'OK');
apex_json.close_object;
apex_json.close_all;
STEP 5- Download JavaScript Library File From the given link below- https://cdnjs.cloudflare.com/ajax/libs/jspdf/2.5.1/jspdf.umd.min.js
Copy the code from the browser. Create a new text file and paste the copied code here. Save it as jsPdf.js file.
Go to the application and open shared Component. Select static application file and upload the jsPdf.js file here. Copy the reference.
STEP 6- Go to the page and open then Javascript > File URLs code editor from the properties section (Right side navigation pane)
Paste the copied reference (#APP_FILES#jsPdf.js) here.
7. Create a JavaScript function. Open the "Function and Global Variable Declaration" code editor. Paste the following code in the editor-
function saveData() {
//clear existing message from the page
apex.message.clearErrors();
// create jsPDF instance for A4 size pepar document
const doc = new jspdf.jsPDF(
{
unit: 'mm',
format: 'a4'
}
);
//setting page margin
const marginLeft = 20 ;
const marginTop = 20 ;
const lineHeight = 7 ;
const pageWidth = 210 ; //A4 Size paper width
const pageHight = 297 ; //A4 size paper height
const maxLineWidth = pageWidth - marginLeft * 2 ;
/ /capture the input text
var txt = $v('P34_OUTPUT_TEXT');
//Split the captured text into line that fit a4 size paper width
const lines = doc.splitTextToSize(txt, maxLineWidth);
//setting page font family and font size
doc.setFont('Times', 'Normal');
doc.setFontSize(12);
let cursorY = marginTop ;
//Handle pagination
for (let i = 0 ; i < lines.length ; i++) {
if (cursorY > pageHight - marginTop) {
doc.addPage() ;
cursorY = marginTop ;
}
doc.text(lines[i], marginLeft, cursorY);
cursorY += lineHeight ;
}
//convert the generated pdf to base64 format to store it to database
const pdfBase64 = doc.output('datauristring').split(',')[1];
//Now call the ajax callback function to save data
if ($v('P34_CLOB_TYPE') === 'T') {
apex.server.process(
'SAVE_DATA'
, {
x01: $v('P34_REPORT_ID') ,
x02: $v('P34_PAGE_ID'),
p_clob_01: pdfBase64
}
, {
dataType: 'json',
success: function(pData) {
if (pData[0].MSG === 'OK') {
apex.message.showPageSuccess('Data Saved Successfully.');
} else {
apex.message.showErrors(
[
{
type: 'error' ,
message: 'Something wrong!',
location: 'page',
unsafe: false
}
]
)
}
}
}
)
}
apex.page.submit();
}
That's it. Let run the page and enjoy the process of text conversion into PDF. Happy coding!!
Comments
Post a Comment