OCR Data Extraction & Display in Oracle APEX

Published on 20 Jul 2025 by CHATURVEDINIDHI26@GMAIL.COM

OCR Data Save & Display in Oracle APEX

1. Database Table Structure

OCR\_RESULTS: Stores full OCR content and JSON structure.

CREATE TABLE OCR\_RESULTS (  
 ID            NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,  
 OCR\_DATE      DATE DEFAULT SYSDATE,  
 RAW\_TEXT      CLOB,  
 JSON\_DATA     CLOB,  
 DOCUMENT\_CODE VARCHAR2(20)  
);

OCR\_LINES: Stores each line of OCR result separately.

CREATE TABLE OCR\_LINES (  
 ID            NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,  
 LINE\_NUM      NUMBER,  
 LINE\_TEXT     VARCHAR2(4000),  
 OCR\_DATE      DATE DEFAULT SYSDATE,  
 DOCUMENT\_CODE VARCHAR2(20)  
);

2. Document Code Generation

Sequence and trigger to assign a unique code like DOC0001, DOC0002:

CREATE SEQUENCE DOC\_CODE\_SEQ START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER TRG\_OCR\_RESULTS\_DOC\_CODE  
BEFORE INSERT ON OCR\_RESULTS  
FOR EACH ROW  
BEGIN  
 IF :NEW.DOCUMENT\_CODE IS NULL THEN  
   :NEW.DOCUMENT\_CODE := 'DOC' || LPAD(DOC\_CODE\_SEQ.NEXTVAL, 4, '0');  
 END IF;  
END;

3. APEX Page Items

P12\_UPLOAD - File browse item for image/PDF.
P12\_RESULT - Textarea to show raw OCR text.
P12\_RESULT\_JSON - Textarea to show OCR JSON output.

4. JavaScript Function (Page Function and Global JS)

Function to extract OCR using OCR.Space API:

function startOCR() {  
 const fileInput = document.querySelector(".ocrFile");  
 const ocrTextarea = document.querySelector(".ocrText");  
 const jsonOutput = document.querySelector(".jsonOutput");

 if (!fileInput.files.length) {  
   alert("Please select an image or PDF file.");  
   return;  
 }

 const formData = new FormData();  
 formData.append("file", fileInput.files\[0\]);  
 formData.append("language", "eng");  
 formData.append("isOverlayRequired", "false");  
 formData.append("OCREngine", "2");  
 formData.append("apikey", "helloworld"); // Replace with your real API key

 fetch("https://api.ocr.space/parse/image", {  
   method: "POST",  
   body: formData  
 })  
 .then(res => res.json())  
 .then(data => {  
   const rawText = data?.ParsedResults?.\[0\]?.ParsedText || "No text found.";  
   ocrTextarea.value = rawText;

   const lines = rawText.split(/\\r?\\n/).map(s => s.trim()).filter(Boolean);  
   const json = {};  
   lines.forEach((line, i) => {  
     json\[\`col${i + 1}\`\] = line;  
   });

   jsonOutput.textContent = JSON.stringify(json, null, 2);

   if (typeof apex !== "undefined" && apex.item) {  
     apex.item("P12\_RESULT").setValue(rawText);  
     apex.item("P12\_RESULT\_JSON").setValue(JSON.stringify(json));  
   }  
 })  
 .catch(err => {  
   console.error("OCR Error:", err);  
   ocrTextarea.value = "OCR failed.";  
 });  
}

5. Save Process (PL/SQL)

Executed when "Save" button is clicked:

DECLARE  
 v\_doc\_code OCR\_RESULTS.DOCUMENT\_CODE%TYPE;  
 v\_json   CLOB := :P12\_RESULT\_JSON;  
 v\_val    VARCHAR2(4000);  
 i        NUMBER := 1;  
BEGIN  
 INSERT INTO OCR\_RESULTS (RAW\_TEXT, JSON\_DATA)  
 VALUES (:P12\_RESULT, :P12\_RESULT\_JSON)  
 RETURNING DOCUMENT\_CODE INTO v\_doc\_code;

 APEX\_JSON.PARSE(v\_json);

 LOOP  
   BEGIN  
     v\_val := APEX\_JSON.GET\_VARCHAR2('col' || i);  
   EXCEPTION  
     WHEN OTHERS THEN  
       EXIT;  
   END;

   EXIT WHEN v\_val IS NULL;

   INSERT INTO OCR\_LINES (LINE\_NUM, LINE\_TEXT, DOCUMENT\_CODE)  
   VALUES (i, v\_val, v\_doc\_code);

   i := i + 1;  
 END LOOP;

 COMMIT;  
END;

6. Dynamic SQL to Display OCR Data

This PL/SQL block generates dynamic pivot SQL to display data as columns.

DECLARE  
 l\_sql CLOB;  
 l\_upload\_code VARCHAR2(100);  
BEGIN  
 SELECT MAX(DOCUMENT\_CODE) INTO l\_upload\_code FROM OCR\_LINES;

 SELECT 'SELECT ' || LISTAGG(  
            'MAX(CASE WHEN LINE\_NUM = ' || LINE\_NUM ||   
            ' THEN LINE\_TEXT END) AS "' || LINE\_NUM || '"',  
            ', '  
        ) WITHIN GROUP (ORDER BY LINE\_NUM)  
        || ' FROM OCR\_LINES WHERE DOCUMENT\_CODE= ''' || l\_upload\_code || ''''  
 INTO l\_sql  
 FROM (SELECT DISTINCT LINE\_NUM FROM OCR\_LINES WHERE DOCUMENT\_CODE = l\_upload\_code);

 RETURN l\_sql;  
END;