Classify a new upload with a language model

All tutorials

By the end of this lesson, you can classify a new PDF with a language model and read the tag and annotations that it wrote.

Lesson 1 of 5~25 minScript: 00_precheck.sql, install.sql, 00_load_pdfs.sql, wait.sql, 01_read_result.sql

1. Lesson 1: Classify a new upload with a language model
2. Lesson 2: Measure the classifier and correct a wrong answer
3. Lesson 3: Handle re-runs, new versions and failures
4. Lesson 4: Review and release verdicts
5. Lesson 5: Classify the documents already in ADM

What the classifier does

In this course, a language model sorts PDFs and images as they arrive in ADM. A person uploads a file. The model answers with a document type, such as invoice or contract, and one sentence that gives its reason. The classifier writes the type into ADM as a tag and as annotations.

The upload does not wait for the model. The upload hook only queues an event, and a worker handles the event in the background. The course builds on the hooks demo app and adds one workflow to it.

The course has five lessons:

1. Classify a new upload (this lesson).
2. Measure the classifier and correct a wrong answer.
3. Handle re-runs, new versions and failures.
4. Review and release verdicts.
5. Classify the documents that are already in ADM.

Terms used in the course

• A hook is a procedure that ADM calls when a file is uploaded. A workflow is a procedure that the worker of the hooks demo runs for the event that a hook queued. You do not need the hooks course to follow this one.
• UC AI is the PL/SQL library of the AI Pack that calls language models. The classifier uses its procedure uc_ai.generate_text. The UC AI documentation is the reference for providers, models and structured output.
• The document type is one of the types that you define in a table, for example invoice.
• The verdict is the document type and the reason that the model gave.
• A tag is a label that people see on a document in ADM. An annotation is a key and a value that ADM stores with a document or a folder. Programs read annotations; people rarely see them.
• A verdict is released when it takes effect without a person. A release is the annotation kb.<type> on the document, for example kb.invoice. The prefix kb stands for knowledge base. A RAG collection of the AI Pack can select files by that key, and lesson 4 shows one.
• A token is a unit of text that the model counts. Providers charge by token.

Prepare the schema and the model access

Use a test schema. You need four things:

• The course scripts. Get the folder examples/hooks-demo-app from United-Codes/adm or from your delivery archive. Connect SQLcl or SQL*Plus as the ADM schema owner, and run every script from the subfolder ai.
• The AI Pack, and the model access. The AI Pack includes UC AI. The course reads an OpenAI key from an APEX web credential with the static ID OPENAI, and it uses the model gpt-6.1-sol.
• An ADM user named ADMIN. Each script that acts as a user has the line define hkd_user = ADMIN. If your ADM user has another name, change that line in every script, and change define hkd_folder_path in 05_backfill.sql to /users/<your user name in lower case>/hkd_archive.
• A schema without hooks of your own. The hooks demo takes the hook slots of the upload, version and folder events, because ADM keeps one snippet for each hook event. Its install stops when another snippet is registered. In that case, use a test schema without hooks.

You do not need to complete the hooks course first. Open the boxes below when a step reports a problem, or when you want another model:

Give the schema access to Advanced Queuing (DBA, once)

@../db/install.sql creates a queue. For that, a DBA gives the schema these two grants, directly and not through a role:

grant execute on sys.dbms_aqadm to <schema>;
grant execute on sys.dbms_aq to <schema>;

The script db/00_dba_grants.sql does the same, with the schema name as its parameter. Without the grants, the install in the next section stops with PLS-00201 on DBMS_AQADM.

Create the OPENAI web credential

In the APEX workspace of your schema, go to Workspace Utilities > Web Credentials and create a credential with these values:

• Static ID: OPENAI
• Authentication Type: HTTP Header
• Credential Name: Authorization
• Credential Secret: Bearer <your OpenAI key>

Set Valid for URLs to https://api.openai.com/. UC AI describes the credential in its OpenAI provider guide.

The worker runs as the system user and uses the workspace that adm_context_api.system_login selects. If your schema has one workspace, nothing more is needed. If it has several, that choice is not defined, so create the credential in each of them. The prerequisite check prints the workspace that it used.

Allow the database to call api.openai.com (network ACL and wallet)

A database that already calls web services from APEX usually needs nothing here. On an on-premises or self-managed database, a DBA must make two changes. The DBA allows the schema to reach api.openai.com on port 443, and puts the certificate chain of OpenAI into the wallet of the database. If one change is missing, the precheck below fails with ORA-24247 or ORA-29024. The UC AI guide Network Setup (ACL & Wallet) lists the steps.

Use another model, provider or credential

The recordings in this course come from gpt-6.1-sol. To use another model, provider or credential, make two changes before you run install.sql. Change the three define lines at the top of 00_precheck.sql. Then change the three constants c_provider, c_model and c_credential in 02_hkd_ai_api.pks. Both files are in the folder ai. If your key does not have gpt-6.1-sol, use gpt-5.4-mini: it classified the sample files in the model comparison of lesson 2. Another model gives other answers on the borderline files of lesson 2, and other token counts. The comparison script of lesson 2 has its own list of models. The optional script 04_collection.sql of lesson 4 names the credential OPENAI and the embedding model text-embedding-3-small on its own. Change them there too.

The recordings come from SQLcl on Oracle 26ai Free, with OpenAI gpt-6.1-sol, on 2026-10-02. The scripts target Oracle 19c and later, but this course was run only on 26ai. SQL*Plus prints the same rows with another column layout. Document IDs, result IDs, the wording of reasons and token counts differ in your run.

Run the lessons once, in order. To start again, run @99_hkd_ai_uninstall.sql. The course sends about 60 requests of a few hundred tokens each. The optional model comparison in lesson 2 sends 27 of them. The optional section at the end of lesson 4 needs Oracle 23ai, because it uses the native vector store. It also sends file text to the embedding model of OpenAI.

The classifier runs for every upload in the schema

install.sql registers workflows on the upload hook and the version hook. After the install, every PDF and image that any user uploads in this schema goes to OpenAI, not only the sample files. To stop the classifier, run update hkd_workflows set enabled_flag = 'N' where workflow_code like 'AI\_%' escape '\' and commit. Lesson 5 shows how to keep a folder away from the model.

Install the hooks demo

Run @../db/install.sql. The script installs the full hooks demo: the queue, the worker, and the hooks. It also brings the demo of the hooks course to its final state if you completed that course. You can run it again. If a statement fails, the script ends the SQLcl session. Open the box about the DBA grants when the error is PLS-00201.

Run the prerequisite check

Run @00_precheck.sql. It makes five checks and names the fix for each failure. The fifth check sends a one-page PDF to the model and uses about 120 tokens. It writes no data.

==========================================================
Classify uploads with an LLM - prerequisite check
==========================================================
1. The hooks demo is installed and both file hooks are registered.
2. The ADM user ADMIN exists.
3. UC AI is installed. Version 26.4.
4. The web credential OPENAI exists.
5. The model read the PDF: "Parking rules for the main building"
   Tokens used: 120. Workspace used: ADM.
If none of the five lines says FAILED, install the course objects with install.sql.

If a check fails, the script prints the error codes that it can cause, with the fix for each. Fix the cause with the boxes above, and run the check again.

Install the classifier and upload an invoice

Run these four scripts in order:

1. @install.sql creates the tables, the views, the package hkd_ai_api and three workflow rows. It also switches off the hooks demo workflow INTAKE_CLASSIFY, which guesses a category from the file name. Both workflows write the tag category, and two classifiers on one file would overwrite each other. The uninstall script switches INTAKE_CLASSIFY on again. If a statement fails, the script ends the SQLcl session. Fix the cause and run it again.
2. @00_load_pdfs.sql inv_ferrotek_1042.pdf loads one sample invoice into the folder hkd_inbox of the ADM user ADMIN. The upload runs as that user. The script also fills the table hkd_ai_labels with the expected type of each sample file (lesson 2). The sample PDFs are in the folder ai/samples/pdf.
3. @wait.sql waits until the worker has handled the event, for at most 150 seconds. The model call takes about ten seconds.
4. @01_read_result.sql reads what the classifier did.

=== tables and view ===
View HKD_AI_SKIP_FOLDERS_V created.
View HKD_AI_CANDIDATES_V created.
View HKD_AI_LATEST_V created.
6 rows merged.
=== hkd_ai_api ===
Package HKD_AI_API compiled
Package Body HKD_AI_API compiled
=== register workflows ===
3 rows merged.
1 row updated.
=== invalid objects ===
no rows selected
done.

  loaded: inv_ferrotek_1042.pdf (document 13827924371566821093072544983891)

The script 01_read_result.sql shows four result sets:

WORKFLOW_CODE         STATUS    SUMMARY
_____________________ _________ _____________________________________________________
DUPLICATE_SCAN        OK        Unique content, 2.3 KB
AI_CLASSIFY_UPLOAD    OK        Classified as invoice and released (450+41 tokens)
TAG_NAME    TAG_VALUE
___________ ____________
category    invoice
ANNOTATION_KEY    ANNOTATION_VALUE
_________________ ___________________
ai.model          gpt-6.1-sol
ai.run_date       2026-10-02
ai.status         released
kb.invoice        released
DOC_TYPE    STATUS         PROMPT_TOKENS    COMPLETION_TOKENS REASON
___________ ___________ ________________ ____________________ ___________________________________________________________________________________________________________________
invoice     RELEASED                 450                   41 The document is a supplier bill with an invoice number, itemized charges, a total amount due, and payment terms.

The first result set lists the workflows that ran for the file. The status OK means that the workflow ran. The status FLAGGED means that it ran and found something for a person. SKIPPED means that there was nothing to do, and FAILED means that it did not finish. DUPLICATE_SCAN comes from the hooks demo. AI_CLASSIFY_UPLOAD classified the file as an invoice and released the verdict. The two numbers are the tokens of the input and of the output.

The second and third result sets show what the workflow wrote into ADM: the tag category, and the annotations. The annotation kb.invoice is the release. The result set of the annotations leaves out the annotations that ADM writes itself, which start with file.. The fourth result set is the row in hkd_ai_results. The classifier keeps the reason of the model in this table and not in an annotation. The reason is text that came out of a document, and ADM’s agent tools show annotations to the chat agent.

The result does not appear

• wait.sql says that events are still waiting after 150 seconds: the worker does not run. Run @../db/install.sql again, and then @wait.sql.
• wait.sql says that the exception queue holds events: an event failed on every attempt. Run select workflow_code, summary from hkd_workflow_runs where status = 'FAILED' order by run_id desc to read the error. The hooks course shows the queue in Handle a workflow that fails.
• The queue is empty and 01_read_result.sql shows no AI_CLASSIFY_UPLOAD row: the hook is not registered. Run @00_precheck.sql and read check 1.

Define the document types

The classifier is the package hkd_ai_api. It reads the file from ADM, sends it to the model with a system prompt, and requires an answer in a fixed format. The allowed document types come from the table hkd_ai_doc_types:

Document type	Release rule	Meaning
invoice	AUTO	A bill from a supplier that asks for payment
quote	AUTO	An offer with prices that does not ask for payment yet
datasheet	AUTO	Technical data about a product
contract	REVIEW	An agreement or an amendment, with clauses and signatures
hr	REVIEW	A personal letter or record about an employee
other	REVIEW	Anything that is none of the types above

The release rule decides what happens to a valid verdict. A verdict of a type with the rule AUTO is released at once. A verdict of a type with the rule REVIEW waits for a person. A verdict also waits when a person has set another category tag for the document (lesson 3). Lesson 4 shows the review. A new row in the table needs no code change, and its release rule is AUTO unless you set REVIEW. Set REVIEW for every type whose wrong answers are costly.

A released verdict writes the tag category and the annotation kb.<type>. A verdict that waits gets the tag category and the tag needs-review. The annotations ai.status, ai.model and ai.run_date record how the verdict came.

Call the model with a response schema

The system prompt lists the types with their descriptions. The response schema allows only those types as the answer. The classifier builds the schema from the table when it runs:

for r in (select doc_type from hkd_ai_doc_types order by sort_order) loop
  l_enum.append(r.doc_type);
end loop;

l_type.put('type', 'string');
l_type.put('enum', l_enum);
l_reason.put('type', 'string');
l_properties.put('doc_type', l_type);
l_properties.put('reason', l_reason);

The call sends the file as part of the user message and asks UC AI to enforce the schema. For how a response schema works, see UC AI’s tutorial A schema the model must fill.

l_content.append(uc_ai_message_api.create_file_content(
  p_media_type => l_mime, p_data_blob => l_blob, p_filename => l_name));

l_response := uc_ai.generate_text(
  p_messages             => l_messages
, p_provider             => c_provider
, p_model                => c_model
, p_config               => json_object_t('{"g_apex_web_credential":"' || c_credential || '"}')
, p_response_json_schema => get_response_schema
);

The variable l_blob holds the file, read with adm_storage_api.get_file_content(l_version_id, p_from_sql => false). The messages hold the file, the question “Which type of document is this?” and the system prompt.

That function reads the file from the database or from object storage, as the document is stored. It does not check permissions. Call it only from code that runs as the system user, as the worker does.

The system prompt also tells the model that text inside a document is data and not an instruction. Lesson 2 comes back to this.

UC AI has a second call for this task, uc_ai.decide, which returns a probability for each document type. It is experimental and reads text, not PDF files. Lesson 2 compares it with this classifier in Another approach: get a probability for each type with uc_ai.decide.

Register the workflow of the classifier

The install script added the workflow AI_CLASSIFY_UPLOAD to the registry hkd_workflows, with the handler hkd_ai_api.classify_event. It also registered workflows for a new version of a file (lesson 3) and for a request to classify again (lessons 2 to 5). The sort order 50 makes INTAKE_CLASSIFY and DUPLICATE_SCAN run first. To write a workflow of your own, see Add a workflow of your own.

Measure the classifier and correct a wrong answerCompare the verdicts with labelled files, find the answer that is wrong, and correct it without changing code.