Oracle APEX: How to extract text from inside a PDF or Word Doc

In this video we will show you how to quickly extract all the text from inside a pdf or word document and store the information inside a CLOB for quick retrieval.
Our video begins with a table containing a BLOB column with several pdf's and .doc's. We will start by creating a filter on the table to index the documents leveraging CTXSYS.CONTEXT and CTXSYS.AUTO_FILTER.
Next we will create a table with a CLOB column to store the text inside of the pdf. Once created we showcase a short segment of code leveraging the ctx_doc.filter API to process all the BLOBs extracting the raw text into the CLOB.
With the information inside a CLOB Oracle APEX can now easily display and process the text to users at a fraction of the time it would take to display or download the BLOB.
Sample Code leveraged in this video:
github.com/chipbaber/apex_tex...
Related Videos of Interest:
How to print a CLOB inside a Dialog Window in Oracle APEX
• How to Print a CLOB in...
How to enable Full Text Search on a BLOB
• Oracle APEX: How to en...
Oracle APEX: How to add an Image as BLOB to Existing Table/Form/Report
• Oracle APEX: How to ad...

[@organismisimbiotici]

wonderful! I've been trying to convert a PDF/BLOB to CLOB in APEX for days!! Thank you!

[@chipbaber]

glad it could help

[@asifiqbal5877]

Hello, can we upload records from oracle apex to an MDB format file?

[@chipbaber]

I am probably not the pro on this front with MS Access. But found this in the Oracle forums. asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9523935800346847870

[@deepakdakhore]

Hi I am getting with CTX_DOC package, I am using 21.2 apex version and Database 12C ORA-20000: Oracle Text error: DRG-50857: oracle error in ctx_doc.filter ORA-20000: Oracle Text error: DRG-11207: user filter command exited with status 127

[@chipbaber]

Check to see if the user running ctx_doc has create table and create trigger privileges. A Text index internally creates some tables like DR$$R, DR$$I, DR$$K.To be able to create those tables, these privs are required.

[@kauecastelani4417]

at here in oracle g11, does not work. Just staying an ' - ' after sucess process, do you know why?

[@chipbaber]

What if anything do you see when you query your filtered_doc table? Or is this error slightly before that? This demo was done on a DB 21c.

[@uselvan]

Hi., Same like can we extract the image from a word/docx file.?

[@chipbaber]

I don't believe there are any native libraries in the database for this today. It can be done though in Java, found this example. gist.github.com/aspose-com-gists/7af5b641d0ab658dbddce3292649c227 So one path could be to use Oracle functions and java to consume the doc and output the images, then save the images inside the database or in object storage.

[@luisf.rodriguezgarcia2888]

Hi, I have done all steps and works really fine, but when I try to convert a large pdf (54 pages) it only give me a string like this SKM_C3320i23022111200 in the filtered_docs table. I'm wondering if there's any limit of size with this index ?

[@chipbaber]

So the filtered docs table stores the result of the index inside a CLOB. The max size of a CLOB should easily handle 54 MB. docs.oracle.com/en/database/oracle/oracle-database/19/refrn/datatype-limits.html . Couple small checks you probably tried already but just in case. After upload make sure to rebuild the index, example at bottom of markdown page resumeAdmin.Batch_Create_Filtered_Docs(); If that doesn't work and the PDF is something you can share I can take a look at it if you shoot me a link.

[@luisf.rodriguezgarcia2888]

@@chipbaberThanks !

[@luisf.rodriguezgarcia2888]

@@chipbaber Hi Chip, I sent you an email