In forensics the evidence can be grouped into two blocks. The first is changes made to the database and the second is read activity. Usually an attackers goal is to steal data (read) but he may need to do changes to get that ability in the database; maybe adding a procedure to do SQL Injection or adding a grant to allow access or ....
For changes we can see these via audit if audit exists and has not been purged and if the correct audit trail settings have been enabled. Often sites do not have adequate audit trail for the database engine itself although they may have audit of the application layer. The application-level audit will not catch a procedure being added as the application level audit is probably not targeting this normally; it should be of course. We must always be auditing the use of the database engine itself as well.
We could also rely on redo if the database is in archive log mode and if the logs are available we could use log miner to analyse the changes in the logs but this is not as simple as it first seems as we need to know when and which logs to target based on time or SCN. If we have no idea when the attack started then it would be difficult to target the correct redo/archive logs.
Another idea is to use flashback which in a sense is similar to using redo. The redo has SQL REDO and SQL UNDO for change vectors and flashback uses UNDO to go back in time for a table, for instance. The same issue applies though, unless we know the time the attack occurred it would be very hard to isolate a change to data and use as audit.
Some changes can be tied to a date/timestamp as the relevant tables have these BUT some as we saw in my blog Forensic Analysis for records in Oracle with no Timestamp do not such as for grants of privileges. We can get very high-level guesses as to when a record might have changed but its related to the block level not the individual rows in the table. For instance if the sysauth$ table has a ROWSCN that translates to 27-MAY-2026 then that does not mean a specific row was changed on that day; well it does actually; that was the date the last specific row changed in that data block BUT it is likely not to be the record we are interested in.
To catch read actions we are much more limited. Again audit trail is the best hope for grabbing any read actions. If no audit is set up then redo does not help as READ / SELECT is not written to redo as it is not a change. For the same reason flashback also would not work.
There may be some incredibly limited cases where a READ is written to redo but that almost certainly does not help an investigation. For instance the table SYS.COL_USAGE$ might be written to. This table records columns used in a where clause and the type of predicate, equal, greater than etc. We could check tables such as COL_USAGE$ to see if a table was referenced in a where clause BUT if the attacker referenced the table without a where clause, then no evidence would be gleaned from this table.
If the database has not been shut down then we can look at transient data in the SGA via V$, X$ and GV$ views. We can look at the SQL in the V$SQL family of views or history views or library cache dumps and more. BUT if the database were shutdown, then this transient READ data is lost.
For the rest of this discussion I want to focus on change and on date/timestamps and SCNs. If we can build a timeline of what happened change wise then we may be able to piece together what happened in terms of an attack. If someone just simple read data as above then this does not help in the investigation.
The ideal goal is a reverse time listing over the period of the attack that includes data changes, redo, audit trails. The audit trail would hopefully include READ events as well.
What changes can we focus on that might help in an attack investigation? there are thousands of tables and views in the database that have date/timestamps and some that have SCN but we cannot check every table, view, procedure etc. What at the core actions we can look at now? we can extend this of course going forward but we have to start somewhere.
The main actions that would tell us a lot about changes in an Oracle database would include (USERS, ROLES, PROFILES, OBJECTS including TABLES and PL/SQL). These items would constitute changes to users, roles, grants, and creation of common database objects BUT any indications of READ or course. As I said my plan is to be able to see at a glance in reverse time order. The idea being that we can use this initial report to target where to look next. Layered onto this list of OBJECTS is the issue of what we can learn from meta data. If there are time stamps then this helps, even SCNs in a limited way can help BUT not of these will deal with deletion of objects or READ.
In the next part we will continue to look at some of these core objects and see what we can learn from them.
As I said my goal is to have a reverse time listing (last changes at the top) and highlight what major events happened in the database
#oracleace #oracleacepro #sym_42 #oracle #forensics #database #security #scn #hacking #databreach #timestamp #evidence
Blog
Pete Finnigan's Oracle Security Weblog
This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.
Generate a PL/SQL Encryption and Decryption Package, Again
First, I am appending this text here before I post this blog live as I realised its almost exactly 22 years since I started this blog in September 2004; so happy birthday to my blog!! Now to the actual subject.
I posted recently about the new Qwen3.8:27 open weights model and how I tried to repeat a previous simple test I did against qwen2.5-coder, qwen3-coder and qwen3-coder-next. The two articles are here:
AI Comparison for Oracle Security Code Generation
Using Qwen3.8:27b to create a PL/SQL encrypt/decrypt Package
The most recent test simply was out of the box LLM and it felt closer than the previous tests with earlier models BUT still did not compile straight away. I fixed some simple issues and then gave up as i have learned enough about it. It was fast but close. The bugs left were not complex.
In this blog I wanted to try again so i changed the temperature of the model to 0.2 from 0.7 and also added as comprehensive system prompt. I used this prompt to ask it to build the PL/SQL package. A lower temperature makes the answer more predictable and less creative and the system prompt helps guide the LLM. Here is the actual prompt that is added to the system prompt:
Passing this to qwen3.8:27b creates this package header
And this package body:
I have put this code in an SQL file and will run it as SYS so we do not need any privileges to be granted first. We would not do this in production of course but for this test its OK.
Running the file to compile shows:
Hmmm, yesterday it created a working base64 interface. I passed these errors to the model and it responded with a new package header/body - the header was not changed.
Trying to compile shows:
Pass this in again to the model and see what qwen3.8:27b answers; it did apologise for inventing UTL_RAW function names twice in a row:
Put this in my code file and try and compile again:
Yes, it compiled. The LLM also gave me a test code block:
And running this from a file (enc3.sql) gives me:
So, we ask qwen3.8:27b again what is wrong and it gives a pretty comprehensive set of checks / tests. Basically it says that AES256 was rejected at run time. but it complied and as you can see it failed in the DBMS_CRYPTO_FFI internal package. Qwen3,8:27b asked me to try this:
So, let me try that:
As the model said it should not be zero, so this looks fine. Next it asked me to try:
Obviously it failed with ORA-28827 and we are not using SSL so this has to be an incorrect cypher type BUT it compiled and the values looked ok. Lets try AES128 instead:
This compiled:
So, try the test again:
This also failed in the same way for AES128. The model responded to this by stating that 26ai is not in its model but asked me to give these details to it:
It then also tells me why it is asking for this. I already knew of course, it is so it can see the source code of the header of DBMS_CRYPTO and other object details BUT the source code is key and qwen3.8:27b states this and actually says why i am asking instead of guessing
Here is the new code:
Now we can compile this:
Now we can try and execute this version:
This clearly now works. I am impressed by HOW Qwen3.8:27b fixed the last run time issue. It decided it did not know the answer and instead of guessing (hallucinating) it decided on the correct strategy to fix the problem. It asked me to give it the package header source code for DBMS_CRYPTO so it could work out that in its knowledge it new to pass the encryption type/model such as AES256 but now the package also needed the chaining mode and packing mode. When it saw the DBMS_CRYPTO header source it could work this out and realise the need for chaining and packing and could then fix the code. The previous code compiled but obviously failed at run time and it corrected the code to still compile and now it works at run time.
I know this is not a frontier model but for a free open weights model and can run on customer hardware not a massive data center hardware. This is a very good model. I did tweak the temperature from 0.7 to 0.2 which basically means it should be less creative for coding this is fine as I want it to use correct syntax and not write a novel. I also added a comprehensive system prompt to tell it that its a SQL or PL/SQL Oracle programmer and I also told it to use high reasoning.
When you see coding demos on line for LLMs they are almost always app based or web site or single web page app based (JavaScript) and not Oracle PL/SQL or even vb.net or c#.net. They are almost always web based. But, we can use then smaller LLM open weight models on reasonable hardware such as my MacBook Pro M5 Max. AND, we have data sovereignty as my data never left my network and never passed any proprietary knowledge to a frontier model.
We could make the model better in specific areas such as Oracle security or coding with PL/SQL by using RAG and adding the Oracle documentation to the model as embedded chunks in a vector database. We can also open up the model to internet search using duckduckgo via AnythingLLM.
There is great potential to use local LLMs as an expert assistant but it works better if you already know or have an idea what you are looking for BUT I am really impressed by the steps qwen3.8:27b took to find and fix the issues in this simple example.
Also qwen3.8:27b is fast (not as fast as frontier models) but fast to use usefully on simple desktop hardware
#oracleace #oracleacepro #sym_42 #oracle #security #ai #llm #qwen3.8:27b #rag #encryption #dbms_crypto
I posted recently about the new Qwen3.8:27 open weights model and how I tried to repeat a previous simple test I did against qwen2.5-coder, qwen3-coder and qwen3-coder-next. The two articles are here:
AI Comparison for Oracle Security Code Generation
Using Qwen3.8:27b to create a PL/SQL encrypt/decrypt Package
The most recent test simply was out of the box LLM and it felt closer than the previous tests with earlier models BUT still did not compile straight away. I fixed some simple issues and then gave up as i have learned enough about it. It was fast but close. The bugs left were not complex.
In this blog I wanted to try again so i changed the temperature of the model to 0.2 from 0.7 and also added as comprehensive system prompt. I used this prompt to ask it to build the PL/SQL package. A lower temperature makes the answer more predictable and less creative and the system prompt helps guide the LLM. Here is the actual prompt that is added to the system prompt:
create a PL/SQL package that has a public encrypt function and public decrypt function to encrypt and decrypt data passed in. Also include a private base64 encode and decode functions that use the built in Oracle packages and call these internally in the encrypt and decrypt functions to give the end user a base64 data interface.
Passing this to qwen3.8:27b creates this package header
-- =============================================================
-- Package Specification (public interface)
-- =============================================================
CREATE OR REPLACE PACKAGE sec_utils
AS
/**
* Encrypt a plaintext string and return the result as a
* Base64-encoded VARCHAR2.
*
* @param plain_text The cleartext value to encrypt (must not be NULL).
* @param key The encryption key as VARCHAR2.
* For AES-256 the key must be exactly 32 bytes.
* @return Base64-encoded ciphertext string.
*/
FUNCTION encrypt (
plain_text IN VARCHAR2,
key IN VARCHAR2
) RETURN VARCHAR2;
/**
* Decrypt a Base64-encoded ciphertext string and return the
* original plaintext.
*
* @param cipher_text Base64-encoded ciphertext produced by encrypt()
* (must not be NULL).
* @param key The same encryption key used during encrypt().
* For AES-256 the key must be exactly 32 bytes.
* @return The decrypted plaintext string.
*/
FUNCTION decrypt (
cipher_text IN VARCHAR2,
key IN VARCHAR2
) RETURN VARCHAR2;
END sec_utils;
/
And this package body:
-- =============================================================
-- Package Body (implementation + private helpers)
-- =============================================================
CREATE OR REPLACE PACKAGE BODY sec_utils
AS
------------------------------------------------------------------
-- Encryption algorithm constant.
-- DBMS_CRYPTO.ENCRYPT_AES256 requires a 32-byte (256-bit) key.
-- Available from Oracle 10g onward.
------------------------------------------------------------------
c_enc_type CONSTANT PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES256; -- 1805
------------------------------------------------------------------
-- PRIVATE: Base64-encode a RAW value using UTL_ENCODE.
--
-- Note: UTL_ENCODE.BASE64_ENCODE accepts RAW up to 32 767 bytes
-- (the PL/SQL RAW size limit). If your ciphertext exceeds this
-- you would need a chunked approach or the 11gR2+ CLOB/BLOB
-- variants of DBMS_CRYPTO.
------------------------------------------------------------------
FUNCTION base64_encode (
data IN RAW
) RETURN VARCHAR2
IS
BEGIN
RETURN UTL_ENCODE.BASE64_ENCODE (raw_value => data);
END base64_encode;
------------------------------------------------------------------
-- PRIVATE: Base64-decode a VARCHAR2 value to RAW using UTL_ENCODE.
------------------------------------------------------------------
FUNCTION base64_decode (
data IN VARCHAR2
) RETURN RAW
IS
BEGIN
RETURN UTL_ENCODE.BASE64_DECODE (base64_encoded_value => data);
END base64_decode;
------------------------------------------------------------------
-- PUBLIC: Encrypt
--
-- 1. Convert VARCHAR2 plaintext to RAW.
-- 2. Convert VARCHAR2 key to RAW.
-- 3. Encrypt with DBMS_CRYPTO.
-- 4. Base64-encode the ciphertext for a text-based interface.
------------------------------------------------------------------
FUNCTION encrypt (
plain_text IN VARCHAR2,
key IN VARCHAR2
) RETURN VARCHAR2
IS
v_raw_key RAW (32767);
v_raw_plain RAW (32767);
v_raw_cipher RAW (32767);
BEGIN
-- Guard against NULL inputs
IF plain_text IS NULL THEN
RAISE_APPLICATION_ERROR (-20001, 'sec_utils.encrypt: plain_text must not be NULL');
END IF;
IF key IS NULL THEN
RAISE_APPLICATION_ERROR (-20002, 'sec_utils.encrypt: key must not be NULL');
END IF;
-- VARCHAR2 -> RAW (simple byte-level reinterpretation)
v_raw_plain := UTL_RAW.CAST_TO_RAW (plain_text);
-- Key -> RAW (must be 32 bytes for AES-256)
v_raw_key := UTL_RAW.CAST_TO_RAW (key);
-- AES-256 encryption
v_raw_cipher := DBMS_CRYPTO.ENCRYPT (
src => v_raw_plain,
key => v_raw_key,
typ => c_enc_type
);
-- Return Base64-encoded ciphertext
RETURN base64_encode (v_raw_cipher);
END encrypt;
------------------------------------------------------------------
-- PUBLIC: Decrypt
--
-- 1. Base64-decode the ciphertext string to RAW.
-- 2. Convert VARCHAR2 key to RAW.
-- 3. Decrypt with DBMS_CRYPTO.
-- 4. Convert RAW back to VARCHAR2.
------------------------------------------------------------------
FUNCTION decrypt (
cipher_text IN VARCHAR2,
key IN VARCHAR2
) RETURN VARCHAR2
IS
v_raw_key RAW (32767);
v_raw_cipher RAW (32767);
v_raw_plain RAW (32767);
BEGIN
-- Guard against NULL inputs
IF cipher_text IS NULL THEN
RAISE_APPLICATION_ERROR (-20003, 'sec_utils.decrypt: cipher_text must not be NULL');
END IF;
IF key IS NULL THEN
RAISE_APPLICATION_ERROR (-20004, 'sec_utils.decrypt: key must not be NULL');
END IF;
-- VARCHAR2 key -> RAW
v_raw_key := UTL_RAW.CAST_TO_RAW (key);
-- Base64 -> RAW ciphertext
v_raw_cipher := base64_decode (cipher_text);
-- AES-256 decryption
v_raw_plain := DBMS_CRYPTO.DECRYPT (
src => v_raw_cipher,
key => v_raw_key,
typ => c_enc_type
);
-- RAW -> VARCHAR2
RETURN UTL_RAW.CAST_FROM_RAW (v_raw_plain);
END decrypt;
END sec_utils;
/
I have put this code in an SQL file and will run it as SYS so we do not need any privileges to be granted first. We would not do this in production of course but for this test its OK.
Running the file to compile shows:
C:\AI\AnythingLLM\pl_sql>sqlplus sys/oracle@//192.168.56.34:1521/freepdb1 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 20 14:32:07 2026
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle AI Database 26ai Free Release 23.26.0.0.0 - Develop, Learn, and Run for Free
Version 23.26.0.0.0
SQL>
SQL> @enc2
Package created.
No errors.
Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY SEC_UTILS:
LINE/COL ERROR
-------- -----------------------------------------------------------------
25/5 PL/SQL: Statement ignored
25/12 PLS-00306: wrong number or types of arguments in call to
'BASE64_ENCODE'
37/5 PL/SQL: Statement ignored
37/12 PLS-00306: wrong number or types of arguments in call to
'BASE64_DECODE'
123/5 PL/SQL: Statement ignored
123/20 PLS-00302: component 'CAST_FROM_RAW' must be declared
SQL>
Hmmm, yesterday it created a working base64 interface. I passed these errors to the model and it responded with a new package header/body - the header was not changed.
-- =============================================================
-- Package Specification (unchanged no need to recompile)
-- =============================================================
CREATE OR REPLACE PACKAGE sec_utils
AS
FUNCTION encrypt (
plain_text IN VARCHAR2,
key IN VARCHAR2
) RETURN VARCHAR2;
FUNCTION decrypt (
cipher_text IN VARCHAR2,
key IN VARCHAR2
) RETURN VARCHAR2;
END sec_utils;
/
-- =============================================================
-- Package Body (corrected)
-- =============================================================
CREATE OR REPLACE PACKAGE BODY sec_utils
AS
-- AES-256 (requires 32-byte key). Oracle 10g+.
c_enc_type CONSTANT PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES256; -- 1805
------------------------------------------------------------------
-- PRIVATE: Base64-encode RAW VARCHAR2
-- UTL_ENCODE.BASE64_ENCODE(data IN RAW) RETURN VARCHAR2
------------------------------------------------------------------
FUNCTION base64_encode (
data IN RAW
) RETURN VARCHAR2
IS
BEGIN
RETURN UTL_ENCODE.BASE64_ENCODE (data); -- positional arg
END base64_encode;
------------------------------------------------------------------
-- PRIVATE: Base64-decode VARCHAR2 RAW
-- UTL_ENCODE.BASE64_DECODE(base64_encoded_value IN VARCHAR2) RETURN RAW
------------------------------------------------------------------
FUNCTION base64_decode (
data IN VARCHAR2
) RETURN RAW
IS
BEGIN
RETURN UTL_ENCODE.BASE64_DECODE (data); -- positional arg
END base64_decode;
------------------------------------------------------------------
-- PUBLIC: Encrypt
------------------------------------------------------------------
FUNCTION encrypt (
plain_text IN VARCHAR2,
key IN VARCHAR2
) RETURN VARCHAR2
IS
v_raw_key RAW (32767);
v_raw_plain RAW (32767);
v_raw_cipher RAW (32767);
BEGIN
IF plain_text IS NULL THEN
RAISE_APPLICATION_ERROR (-20001, 'sec_utils.encrypt: plain_text must not be NULL');
END IF;
IF key IS NULL THEN
RAISE_APPLICATION_ERROR (-20002, 'sec_utils.encrypt: key must not be NULL');
END IF;
-- VARCHAR2 RAW
v_raw_plain := UTL_RAW.CAST_TO_RAW (plain_text);
-- Key RAW (must be 32 bytes for AES-256)
v_raw_key := UTL_RAW.CAST_TO_RAW (key);
-- AES-256 encrypt
v_raw_cipher := DBMS_CRYPTO.ENCRYPT (
src => v_raw_plain,
key => v_raw_key,
typ => c_enc_type
);
-- RAW ciphertext Base64 VARCHAR2
RETURN base64_encode (v_raw_cipher);
END encrypt;
------------------------------------------------------------------
-- PUBLIC: Decrypt
------------------------------------------------------------------
FUNCTION decrypt (
cipher_text IN VARCHAR2,
key IN VARCHAR2
) RETURN VARCHAR2
IS
v_raw_key RAW (32767);
v_raw_cipher RAW (32767);
v_raw_plain RAW (32767);
BEGIN
IF cipher_text IS NULL THEN
RAISE_APPLICATION_ERROR (-20003, 'sec_utils.decrypt: cipher_text must not be NULL');
END IF;
IF key IS NULL THEN
RAISE_APPLICATION_ERROR (-20004, 'sec_utils.decrypt: key must not be NULL');
END IF;
-- Key RAW
v_raw_key := UTL_RAW.CAST_TO_RAW (key);
-- Base64 VARCHAR2 RAW ciphertext
v_raw_cipher := base64_decode (cipher_text);
-- AES-256 decrypt
v_raw_plain := DBMS_CRYPTO.DECRYPT (
src => v_raw_cipher,
key => v_raw_key,
typ => c_enc_type
);
-- RAW VARCHAR2 (corrected: CAST_FROM_VARCHAR2, not CAST_FROM_RAW)
RETURN UTL_RAW.CAST_FROM_VARCHAR2 (v_raw_plain);
END decrypt;
END sec_utils;
/
Trying to compile shows:
SQL> @enc2
Package created.
Warning: Package Body created with compilation errors.
SQL> sho err
Errors for PACKAGE BODY SEC_UTILS:
LINE/COL ERROR
-------- -----------------------------------------------------------------
104/5 PL/SQL: Statement ignored
104/20 PLS-00302: component 'CAST_FROM_VARCHAR2' must be declared
SQL>
Pass this in again to the model and see what qwen3.8:27b answers; it did apologise for inventing UTL_RAW function names twice in a row:
CREATE OR REPLACE PACKAGE BODY sec_utils
AS
-- AES-256 (requires 32-byte key). Oracle 10g+.
c_enc_type CONSTANT PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES256; -- 1805
------------------------------------------------------------------
-- PRIVATE: Base64-encode RAW to VARCHAR2
------------------------------------------------------------------
FUNCTION base64_encode (
data IN RAW
) RETURN VARCHAR2
IS
BEGIN
RETURN UTL_ENCODE.BASE64_ENCODE (data);
END base64_encode;
------------------------------------------------------------------
-- PRIVATE: Base64-decode VARCHAR2 to RAW
------------------------------------------------------------------
FUNCTION base64_decode (
data IN VARCHAR2
) RETURN RAW
IS
BEGIN
RETURN UTL_ENCODE.BASE64_DECODE (data);
END base64_decode;
------------------------------------------------------------------
-- PUBLIC: Encrypt
------------------------------------------------------------------
FUNCTION encrypt (
plain_text IN VARCHAR2,
key IN VARCHAR2
) RETURN VARCHAR2
IS
v_raw_key RAW (32767);
v_raw_plain RAW (32767);
v_raw_cipher RAW (32767);
BEGIN
IF plain_text IS NULL THEN
RAISE_APPLICATION_ERROR (-20001, 'sec_utils.encrypt: plain_text must not be NULL');
END IF;
IF key IS NULL THEN
RAISE_APPLICATION_ERROR (-20002, 'sec_utils.encrypt: key must not be NULL');
END IF;
-- VARCHAR2 to RAW
v_raw_plain := UTL_RAW.CAST_TO_RAW (plain_text);
-- Key to RAW (must be 32 bytes for AES-256)
v_raw_key := UTL_RAW.CAST_TO_RAW (key);
-- AES-256 encrypt
v_raw_cipher := DBMS_CRYPTO.ENCRYPT (
src => v_raw_plain,
key => v_raw_key,
typ => c_enc_type
);
-- RAW ciphertext to Base64 VARCHAR2
RETURN base64_encode (v_raw_cipher);
END encrypt;
------------------------------------------------------------------
-- PUBLIC: Decrypt
------------------------------------------------------------------
FUNCTION decrypt (
cipher_text IN VARCHAR2,
key IN VARCHAR2
) RETURN VARCHAR2
IS
v_raw_key RAW (32767);
v_raw_cipher RAW (32767);
v_raw_plain RAW (32767);
BEGIN
IF cipher_text IS NULL THEN
RAISE_APPLICATION_ERROR (-20003, 'sec_utils.decrypt: cipher_text must not be NULL');
END IF;
IF key IS NULL THEN
RAISE_APPLICATION_ERROR (-20004, 'sec_utils.decrypt: key must not be NULL');
END IF;
-- Key to RAW
v_raw_key := UTL_RAW.CAST_TO_RAW (key);
-- Base64 VARCHAR2 to RAW ciphertext
v_raw_cipher := base64_decode (cipher_text);
-- AES-256 decrypt
v_raw_plain := DBMS_CRYPTO.DECRYPT (
src => v_raw_cipher,
key => v_raw_key,
typ => c_enc_type
);
-- RAW to VARCHAR2
RETURN UTL_RAW.CAST_TO_VARCHAR2 (v_raw_plain);
END decrypt;
END sec_utils;
/
Put this in my code file and try and compile again:
SQL> @enc2
Package created.
Package body created.
SQL>
Yes, it compiled. The LLM also gave me a test code block:
-- SQL*Plus / SQLcl
SET SERVEROUTPUT ON SIZE 100000
DECLARE
v_plain VARCHAR2 (200) := 'Hello, this is a secret message.';
v_key VARCHAR2 (32) := '0123456789abcdef0123456789abcdef';
v_cipher VARCHAR2 (400);
v_decrypted VARCHAR2 (200);
BEGIN
v_cipher := sec_utils.encrypt (plain_text => v_plain, key => v_key);
DBMS_OUTPUT.PUT_LINE ('Ciphertext : ' || v_cipher);
v_decrypted := sec_utils.decrypt (cipher_text => v_cipher, key => v_key);
DBMS_OUTPUT.PUT_LINE ('Decrypted : ' || v_decrypted);
DBMS_OUTPUT.PUT_LINE ('Match : ' || (CASE WHEN v_decrypted = v_plain THEN 'YES' ELSE 'NO' END));
END;
/
And running this from a file (enc3.sql) gives me:
SQL> @enc3
DECLARE
*
ERROR at line 1:
ORA-28827: An invalid cipher type was passed to a PL/SQL function or procedure.
ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 3
ORA-06512: at "SYS.DBMS_CRYPTO", line 13
ORA-06512: at "SYS.SEC_UTILS", line 58
ORA-06512: at line 7
SQL>
So, we ask qwen3.8:27b again what is wrong and it gives a pretty comprehensive set of checks / tests. Basically it says that AES256 was rejected at run time. but it complied and as you can see it failed in the DBMS_CRYPTO_FFI internal package. Qwen3,8:27b asked me to try this:
SET SERVEROUTPUT ON SIZE 100000
DECLARE
v_aes256 PLS_INTEGER;
v_aes128 PLS_INTEGER;
BEGIN
v_aes256 := DBMS_CRYPTO.ENCRYPT_AES256;
v_aes128 := DBMS_CRYPTO.ENCRYPT_AES128;
DBMS_OUTPUT.PUT_LINE ('ENCRYPT_AES256 = ' || v_aes256);
DBMS_OUTPUT.PUT_LINE ('ENCRYPT_AES128 = ' || v_aes128);
END;
/
So, let me try that:
SQL> SET SERVEROUTPUT ON SIZE 100000
SQL>
SQL> DECLARE
2 v_aes256 PLS_INTEGER;
3 v_aes128 PLS_INTEGER;
4 BEGIN
5 v_aes256 := DBMS_CRYPTO.ENCRYPT_AES256;
6 v_aes128 := DBMS_CRYPTO.ENCRYPT_AES128;
7 DBMS_OUTPUT.PUT_LINE ('ENCRYPT_AES256 = ' || v_aes256);
8 DBMS_OUTPUT.PUT_LINE ('ENCRYPT_AES128 = ' || v_aes128);
9 END;
10 /
ENCRYPT_AES256 = 8
ENCRYPT_AES128 = 6
PL/SQL procedure successfully completed.
SQL>
As the model said it should not be zero, so this looks fine. Next it asked me to try:
SQL> SET SERVEROUTPUT ON SIZE 100000
SQL>
SQL> DECLARE
2 v_plain RAW (32767) := UTL_RAW.CAST_TO_RAW ('test data');
3 v_key16 RAW (32767) := UTL_RAW.CAST_TO_RAW ('1234567890abcdef'); -- 16 bytes
4 v_cipher RAW (32767);
5 v_plain2 RAW (32767);
6 BEGIN
7 -- AES-128 (constant value 33)
8 v_cipher := DBMS_CRYPTO.ENCRYPT (src => v_plain, key => v_key16, typ => DBMS_CRYPTO.ENCRYPT_AES128);
9 DBMS_OUTPUT.PUT_LINE ('AES128 cipher length: ' || UTL_RAW.LENGTH (v_cipher));
10
11 v_plain2 := DBMS_CRYPTO.DECRYPT (src => v_cipher, key => v_key16, typ => DBMS_CRYPTO.ENCRYPT_AES128);
12 DBMS_OUTPUT.PUT_LINE ('AES128 round-trip: ' || UTL_RAW.CAST_TO_VARCHAR2 (v_plain2));
13 END;
14 /
DECLARE
*
ERROR at line 1:
ORA-28827: An invalid cipher type was passed to a PL/SQL function or procedure.
ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 3
ORA-06512: at "SYS.DBMS_CRYPTO", line 13
ORA-06512: at line 8
SQL>
Obviously it failed with ORA-28827 and we are not using SSL so this has to be an incorrect cypher type BUT it compiled and the values looked ok. Lets try AES128 instead:
CREATE OR REPLACE PACKAGE BODY sec_utils
AS
-- AES-128: requires a 16-byte key. Available Oracle 10g+.
c_enc_type CONSTANT PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES128;
FUNCTION base64_encode (
data IN RAW
) RETURN VARCHAR2
IS
BEGIN
RETURN UTL_ENCODE.BASE64_ENCODE (data);
END base64_encode;
FUNCTION base64_decode (
data IN VARCHAR2
) RETURN RAW
IS
BEGIN
RETURN UTL_ENCODE.BASE64_DECODE (data);
END base64_decode;
FUNCTION encrypt (
plain_text IN VARCHAR2,
key IN VARCHAR2
) RETURN VARCHAR2
IS
v_raw_key RAW (32767);
v_raw_plain RAW (32767);
v_raw_cipher RAW (32767);
BEGIN
IF plain_text IS NULL THEN
RAISE_APPLICATION_ERROR (-20001, 'sec_utils.encrypt: plain_text must not be NULL');
END IF;
IF key IS NULL THEN
RAISE_APPLICATION_ERROR (-20002, 'sec_utils.encrypt: key must not be NULL');
END IF;
v_raw_plain := UTL_RAW.CAST_TO_RAW (plain_text);
v_raw_key := UTL_RAW.CAST_TO_RAW (key); -- must be exactly 16 bytes
v_raw_cipher := DBMS_CRYPTO.ENCRYPT (
src => v_raw_plain,
key => v_raw_key,
typ => c_enc_type
);
RETURN base64_encode (v_raw_cipher);
END encrypt;
FUNCTION decrypt (
cipher_text IN VARCHAR2,
key IN VARCHAR2
) RETURN VARCHAR2
IS
v_raw_key RAW (32767);
v_raw_cipher RAW (32767);
v_raw_plain RAW (32767);
BEGIN
IF cipher_text IS NULL THEN
RAISE_APPLICATION_ERROR (-20003, 'sec_utils.decrypt: cipher_text must not be NULL');
END IF;
IF key IS NULL THEN
RAISE_APPLICATION_ERROR (-20004, 'sec_utils.decrypt: key must not be NULL');
END IF;
v_raw_key := UTL_RAW.CAST_TO_RAW (key); -- must be exactly 16 bytes
v_raw_cipher := base64_decode (cipher_text);
v_raw_plain := DBMS_CRYPTO.DECRYPT (
src => v_raw_cipher,
key => v_raw_key,
typ => c_enc_type
);
RETURN UTL_RAW.CAST_TO_VARCHAR2 (v_raw_plain);
END decrypt;
END sec_utils;
/
This compiled:
SQL> @enc2
Package created.
Package body created.
SQL>
So, try the test again:
SQL> SET SERVEROUTPUT ON SIZE 100000
SQL>
SQL> DECLARE
2 v_plain VARCHAR2 (200) := 'Hello, this is a secret message.';
3 v_key VARCHAR2 (16) := '1234567890abcdef'; -- exactly 16 bytes
4 v_cipher VARCHAR2 (400);
5 v_decrypted VARCHAR2 (200);
6 BEGIN
7 v_cipher := sec_utils.encrypt (plain_text => v_plain, key => v_key);
8 DBMS_OUTPUT.PUT_LINE ('Ciphertext : ' || v_cipher);
9
10 v_decrypted := sec_utils.decrypt (cipher_text => v_cipher, key => v_key);
11 DBMS_OUTPUT.PUT_LINE ('Decrypted : ' || v_decrypted);
12 DBMS_OUTPUT.PUT_LINE ('Match : ' || (CASE WHEN v_decrypted = v_plain THEN 'YES' ELSE 'NO' END));
13 END;
14 /
DECLARE
*
ERROR at line 1:
ORA-28827: An invalid cipher type was passed to a PL/SQL function or procedure.
ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 3
ORA-06512: at "SYS.DBMS_CRYPTO", line 13
ORA-06512: at "SYS.SEC_UTILS", line 45
ORA-06512: at line 7
This also failed in the same way for AES128. The model responded to this by stating that 26ai is not in its model but asked me to give these details to it:
-- 1. What crypto-related packages exist?
SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE object_name LIKE '%CRYPTO%'
OR object_name LIKE '%ENCRYPT%'
ORDER BY owner, object_name;
-- 2. Full specification of DBMS_CRYPTO
SELECT line, text
FROM dba_source
WHERE name = 'DBMS_CRYPTO'
AND type = 'PACKAGE'
AND owner = 'SYS'
ORDER BY line;
-- 3. Signatures of ENCRYPT and DECRYPT specifically
SELECT object_name, argument_name, position, data_type, in_out
FROM all_arguments
WHERE owner = 'SYS'
AND package_name = 'DBMS_CRYPTO'
AND object_name IN ('ENCRYPT', 'DECRYPT')
ORDER BY object_name, position;
-- 4. Check if there is a newer/alternative crypto package
SELECT owner, object_name, object_type
FROM dba_objects
WHERE owner = 'SYS'
AND (object_name LIKE 'DBMS_%CRYPT%'
OR object_name LIKE 'DBMS_%SECR%'
OR object_name LIKE '%_ENCRYPT%')
ORDER BY object_name;
It then also tells me why it is asking for this. I already knew of course, it is so it can see the source code of the header of DBMS_CRYPTO and other object details BUT the source code is key and qwen3.8:27b states this and actually says why i am asking instead of guessing
Here is the new code:
CREATE OR REPLACE PACKAGE BODY sec_utils
AS
-- AES-CBC-PKCS5 (the convenience constant defined in the 26ai spec).
-- The AES key size (128/192/256) is determined by the key length:
-- 16 bytes -> AES-128
-- 24 bytes -> AES-192
-- 32 bytes -> AES-256
c_enc_type CONSTANT PLS_INTEGER := DBMS_CRYPTO.AES_CBC_PKCS5;
------------------------------------------------------------------
-- PRIVATE: Base64-encode RAW to VARCHAR2
------------------------------------------------------------------
FUNCTION base64_encode (
data IN RAW
) RETURN VARCHAR2
IS
BEGIN
RETURN UTL_ENCODE.BASE64_ENCODE (data);
END base64_encode;
------------------------------------------------------------------
-- PRIVATE: Base64-decode VARCHAR2 to RAW
------------------------------------------------------------------
FUNCTION base64_decode (
data IN VARCHAR2
) RETURN RAW
IS
BEGIN
RETURN UTL_ENCODE.BASE64_DECODE (data);
END base64_decode;
------------------------------------------------------------------
-- PUBLIC: Encrypt
-- key must be 16, 24, or 32 bytes for AES-128/192/256.
------------------------------------------------------------------
FUNCTION encrypt (
plain_text IN VARCHAR2,
key IN VARCHAR2
) RETURN VARCHAR2
IS
v_raw_key RAW (32767);
v_raw_plain RAW (32767);
v_raw_cipher RAW (32767);
BEGIN
IF plain_text IS NULL THEN
RAISE_APPLICATION_ERROR (-20001, 'sec_utils.encrypt: plain_text must not be NULL');
END IF;
IF key IS NULL THEN
RAISE_APPLICATION_ERROR (-20002, 'sec_utils.encrypt: key must not be NULL');
END IF;
-- VARCHAR2 to RAW
v_raw_plain := UTL_RAW.CAST_TO_RAW (plain_text);
-- Key to RAW
v_raw_key := UTL_RAW.CAST_TO_RAW (key);
-- AES-CBC-PKCS5 encrypt
v_raw_cipher := DBMS_CRYPTO.ENCRYPT (
src => v_raw_plain,
typ => c_enc_type,
key => v_raw_key
);
-- RAW ciphertext to Base64 VARCHAR2
RETURN base64_encode (v_raw_cipher);
END encrypt;
------------------------------------------------------------------
-- PUBLIC: Decrypt
------------------------------------------------------------------
FUNCTION decrypt (
cipher_text IN VARCHAR2,
key IN VARCHAR2
) RETURN VARCHAR2
IS
v_raw_key RAW (32767);
v_raw_cipher RAW (32767);
v_raw_plain RAW (32767);
BEGIN
IF cipher_text IS NULL THEN
RAISE_APPLICATION_ERROR (-20003, 'sec_utils.decrypt: cipher_text must not be NULL');
END IF;
IF key IS NULL THEN
RAISE_APPLICATION_ERROR (-20004, 'sec_utils.decrypt: key must not be NULL');
END IF;
-- Key to RAW
v_raw_key := UTL_RAW.CAST_TO_RAW (key);
-- Base64 VARCHAR2 to RAW ciphertext
v_raw_cipher := base64_decode (cipher_text);
-- AES-CBC-PKCS5 decrypt
v_raw_plain := DBMS_CRYPTO.DECRYPT (
src => v_raw_cipher,
typ => c_enc_type,
key => v_raw_key
);
-- RAW to VARCHAR2
RETURN UTL_RAW.CAST_TO_VARCHAR2 (v_raw_plain);
END decrypt;
END sec_utils;
/
Now we can compile this:
SQL> @enc2.sql
Package created.
Package body created.
SQL>
Now we can try and execute this version:
SQL> SET SERVEROUTPUT ON SIZE 100000
SQL>
SQL> DECLARE
2 v_plain VARCHAR2 (200) := 'Hello, this is a secret message.';
3 v_key VARCHAR2 (32) := '0123456789abcdef0123456789abcdef'; -- 32 bytes = AES-256
4 v_cipher VARCHAR2 (400);
5 v_decrypted VARCHAR2 (200);
6 BEGIN
7 v_cipher := sec_utils.encrypt (plain_text => v_plain, key => v_key);
8 DBMS_OUTPUT.PUT_LINE ('Ciphertext : ' || v_cipher);
9
10 v_decrypted := sec_utils.decrypt (cipher_text => v_cipher, key => v_key);
11 DBMS_OUTPUT.PUT_LINE ('Decrypted : ' || v_decrypted);
12 DBMS_OUTPUT.PUT_LINE ('Match : ' || (CASE WHEN v_decrypted = v_plain THEN 'YES' ELSE 'NO' END));
13 END;
14 /
Ciphertext :
616F665248627443326C7169327278472B70515272315170507171517A69695954594851736B4173
685378796F7357695A51332B483939785537536B766E7175
Decrypted : Hello, this is a secret message.
Match : YES
PL/SQL procedure successfully completed.
SQL>
This clearly now works. I am impressed by HOW Qwen3.8:27b fixed the last run time issue. It decided it did not know the answer and instead of guessing (hallucinating) it decided on the correct strategy to fix the problem. It asked me to give it the package header source code for DBMS_CRYPTO so it could work out that in its knowledge it new to pass the encryption type/model such as AES256 but now the package also needed the chaining mode and packing mode. When it saw the DBMS_CRYPTO header source it could work this out and realise the need for chaining and packing and could then fix the code. The previous code compiled but obviously failed at run time and it corrected the code to still compile and now it works at run time.
I know this is not a frontier model but for a free open weights model and can run on customer hardware not a massive data center hardware. This is a very good model. I did tweak the temperature from 0.7 to 0.2 which basically means it should be less creative for coding this is fine as I want it to use correct syntax and not write a novel. I also added a comprehensive system prompt to tell it that its a SQL or PL/SQL Oracle programmer and I also told it to use high reasoning.
When you see coding demos on line for LLMs they are almost always app based or web site or single web page app based (JavaScript) and not Oracle PL/SQL or even vb.net or c#.net. They are almost always web based. But, we can use then smaller LLM open weight models on reasonable hardware such as my MacBook Pro M5 Max. AND, we have data sovereignty as my data never left my network and never passed any proprietary knowledge to a frontier model.
We could make the model better in specific areas such as Oracle security or coding with PL/SQL by using RAG and adding the Oracle documentation to the model as embedded chunks in a vector database. We can also open up the model to internet search using duckduckgo via AnythingLLM.
There is great potential to use local LLMs as an expert assistant but it works better if you already know or have an idea what you are looking for BUT I am really impressed by the steps qwen3.8:27b took to find and fix the issues in this simple example.
Also qwen3.8:27b is fast (not as fast as frontier models) but fast to use usefully on simple desktop hardware
#oracleace #oracleacepro #sym_42 #oracle #security #ai #llm #qwen3.8:27b #rag #encryption #dbms_crypto
Extreme PL/SQL - Creating a simple programming language interpreter in PL/SQL
Last week I posted a blog - Extreme PL/SQL - Running an Assembly Language Program in PL/SQL where I presented the fact that I have written a virtual machine on PL/SQL and also an assembler in PL/SQL and showed how I can pass an assembly language program to my PL/SQL assembler and produce machine code that is then passed to my PL/SQL virtual machine which executes the machine code.
I presented a simple language that I wrote an interpreter for back in 2022 - 2024. It started as a very simple version of the BASIC language and it and then transitioned to a less BASIC like language. It has some limitations at this point such as it does not support strings as function parameters but it is a language that can be used for simple scripts.
Creating an interpreter is complex and this is around 1200 lines of PL/SQL at the moment. This interpreter does not execute machine code in the VM it executes my simple language directly as a one pass lexer / parser / executor. I have many prepared articles on the design of this interpreter and I will be releasing those soon as well as the articles on my VM / ASM and also a compiler that creates assembly language for my VM from a simple language. I will also be demoing how to embed a scripting language in an existing PL/SQL application and I have two more areas I might include. I have a part written C compiler written in PL/SQL to generate my assembly language or even direct to machine code (the back end is not written yet).
Would it be great to extend PL/SQL applications using C as the scripting language?
One further area I have been looking at is transpilers also written in PL/SQL of course using the same trend / ideas to add features to a language such as PL/SQL in similar way to how I added pseudo ASM instructions to my assembler but not the VM. An example is that I added a SETSP op code, mnemonic to the assembly language to make dynamic allocation of the stack easier. The assembler converts the SETSP op code to MOV instructions. In a similar way it might be good to extend PL/SQL to have extra features such as lv_var++; or #define from the C world. I know PL/SQL has some level of this. The idea with a transpiler is to allow extra syntax and transpile it into native PL/SQL. So lv_var++; becomes transpiled into lv_var:=lv_var+1; and so on.
There are also other tools that i may write, or may not. I am thinking to add a debugger interface to the VM so that an assembly program that is running in the VM can be stepped through or breakpoints set etc. This also then means the debugger interface could be used at the source level of the high level language as an interface to a source level debugger
In this blog I want to talk about how I have written an interpreter for my simple language. The language is defined in the header for the PL/SQL package as follows (included in the PL/SQL source code):
I implemented the interpreter in PL/SQL. The package has two public procedures. Init() is used to set up the interpreter and also to control trace. Yes, it has trace built in to aid debugging. The VM also had trace as did the Assembler. The second public function of the interpreter is run() which simply runs a provided program written in my simple language. An example 30 line program, well its 29 but 30 sounded better in the comment and print statements is here:
This program exercises quite a few of the language features; it uses recursive function calls, nested function calls, IF THEN / ELSE / FI, most of the arithmetic and comparison of the expression parser as well as comments and LET to assign variables. It does not use GOTO or labels and does not include a loop but we also have these in the language. We do not support FOR... or DO ... WHILE or WHILE ... but all of these constructs can be added with just LOOP ... EXIT ... POOL and in fact adding these syntaxes would be a good use for a pre-compiler or transpiler.
Running the example shows:
This is a good demo to show the usefulness of the simple language. It is not C or C++ but its a good simple script language and shows how PL/SQL can be exercised with some effort
#oracleace #oracleacepo #sym_42 #extreme #plsql #interpreter #vm #assembler #compiler #scripting #language
I presented a simple language that I wrote an interpreter for back in 2022 - 2024. It started as a very simple version of the BASIC language and it and then transitioned to a less BASIC like language. It has some limitations at this point such as it does not support strings as function parameters but it is a language that can be used for simple scripts.
Creating an interpreter is complex and this is around 1200 lines of PL/SQL at the moment. This interpreter does not execute machine code in the VM it executes my simple language directly as a one pass lexer / parser / executor. I have many prepared articles on the design of this interpreter and I will be releasing those soon as well as the articles on my VM / ASM and also a compiler that creates assembly language for my VM from a simple language. I will also be demoing how to embed a scripting language in an existing PL/SQL application and I have two more areas I might include. I have a part written C compiler written in PL/SQL to generate my assembly language or even direct to machine code (the back end is not written yet).
Would it be great to extend PL/SQL applications using C as the scripting language?
One further area I have been looking at is transpilers also written in PL/SQL of course using the same trend / ideas to add features to a language such as PL/SQL in similar way to how I added pseudo ASM instructions to my assembler but not the VM. An example is that I added a SETSP op code, mnemonic to the assembly language to make dynamic allocation of the stack easier. The assembler converts the SETSP op code to MOV instructions. In a similar way it might be good to extend PL/SQL to have extra features such as lv_var++; or #define from the C world. I know PL/SQL has some level of this. The idea with a transpiler is to allow extra syntax and transpile it into native PL/SQL. So lv_var++; becomes transpiled into lv_var:=lv_var+1; and so on.
There are also other tools that i may write, or may not. I am thinking to add a debugger interface to the VM so that an assembly program that is running in the VM can be stepped through or breakpoints set etc. This also then means the debugger interface could be used at the source level of the high level language as an interface to a source level debugger
In this blog I want to talk about how I have written an interpreter for my simple language. The language is defined in the header for the PL/SQL package as follows (included in the PL/SQL source code):
--
-- ============================================================
-- PFCLScript BNF (derived from pfclscript package body v1.15)
-- ============================================================
-- A complete program
--::= { } "END"
-- One statement (the unit dispatched by DoCommand)
--::=
I implemented the interpreter in PL/SQL. The package has two public procedures. Init() is used to set up the interpreter and also to control trace. Yes, it has trace built in to aid debugging. The VM also had trace as did the Assembler. The second public function of the interpreter is run() which simply runs a provided program written in my simple language. An example 30 line program, well its 29 but 30 sounded better in the comment and print statements is here:
declare
lv_prog varchar2(32767) := q'[
REM "PFCLScript 30-line demo: recursion and nested calls"
FUN fact(n)
IF n<2 THEN
LET fact=1
ELSE
LET fact=n*fact(n-1)
FI
NUF
FUN pow(b;e)
IF e=0 THEN
LET pow=1
ELSE
LET pow=b*pow(b;e-1)
FI
NUF
FUN max(a;b)
IF a>b THEN
LET max=a
ELSE
LET max=b
FI
NUF
REM "--- Main ---"
PRINT "PFCLScript 30-line demo"
PRINT "fact(5) = ";fact(5)
PRINT "pow(2;10) = ";pow(2;10)
PRINT "max(fact(4);pow(3;3)) = ";max(fact(4);pow(3;3))
LET r=pow(fact(2);3)+fact(4)/2
PRINT "pow(fact(2);3)+fact(4)/2 = ";r
END
]';
begin
pfclscript.init(true, 1);
pfclscript.run(lv_prog);
end;
/
sho err
--l
This program exercises quite a few of the language features; it uses recursive function calls, nested function calls, IF THEN / ELSE / FI, most of the arithmetic and comparison of the expression parser as well as comments and LET to assign variables. It does not use GOTO or labels and does not include a loop but we also have these in the language. We do not support FOR... or DO ... WHILE or WHILE ... but all of these constructs can be added with just LOOP ... EXIT ... POOL and in fact adding these syntaxes would be a good use for a pre-compiler or transpiler.
Running the example shows:
SQL> declare
2 lv_prog varchar2(32767) := q'[
3 REM "PFCLScript 30-line demo: recursion and nested calls"
4 FUN fact(n)
5 IF n<2 THEN
6 LET fact=1
7 ELSE
8 LET fact=n*fact(n-1)
9 FI
10 NUF
11 FUN pow(b;e)
12 IF e=0 THEN
13 LET pow=1
14 ELSE
15 LET pow=b*pow(b;e-1)
16 FI
17 NUF
18 FUN max(a;b)
19 IF a>b THEN
20 LET max=a
21 ELSE
22 LET max=b
23 FI
24 NUF
25 REM "--- Main ---"
26 PRINT "PFCLScript 30-line demo"
27 PRINT "fact(5) = ";fact(5)
28 PRINT "pow(2;10) = ";pow(2;10)
29 PRINT "max(fact(4);pow(3;3)) = ";max(fact(4);pow(3;3))
30 LET r=pow(fact(2);3)+fact(4)/2
31 PRINT "pow(fact(2);3)+fact(4)/2 = ";r
32 END
33 ]';
34 begin
35 pfclscript.init(true, 1);
36 pfclscript.run(lv_prog);
37 end;
38 /
PFCLScript 30-line demo
fact(5) = 120
pow(2;10) = 1024
max(fact(4);pow(3;3)) = 27
pow(fact(2);3)+fact(4)/2 = 20
PFCLScript Execution Time (Seconds) : +000000 00:00:00.013464000
SQL> sho err
No errors.
SQL>
This is a good demo to show the usefulness of the simple language. It is not C or C++ but its a good simple script language and shows how PL/SQL can be exercised with some effort
#oracleace #oracleacepo #sym_42 #extreme #plsql #interpreter #vm #assembler #compiler #scripting #language
Extreme PL/SQL - Running an Assembly Language Program in PL/SQL
Back in 2022 I started work on an idea that I could build an interpreter for a simple language in PL/SQL and then building on that create a Virtual Machine in PL/SQL and an assembler in PL/SQL for the assembly language used by the virtual machine and then create a compiler also written in PL/SQL that could be used to compile a simple language into Assembler that is then assembled into machine code and run in the VM written in PL/SQL.
The article from 2022 is Adding Scripting Languages to PL/SQL Applications - Part 1
Back in 2024 I did a lot of work on the interpreter written in PL/SQL and created a couple of blog posts that showed some simple programs being executed in the interpreter.
The links from 2024 are Extreme PL/SQL - An Interpreter for a Simple Language and Write An Interpreter in PL/SQL - Adding More Features
I went on to complete that interpreter and I have around 150 pages of notes that I will publish over the coming months as a set of articles. Watch out for that.
I have also completed the virtual machine in written in PL/SQL and a test suite for that machine as well as writing an assembler also written in PL/SQL and a test suite for the assembler.
Why do i want to do all of this?
I write in C and have a number of systems / tools that are very powerful as I embed the Lua engine into C so that some of the functionality can be scripted at run time. For instance our obfuscator for dynamic obfuscation uses Lua to do this. This is a fantastic model. I wanted to be able to do something similar in PL/SQL applications so that the application could be extended at run time via scripts. I know PL/SQL could be used for this BUT if you allowed an end user to write random PL/SQL at run time and have it executed by your application that is a recipe for disaster. A better approach is for the original PL/SQL developer to embed a script engine and expose only what it needs of the original application to the end user script writer. This could be specific data or specific functions or procedures.
I will demo the script embedding soon in a blog here. I will be releasing a set of articles about the interpreter as I said above as well as a set of articles about the VM, assembler and compiler and finally a set of articles about embedding a script engine in your PL/SQL.
Today I want to show a simple example of executing assembly language program from PL/SQL. I have created a simple assembler program that calculates the 5th factorial. This program is written in assembler and is assembled to machine code for my VM written in PL/SQL. The assembler program is here:
And embedding the program into a simple PL/SQL harness to run it is here:
The results of running the program are here:
This works and show the correct value of 120 for a factorial of 5. It used a sub-program to do the calculation. 5! is 5*4*3*2*1 = 120. This means we can run programs written in Assembler in PL/SQL.
The next demo is another common demo. We will calculate the 10th Fibonacci number which should be 55 when we start the sequence at 1. The assembly language program is:
And inserting this in the test harness is as follows:
When I run this we get:
This works correctly and show the tenth Fibonacci number.
The assembly language code is assembled via a PL/SQL assembler to binary machine code and then executed in a Virtual Machine (VM) written also in PL/SQL. This is just a simple test to show the system working. I will post more detailed blogs/articles over the coming months of the design and build of the VM and assembler as well as test suites to check that they both work and also a complete set of articles showing how i designed and created a compiler written in PL/SQL for a simple language that is compiled into assembly language for the VM presented today.
I also have around 150 pages of notes and articles about the interpreter started in 2022 / 2024. I have over 250 pages of articles and notes for each system combined (the VM, ASM, Compiler) and the Interpreter all written in PL/SQL. Keep an eye out, I will be releasing the series of articles as I have spent a huge amount of time and work on these. I will also release a short series showing how the compiler/VM/ASM or Interpreter can be embedded in an existing PL/SQL application so that it can be scripted at run time without exposing the ability to add dynamic PL/SQL to the end user.
I may release all three sets of articles as a complete PDF book if anyone is interested BUT after all articles are released individually.
#oracleace #oracleacepro #sym_42 #oracle #plsql #interpreter #compiler #asm #assembler #vm #virtualmachine #machinecode
The article from 2022 is Adding Scripting Languages to PL/SQL Applications - Part 1
Back in 2024 I did a lot of work on the interpreter written in PL/SQL and created a couple of blog posts that showed some simple programs being executed in the interpreter.
The links from 2024 are Extreme PL/SQL - An Interpreter for a Simple Language and Write An Interpreter in PL/SQL - Adding More Features
I went on to complete that interpreter and I have around 150 pages of notes that I will publish over the coming months as a set of articles. Watch out for that.
I have also completed the virtual machine in written in PL/SQL and a test suite for that machine as well as writing an assembler also written in PL/SQL and a test suite for the assembler.
Why do i want to do all of this?
I write in C and have a number of systems / tools that are very powerful as I embed the Lua engine into C so that some of the functionality can be scripted at run time. For instance our obfuscator for dynamic obfuscation uses Lua to do this. This is a fantastic model. I wanted to be able to do something similar in PL/SQL applications so that the application could be extended at run time via scripts. I know PL/SQL could be used for this BUT if you allowed an end user to write random PL/SQL at run time and have it executed by your application that is a recipe for disaster. A better approach is for the original PL/SQL developer to embed a script engine and expose only what it needs of the original application to the end user script writer. This could be specific data or specific functions or procedures.
I will demo the script embedding soon in a blog here. I will be releasing a set of articles about the interpreter as I said above as well as a set of articles about the VM, assembler and compiler and finally a set of articles about embedding a script engine in your PL/SQL.
Today I want to show a simple example of executing assembly language program from PL/SQL. I have created a simple assembler program that calculates the 5th factorial. This program is written in assembler and is assembled to machine code for my VM written in PL/SQL. The assembler program is here:
'; --- Caller ---
MOV 15, r0, -10 ; r0 = 5 (parameter)
LDA r1, fact ; r1 = address of fact
BRA r1, r5 ; CALL fact
COP r0, r2 ; r2 = return value
HLT
; --- Subroutine ---
fact:
COP r0, r3 ; r3 = n (preserve parameter)
MOV 15, r0, -14 ; r0 = 1 (result accumulator)
floop:
MUL r3, r0 ; r0 = r0 * r3 (result *= n)
MOV r3, r3, -1 ; r3 = r3 - 1 (decrement n; sets flag)
BRN floop ; if n != 0, continue
COP r0, r4 ; r4 = result
COP r5, r0 ; r0 = return address (from r5)
BRA r0, r5 ; RETURN'
And embedding the program into a simple PL/SQL harness to run it is here:
-- test_fact_5.sql
-- Pete Finnigan
-- 15.09.2026
-- Test PFCL_ASM with a simple factorial program
declare
l_asm varchar2(32767);
l_out varchar2(32767);
l_result number;
begin
-- Assemble the code to machine code
l_asm := pfcl_asm.assemble(
'; --- Caller ---
MOV 15, r0, -10 ; r0 = 5 (parameter)
LDA r1, fact ; r1 = address of fact
BRA r1, r5 ; CALL fact
COP r0, r2 ; r2 = return value
HLT
; --- Subroutine ---
fact:
COP r0, r3 ; r3 = n (preserve parameter)
MOV 15, r0, -14 ; r0 = 1 (result accumulator)
floop:
MUL r3, r0 ; r0 = r0 * r3 (result *= n)
MOV r3, r3, -1 ; r3 = r3 - 1 (decrement n; sets flag)
BRN floop ; if n != 0, continue
COP r0, r4 ; r4 = result
COP r5, r0 ; r0 = return address (from r5)
BRA r0, r5 ; RETURN'
);
-- Load the VM and run
pfcl_vm.init;
pfcl_vm.load(l_asm);
pfcl_vm.run;
-- Read the results from r4
l_result := pfcl_vm.get_reg(5);
dbms_output.put_line('r4 = ' || l_result);
dbms_output.put_line('clock = ' || pfcl_vm.get_clock);
end;
/
The results of running the program are here:
SQL> @test_fact_5
r4 = 120
clock = 48
PL/SQL procedure successfully completed.
SQL>
This works and show the correct value of 120 for a factorial of 5. It used a sub-program to do the calculation. 5! is 5*4*3*2*1 = 120. This means we can run programs written in Assembler in PL/SQL.
The next demo is another common demo. We will calculate the 10th Fibonacci number which should be 55 when we start the sequence at 1. The assembly language program is:
'; === main ===
MOV 15, r0, -5 ; r0 = 10 (Fibonacci parameter)
MOV 15, sp, 285 ; sp = 300 (past program end at 272)
LDA r1, fib ; r1 = address of fib
BRA r1, r5 ; CALL fib (r5 = return address)
COP r0, r2 ; r2 = fib(10) = 55
LDA r1, print ; r1 = address of print
BRA r1, r5 ; CALL print (r5 = return address)
HLT
; === fib(n): in r0, out r0 ===
fib:
STO r5, sp, 0 ; mem[sp] = return address
MOV sp, sp, 1 ; sp++
STO r0, sp, 0 ; mem[sp] = n
MOV sp, sp, 1 ; sp++
LDA r1, fib ; r1 = address of fib
STO r1, sp, 0 ; mem[sp] = address of fib
MOV sp, sp, 1 ; sp++
; Base case: n = 0
MOV r0, r0, 0 ; zero flag if n == 0
BRZ ret_zero
; Base case: n = 1
MOV r0, r0, -1 ; zero flag if n == 1
BRZ ret_one
; Recursive: fib(n) = fib(n-1) + fib(n-2)
; r0 = n-1 (from check above)
LDO sp, r1, -1 ; r1 = address of fib
BRA r1, r5 ; CALL fib(n-1)
STO r0, sp, 0 ; push fib(n-1)
MOV sp, sp, 1 ; sp++
LDO sp, r1, -3 ; r1 = n
MOV r1, r0, -2 ; r0 = n-2
LDO sp, r2, -2 ; r2 = address of fib
BRA r2, r5 ; CALL fib(n-2)
LDO sp, r1, -1 ; r1 = fib(n-1)
ADD r1, r0 ; r0 = fib(n-2) + fib(n-1)
MOV sp, sp, -4 ; pop 4
LDO sp, r1, 0 ; r1 = return address
BRA r1, r5 ; RETURN
ret_zero:
MOV sp, sp, -3
LDO sp, r1, 0
BRA r1, r5 ; RETURN
ret_one:
MOV 15, r0, -14 ; r0 = 1
MOV sp, sp, -3
LDO sp, r1, 0
BRA r1, r5 ; RETURN
; === print(n): in r0, prints decimal 0-999 ===
print:
COP r0, r3 ; r3 = N
MOV r3, r3, 0 ; zero flag if N == 0
BRZ print_zero
; Hundreds digit
COP r3, r4 ; r4 = N
COP 100, r6 ; r6 = 100 (100 > 14, safe as immediate)
DIV r6, r4 ; r4 = trunc(N / 100)
MOV r4, r4, 0 ; zero flag
BRZ no_hun
COP r4, r1 ; r1 = digit
MOV r1, r1, 48 ; r1 = ASCII
COP r1, ro ; ro = code
OUT
COP r4, r1 ; r1 = digit
COP 100, r2 ; r2 = 100
MUL r2, r1 ; r1 = digit * 100
COP r3, r2 ; r2 = N
SUB r1, r2 ; r2 = N - digit*100
COP r2, r3 ; r3 = remainder
no_hun:
; Tens digit
COP r3, r4 ; r4 = N
MOV 15, r6, -5 ; r6 = 10 (15 + -5 = 10)
DIV r6, r4 ; r4 = trunc(N / 10)
MOV r4, r4, 0 ; zero flag
BRZ no_ten
COP r4, r1 ; r1 = digit
MOV r1, r1, 48 ; r1 = ASCII
COP r1, ro ; ro = code
OUT
COP r4, r1 ; r1 = digit
MOV 15, r2, -5 ; r2 = 10 (15 + -5 = 10)
MUL r2, r1 ; r1 = digit * 10
COP r3, r2 ; r2 = N
SUB r1, r2 ; r2 = N - digit*10
COP r2, r3 ; r3 = remainder
no_ten:
; Ones digit
COP r3, r4 ; r4 = ones digit
COP r4, r1 ; r1 = digit
MOV r1, r1, 48 ; r1 = ASCII
COP r1, ro ; ro = code
OUT
COP r5, r1 ; r1 = return address
BRA r1, r5 ; RETURN
print_zero:
MOV 15, r1, 33 ; r1 = 48 (ASCII zero)
COP r1, ro
OUT
COP r5, r1 ; r1 = return address
BRA r1, r5 ; RETURN'
And inserting this in the test harness is as follows:
-- test_fib.sql
-- Pete Finnigan
-- 15.09.2026
-- Test PFCL_ASM with recursive Fibonacci(10) = 55
-- Result is printed to output buffer via OUT instruction
declare
l_asm varchar2(32767);
l_out varchar2(32767);
l_result number;
begin
l_asm := pfcl_asm.assemble(
'; === main ===
MOV 15, r0, -5 ; r0 = 10 (Fibonacci parameter)
MOV 15, sp, 285 ; sp = 300 (past program end at 272)
LDA r1, fib ; r1 = address of fib
BRA r1, r5 ; CALL fib (r5 = return address)
COP r0, r2 ; r2 = fib(10) = 55
LDA r1, print ; r1 = address of print
BRA r1, r5 ; CALL print (r5 = return address)
HLT
; === fib(n): in r0, out r0 ===
fib:
STO r5, sp, 0 ; mem[sp] = return address
MOV sp, sp, 1 ; sp++
STO r0, sp, 0 ; mem[sp] = n
MOV sp, sp, 1 ; sp++
LDA r1, fib ; r1 = address of fib
STO r1, sp, 0 ; mem[sp] = address of fib
MOV sp, sp, 1 ; sp++
; Base case: n = 0
MOV r0, r0, 0 ; zero flag if n == 0
BRZ ret_zero
; Base case: n = 1
MOV r0, r0, -1 ; zero flag if n == 1
BRZ ret_one
; Recursive: fib(n) = fib(n-1) + fib(n-2)
; r0 = n-1 (from check above)
LDO sp, r1, -1 ; r1 = address of fib
BRA r1, r5 ; CALL fib(n-1)
STO r0, sp, 0 ; push fib(n-1)
MOV sp, sp, 1 ; sp++
LDO sp, r1, -3 ; r1 = n
MOV r1, r0, -2 ; r0 = n-2
LDO sp, r2, -2 ; r2 = address of fib
BRA r2, r5 ; CALL fib(n-2)
LDO sp, r1, -1 ; r1 = fib(n-1)
ADD r1, r0 ; r0 = fib(n-2) + fib(n-1)
MOV sp, sp, -4 ; pop 4
LDO sp, r1, 0 ; r1 = return address
BRA r1, r5 ; RETURN
ret_zero:
MOV sp, sp, -3
LDO sp, r1, 0
BRA r1, r5 ; RETURN
ret_one:
MOV 15, r0, -14 ; r0 = 1
MOV sp, sp, -3
LDO sp, r1, 0
BRA r1, r5 ; RETURN
; === print(n): in r0, prints decimal 0-999 ===
print:
COP r0, r3 ; r3 = N
MOV r3, r3, 0 ; zero flag if N == 0
BRZ print_zero
; Hundreds digit
COP r3, r4 ; r4 = N
COP 100, r6 ; r6 = 100 (100 > 14, safe as immediate)
DIV r6, r4 ; r4 = trunc(N / 100)
MOV r4, r4, 0 ; zero flag
BRZ no_hun
COP r4, r1 ; r1 = digit
MOV r1, r1, 48 ; r1 = ASCII
COP r1, ro ; ro = code
OUT
COP r4, r1 ; r1 = digit
COP 100, r2 ; r2 = 100
MUL r2, r1 ; r1 = digit * 100
COP r3, r2 ; r2 = N
SUB r1, r2 ; r2 = N - digit*100
COP r2, r3 ; r3 = remainder
no_hun:
; Tens digit
COP r3, r4 ; r4 = N
MOV 15, r6, -5 ; r6 = 10 (15 + -5 = 10)
DIV r6, r4 ; r4 = trunc(N / 10)
MOV r4, r4, 0 ; zero flag
BRZ no_ten
COP r4, r1 ; r1 = digit
MOV r1, r1, 48 ; r1 = ASCII
COP r1, ro ; ro = code
OUT
COP r4, r1 ; r1 = digit
MOV 15, r2, -5 ; r2 = 10 (15 + -5 = 10)
MUL r2, r1 ; r1 = digit * 10
COP r3, r2 ; r2 = N
SUB r1, r2 ; r2 = N - digit*10
COP r2, r3 ; r3 = remainder
no_ten:
; Ones digit
COP r3, r4 ; r4 = ones digit
COP r4, r1 ; r1 = digit
MOV r1, r1, 48 ; r1 = ASCII
COP r1, ro ; ro = code
OUT
COP r5, r1 ; r1 = return address
BRA r1, r5 ; RETURN
print_zero:
MOV 15, r1, 33 ; r1 = 48 (ASCII zero)
COP r1, ro
OUT
COP r5, r1 ; r1 = return address
BRA r1, r5 ; RETURN'
);
-- Load and run
pfcl_vm.init;
pfcl_vm.load(l_asm);
pfcl_vm.run;
-- Retrieve results
l_out := pfcl_vm.get_output;
dbms_output.put_line('output = [' || l_out || ']'); -- out=fib(10)
dbms_output.put_line('clock = ' || pfcl_vm.get_clock);
end;
/
When I run this we get:
SQL> @test_fib
output = [55]
clock = 6003
PL/SQL procedure successfully completed.
SQL>
This works correctly and show the tenth Fibonacci number.
The assembly language code is assembled via a PL/SQL assembler to binary machine code and then executed in a Virtual Machine (VM) written also in PL/SQL. This is just a simple test to show the system working. I will post more detailed blogs/articles over the coming months of the design and build of the VM and assembler as well as test suites to check that they both work and also a complete set of articles showing how i designed and created a compiler written in PL/SQL for a simple language that is compiled into assembly language for the VM presented today.
I also have around 150 pages of notes and articles about the interpreter started in 2022 / 2024. I have over 250 pages of articles and notes for each system combined (the VM, ASM, Compiler) and the Interpreter all written in PL/SQL. Keep an eye out, I will be releasing the series of articles as I have spent a huge amount of time and work on these. I will also release a short series showing how the compiler/VM/ASM or Interpreter can be embedded in an existing PL/SQL application so that it can be scripted at run time without exposing the ability to add dynamic PL/SQL to the end user.
I may release all three sets of articles as a complete PDF book if anyone is interested BUT after all articles are released individually.
#oracleace #oracleacepro #sym_42 #oracle #plsql #interpreter #compiler #asm #assembler #vm #virtualmachine #machinecode
Oracle Security AnythingLLM Tools
As you will have noticed in my blogs I have been using and playing with AI and Large Language Models (LLMs) for a while now but with a focus on Oracle Security still.
I want to understand the capabilities of running AI LLMs locally and do they offer value to my day job of Oracle security consulting, training and products and more generally can Oracle people; DBAs, Developers, Security people find value as well.
The key drivers for me are AI Sovereignty and protection of data. For years I have helped people protect data in Oracle databases and it seems AI has come along to drop us back 15 to 20 years; right to the beginning. People seem to be blind-sided by AI and think it is OK to open up the complete database schema to AI and transmit data in the form of prompts to AI providers.
I have spent years helping people lock down data using the controls within the core database and also helping implement additional cost and non-cost options such Database Vault, TDE, TSDP, audit trails, masking and basically everything possible including of course writing custom security code for Oracle databases.
One major gap in LLMs is the audit trails; yes there are some but they are not good enough. Any company serious about securing data in an Oracle should lock down that data, audit that data, control access to that data and have controls to provide logs of who accessed the data. In terms of LLMs we need to know who, when, why and what data was accessed (sent in a prompt) and what data was retrieved (response).
We should consider the design if we allow data from the database to be sent to an Large Language Model. We should limit or anonymise what data is sent and we should control the access paths to the data and ideally air gap it from production; in simple terms do not allow unfettered access to production with an LLM or agents or harnesses or...
In terms of whether an LLM is useful for day to day Oracle work; yes, it can be BUT it should not be a replacement for a DBA or developer. In other words don't think that you can reduce DBA roles and replace with an AI and use a harness, loop, agents and expect your database to work and be fully supported. In my experience so far of researching AI and using AI I have found that it may be great at vide coding or the new phrase One Shotting a web app or web based game or website BUT it seems to struggle with other areas. The main LLMs seem to be good at Javascript and HTML and CSS but less so with the details of Oracle databases or coding with PL/SQL or ...
There are clear gaps in using an LLM with Oracle; my guess would be because the main models have not been trained as well on the internals of Oracle and the documentation and there are also a lot less posts on various sources on the internet that focus on some Oracle tasks.
I think LLMs and particularly the open weight free ones are getting better and we can improve the use of LLMs with better more concise system prompts, tune the settings such as reasoning and temperature and context size. We can also teach open free weight models using tools such as Unsloth and we can of course use harnesses such as Pi or DeepSeek harenss to create agentic loops or graphs. There is a lot of power available with Local LLMs but it is clear they are getting better all of the time and it is clear that we need to put in a lot of effort in terms of inputs (RAG or training LoRA adaptors) to make them realy useful.
In simple terms the model needs to be set up and tuned and controlled and the right inputs need to be made available in terms of documentation fed into it via RAG or training and of course to use models successfully you need to be able to write prompts that work and be able to already know if the answer is correct or sounding correct. This is why I think the use of AI / LLMs is useful as an expert assistant and not a replacement.
MOST IMPORTANT; before you consider using either frontier models or set up your local LLM you need to consider auditing and data exposure as I said above BUT if you spend time on AI how does it provide ROI (Return On Investment) for you. There are mainly two ways; increase productivity to make more money by doing more in the same time or reduce costs. So, paying huge amounts for tokens that do not generate more turnover or revenue does not work. Use of AI should increase net profit otherwise what is the point.
I have been using a local AI set up with a Macbook Pro M5 MAX with 64gb of RAM for AI and normal use. This means I can run local models around 30 billion parameters easily and with good performance for one person (me) or larger models around 70B in some cases with reduced performance. I am using ollama to run the models and I was using OpenWebui to provide a chat interface but I wanted to move to AnythingLLM as a better model control interface and also to have some tools available OOTB (Out Of The Box). I am running the mac as a server and access OpenWebui and AnythingLLM from a Windows laptop via the browser.
Setting up AnythingLLM was easy. I had to download another model for the embedding nomic-embed-text. I also then had to pull anythingllm via docker on the mac and then use a docker command to run anythingllm on port 3001 within docker; I used port 3000 for OpenWebUI already. Once started I can then access the LLMs via AnythingLLM from a browser on my Windows laptop.
The real focus of AnythingLLM is the tools interface and the fact that you can upload documents and source code to it and these are indexed and stored in a vector database. The idea is that you can create different workspaces and different documents to use in the interface either by chat or by agents.
A first test i did was to ask Quen3.6:27b via AnythingLLM What is the latest version of the Oracle database for Windows?
The answer came back via AnythingLLM as:
Hmmm, obviously wrong BUT it explained why it is wrong; at the time the model was trained Oracle 23ai was the latest version.
So, using AnythingLLM I can improve this result by enabling the web search in the current workspace and enable DuckDuckGo search which is built in and send the same prompt again.
This time I get this answer:
So clearly we can combine the models own weights (knowledge) with RAG (documentation) and search (DuckDuckGo) and even teach a model with data and unsloth.
I will be testing more with AnythingLLM in respect to my work with Oracle and security and also newer models as they become available and update you here.
#oracleace #oracleacepro #sym_42 #oracle #database #security #llm #ai #anythingllm #unsloth #rag #duckduckgo #qwen
I want to understand the capabilities of running AI LLMs locally and do they offer value to my day job of Oracle security consulting, training and products and more generally can Oracle people; DBAs, Developers, Security people find value as well.
The key drivers for me are AI Sovereignty and protection of data. For years I have helped people protect data in Oracle databases and it seems AI has come along to drop us back 15 to 20 years; right to the beginning. People seem to be blind-sided by AI and think it is OK to open up the complete database schema to AI and transmit data in the form of prompts to AI providers.
I have spent years helping people lock down data using the controls within the core database and also helping implement additional cost and non-cost options such Database Vault, TDE, TSDP, audit trails, masking and basically everything possible including of course writing custom security code for Oracle databases.
One major gap in LLMs is the audit trails; yes there are some but they are not good enough. Any company serious about securing data in an Oracle should lock down that data, audit that data, control access to that data and have controls to provide logs of who accessed the data. In terms of LLMs we need to know who, when, why and what data was accessed (sent in a prompt) and what data was retrieved (response).
We should consider the design if we allow data from the database to be sent to an Large Language Model. We should limit or anonymise what data is sent and we should control the access paths to the data and ideally air gap it from production; in simple terms do not allow unfettered access to production with an LLM or agents or harnesses or...
In terms of whether an LLM is useful for day to day Oracle work; yes, it can be BUT it should not be a replacement for a DBA or developer. In other words don't think that you can reduce DBA roles and replace with an AI and use a harness, loop, agents and expect your database to work and be fully supported. In my experience so far of researching AI and using AI I have found that it may be great at vide coding or the new phrase One Shotting a web app or web based game or website BUT it seems to struggle with other areas. The main LLMs seem to be good at Javascript and HTML and CSS but less so with the details of Oracle databases or coding with PL/SQL or ...
There are clear gaps in using an LLM with Oracle; my guess would be because the main models have not been trained as well on the internals of Oracle and the documentation and there are also a lot less posts on various sources on the internet that focus on some Oracle tasks.
I think LLMs and particularly the open weight free ones are getting better and we can improve the use of LLMs with better more concise system prompts, tune the settings such as reasoning and temperature and context size. We can also teach open free weight models using tools such as Unsloth and we can of course use harnesses such as Pi or DeepSeek harenss to create agentic loops or graphs. There is a lot of power available with Local LLMs but it is clear they are getting better all of the time and it is clear that we need to put in a lot of effort in terms of inputs (RAG or training LoRA adaptors) to make them realy useful.
In simple terms the model needs to be set up and tuned and controlled and the right inputs need to be made available in terms of documentation fed into it via RAG or training and of course to use models successfully you need to be able to write prompts that work and be able to already know if the answer is correct or sounding correct. This is why I think the use of AI / LLMs is useful as an expert assistant and not a replacement.
MOST IMPORTANT; before you consider using either frontier models or set up your local LLM you need to consider auditing and data exposure as I said above BUT if you spend time on AI how does it provide ROI (Return On Investment) for you. There are mainly two ways; increase productivity to make more money by doing more in the same time or reduce costs. So, paying huge amounts for tokens that do not generate more turnover or revenue does not work. Use of AI should increase net profit otherwise what is the point.
I have been using a local AI set up with a Macbook Pro M5 MAX with 64gb of RAM for AI and normal use. This means I can run local models around 30 billion parameters easily and with good performance for one person (me) or larger models around 70B in some cases with reduced performance. I am using ollama to run the models and I was using OpenWebui to provide a chat interface but I wanted to move to AnythingLLM as a better model control interface and also to have some tools available OOTB (Out Of The Box). I am running the mac as a server and access OpenWebui and AnythingLLM from a Windows laptop via the browser.
Setting up AnythingLLM was easy. I had to download another model for the embedding nomic-embed-text. I also then had to pull anythingllm via docker on the mac and then use a docker command to run anythingllm on port 3001 within docker; I used port 3000 for OpenWebUI already. Once started I can then access the LLMs via AnythingLLM from a browser on my Windows laptop.
The real focus of AnythingLLM is the tools interface and the fact that you can upload documents and source code to it and these are indexed and stored in a vector database. The idea is that you can create different workspaces and different documents to use in the interface either by chat or by agents.
A first test i did was to ask Quen3.6:27b via AnythingLLM What is the latest version of the Oracle database for Windows?
The answer came back via AnythingLLM as:
Based on my latest training data, Oracle Database 23ai (version 23.0) is the newest major release, with 19c remaining the current Long-Term Support (LTS) version. Both are officially supported on Windows x64. Oracle frequently rolls out quarterly patches and minor updates, so for the exact latest build number, download links, and compatibility notes, please visit:
Official downloads: https://www.oracle.com/database/technologies/oracle-database-software-downloads.html
Patch/Release Notes: My Oracle Support (MOS) or OTN’s Release History page
If you share your Windows version and use case (development vs. production), I can help you choose the right edition, verify system requirements, or walk through installation/upgrading steps.
Hmmm, obviously wrong BUT it explained why it is wrong; at the time the model was trained Oracle 23ai was the latest version.
So, using AnythingLLM I can improve this result by enabling the web search in the current workspace and enable DuckDuckGo search which is built in and send the same prompt again.
This time I get this answer:
Based on the latest search results, the newest major version is Oracle AI Database 26ai (often referred to simply as Oracle Database 26).
This was officially released in early 2026 and serves as the new Long-Term Release (LTR), replacing Oracle Database 19c as the primary long-term support option for production environments. It is fully supported on Windows x64 and includes official installation guides for Windows (ntcli).
Key details:
Latest Major Version: Oracle AI Database 26ai (version 26.0+)
Release Type: Long-Term Release (LTS)
Windows Support: Yes, fully supported on Windows x64 platforms.
Predecessors: It succeeds Oracle Database 19c (previous LTS) and Oracle Database 23ai (which served as an interim release with a shorter support window).
You can download the latest installers for Windows directly from the Oracle AI Database Software Downloads page.
So clearly we can combine the models own weights (knowledge) with RAG (documentation) and search (DuckDuckGo) and even teach a model with data and unsloth.
I will be testing more with AnythingLLM in respect to my work with Oracle and security and also newer models as they become available and update you here.
#oracleace #oracleacepro #sym_42 #oracle #database #security #llm #ai #anythingllm #unsloth #rag #duckduckgo #qwen
Perform a Security Audit of an Old Oracle Database
We support doing security audits of all of the current Oracle databases from 19c to 21c and 26ai. We also support doing security audits on databases in your own data center or in the cloud. No matter where the database is we can audit it.
We get requests occasionally for old databases to be audited and had one such request to be able to audit a 9iR2 database. The customer wanted to use our scanner PFCLScan to do this but wanted a small footprint on the clients Windows PC where PFCLScan runs from.
We have a tool called OEMFrame.exe that was used for a previous collaboration with an Oracle tools vendor. This is a very cut down version of the complete scanner and much smaller and command line scans only BUT you can choose the OCI library needed (Oracle Call Interface not Cloud) and choose the report output type such as HTML, JSON, XML etc.
We can extract the command line tool from a complete scanner install and it then needs to be deployed simply as a zip file. Once deployed the customer needs to run a set up tool from the command line and then request a license key also from the command line and finally once we supply the license key locked to the installation it needs to be applied via a command line tool. Simple, quick and easy and command line only BUT it is a full scan of the database.
Running a scan is simple and is one command:
The username and password are passed in clear text here as they would be using SQL*Plus BUT they can be encrypted first and during all steps of the scan the username and password are encrypted even if passed in clear text. This is just a demo here to show the functionality.
The scan runs the full suite of thousands of security checks that the normal GUI version of PFCLScan runs.
A sample of a policy being executed and scanned is here:
Here is part of the HTML report generated:
We were asked by a customer to scan a 9.2.0.8 database using PFCLScan so we used our cut down scanner above and tested it locally here on an old 9.2.0.1 database that we had an old virtual Box VM of. This VM had not been started for just over 10 years but worked.
Because we do not scan 9iR2 or indeed 10gR2 or 11gR2 anymore in testing and we update the scanner checks on a regular basis we found a few small issues where we use more modern techniques to do things now that do not work in 9iR2. We fixed these in a customer specific download and now have a working version of the scanner that will scan 9iR2. 11gR2 should work as we tested 11gR2 much more recently and 10gR2 can be made to work easily or may work now if we test it
Why scan old databases?
Some customers will be forced to run old out of date Oracle databases in some cases. The usual reason is they still have customers on old systems that will age out and then the system will be decommissioned when all the customers have had the service completed. Some run old applications where the vendor is not available and they do not want to run on newer databases. There are systems around still that use older databases.
Whilst there are not any security patches available for 9iR2 (of course) there is still a lot of the database configuration and controls that can be changed and tightened to improve the security even of old databases.
#oracleace #oracleacepro #sym_42 #oracle #database #security #9ir2 #scanning #pfclscan
We get requests occasionally for old databases to be audited and had one such request to be able to audit a 9iR2 database. The customer wanted to use our scanner PFCLScan to do this but wanted a small footprint on the clients Windows PC where PFCLScan runs from.
We have a tool called OEMFrame.exe that was used for a previous collaboration with an Oracle tools vendor. This is a very cut down version of the complete scanner and much smaller and command line scans only BUT you can choose the OCI library needed (Oracle Call Interface not Cloud) and choose the report output type such as HTML, JSON, XML etc.
We can extract the command line tool from a complete scanner install and it then needs to be deployed simply as a zip file. Once deployed the customer needs to run a set up tool from the command line and then request a license key also from the command line and finally once we supply the license key locked to the installation it needs to be applied via a command line tool. Simple, quick and easy and command line only BUT it is a full scan of the database.
Running a scan is simple and is one command:
C:\>cd customers\xxx_xxxxx\pfclscan\PFCLScan_Bin\bin
C:\customers\xxx_xxxxx\pfclscan\PFCLScan_Bin\bin>pfclset
pfclset.bat Release 1.0 Copyright 2015 PeteFinnigan.com Limited
c:\customers\xxx_xxxxx\pfclscan\PFCLScan_Data>oemframe system oracle1 192.168.1.36 1521 orcl.localdomain
[2026 Sep 09 10:16:11] OEMFrame : Opening the application settings file
OEMFrame: Release 6.0.26.1506 - Production on Wed, 09 Sep 2026 10:16:11 GMT
Copyright (c) 2026 PeteFinnigan.com Limited. All rights reserved.
[2026 Sep 09 10:16:11] OEMFrame : Starting OEMFrame...
[2026 Sep 09 10:16:11] OEMFrame : Create Credentials
[2026 Sep 09 10:16:11] OEMFrame : Run oemrun
Press any key to exit.
oemrun.bat Release 1.0 Copyright 2015 PeteFinnigan.com Limited, Production on 09/09/2026 10:16:11.78
[09/09/2026 10:16:11.79] oemrun: Start running OEM project processor
[09/09/2026 10:16:11.79] oemrun: Copy safe oemscan project
[09/09/2026 10:16:11.79] oemrun: Run the OEMBUILD project
...
The username and password are passed in clear text here as they would be using SQL*Plus BUT they can be encrypted first and during all steps of the scan the username and password are encrypted even if passed in clear text. This is just a demo here to show the functionality.
The scan runs the full suite of thousands of security checks that the normal GUI version of PFCLScan runs.
A sample of a policy being executed and scanned is here:
...
[2026 Sep 09 10:16:13] Lock : Creating policy file=[c:\customers\xxx_xxxxx\pfclscan\PFCLScan_Data\PeteFinnigan.com Limited\PFCLScan\plugins\policy\policy.4.1.29.1.0.conf.xml]
[2026 Sep 09 10:16:13] Lock : Closing Down LOCK
LOAD: Release 6.0.26.1506 - Production on Wed, 09 Sep 2026 10:16:13 GMT
Copyright (c) 2026 PeteFinnigan.com Limited. All rights reserved.
[2026 Sep 09 10:16:13] Load : Starting LOAD...
[2026 Sep 09 10:16:13] Load : Opening the application settings file
[2026 Sep 09 10:16:13] Load : Opening the project: c:\customers\xxx_xxxxx\pfclscan\PFCLScan_Data\PeteFinnigan.com Limited\PFCLScan\plugins\oemscan.pfclx
[2026 Sep 09 10:16:13] Load : Run Number=[cur]
[2026 Sep 09 10:16:13] Load : run cmd line [oscan -c c:\customers\xxx_xxxxx\pfclscan\PFCLScan_Data\PeteFinnigan.com Limited\PFCLScan\plugins\policy\4.1.1.1.0.conf -v]
OSCAN: Release 6.0.12.1526 - Production on Wed Sep 9 10:16:13 2026
Copyright (c) 2026 PeteFinnigan.com Limited. All rights reserved.
[2026 Sep 09 09:16:13] Oscan: Starting OSCAN...
[2026 Sep 09 09:16:13] Oscan: Running Scanner
[2026 Sep 09 09:16:13] Oscan: Load Test from XML...
[2026 Sep 09 09:16:13] Oscan: Load policy from XML...
[2026 Sep 09 09:16:13] Oscan: Load dictionary file...
[2026 Sep 09 09:16:13] Oscan: Load default list file...
[2026 Sep 09 09:16:13] Oscan: Connect to the database....
[2026 Sep 09 09:16:13] Oscan: Server Attached to [//192.168.1.36:1521/orcl.localdomain]
[2026 Sep 09 09:16:13] Oscan: Connected to [//192.168.1.36:1521/orcl.localdomain] as [:E:FE21B3993FCA2E83]
[2026 Sep 09 09:16:13] Oscan: Opening Output File
[2026 Sep 09 09:16:13] Oscan: [-] Stabalisation Check
[2026 Sep 09 09:16:13] Oscan: [-] Audit Users Privileges
[2026 Sep 09 09:16:13] Oscan: Disconnecting from [//192.168.1.36:1521/orcl.localdomain] as [:E:FE21B3993FCA2E83]
[2026 Sep 09 09:16:13] Oscan: Closing Output File [oscan.op.4.1.1.xml]
[2026 Sep 09 09:16:13] Oscan: Closing Down OSCAN
[2026 Sep 09 10:16:13] Load : Exit code=[0]
[2026 Sep 09 10:16:13] Load : Completed [oscan -c "c:\customers\xxx_xxxxx\pfclscan\PFCLScan_Data\PeteFinnigan.com Limited\PFCLScan\plugins\policy\4.1.1.1.0.conf" -v]
[2026 Sep 09 10:16:13] Load : Update the project runset
[2026 Sep 09 10:16:13] Load : Testing run file [c:\customers\xxx_xxxxx\pfclscan\PFCLScan_Data\PeteFinnigan.com Limited\PFCLScan\reports\run.4.1.1.data.xml] exists
[2026 Sep 09 10:16:13] Load : Testing raw file [c:\customers\xxx_xxxxx\pfclscan\PFCLScan_Data\oscan.op.4.1.1.xml] exists
[2026 Sep 09 10:16:13] Load : Running Loop compactor
[2026 Sep 09 10:16:13] Load : Run Loopcompact [loop "c:\customers\xxx_xxxxx\pfclscan\PFCLScan_Data\oscan.op.4.1.1.xml"]
...
Here is part of the HTML report generated:
We were asked by a customer to scan a 9.2.0.8 database using PFCLScan so we used our cut down scanner above and tested it locally here on an old 9.2.0.1 database that we had an old virtual Box VM of. This VM had not been started for just over 10 years but worked.
Because we do not scan 9iR2 or indeed 10gR2 or 11gR2 anymore in testing and we update the scanner checks on a regular basis we found a few small issues where we use more modern techniques to do things now that do not work in 9iR2. We fixed these in a customer specific download and now have a working version of the scanner that will scan 9iR2. 11gR2 should work as we tested 11gR2 much more recently and 10gR2 can be made to work easily or may work now if we test it
Why scan old databases?
Some customers will be forced to run old out of date Oracle databases in some cases. The usual reason is they still have customers on old systems that will age out and then the system will be decommissioned when all the customers have had the service completed. Some run old applications where the vendor is not available and they do not want to run on newer databases. There are systems around still that use older databases.
Whilst there are not any security patches available for 9iR2 (of course) there is still a lot of the database configuration and controls that can be changed and tightened to improve the security even of old databases.
#oracleace #oracleacepro #sym_42 #oracle #database #security #9ir2 #scanning #pfclscan
Can Synonyms Point to Synonyms?
I got asked a question via a DM on one of my social media channels a few days ago and thought it worth an investigation. They asked me; Can an Oracle synonym point to another synonym, ad infinitum.
Let me do a test and see. First what privileges exist that involve synonyms:
There are two groups of synonym privileges; drop and create PUBLIC synonyms and drop and create ANY and one single privilege CREATE SYNONYM to create a single synonym in a schema. For the single create of synonym in your own schema you do not need a DROP because of the OBJECT OWNER PRINCIPAL as the owner of an object once its created does not need privileges to change their own objects.
Let me create a user and then add permissions and connect to the user:
Now as this user create a synonym:
Now try and create another synonym pointing to this synonym and just for fun create another synonym to that synonym so that we have synonym to synonym to sys.all_users
That works. What does the meta data show:
So, yes synonyms can point at synonyms ad-infinitum. We can also use these synonyms and they work:
Hmm, this is a confusing situation to be in if you have a chain of synonyms pointing eventually to an object. From a security perspective this would be hard to understand and to ensure everything was correct.
#oracleace #oracleacepro #sym_42 #oracle #database #security #synonyms
Let me do a test and see. First what privileges exist that involve synonyms:
SQL> select name from system_privilege_map where name like '%SYNONYM%';
NAME
----------------------------------------
DROP PUBLIC SYNONYM
CREATE PUBLIC SYNONYM
DROP ANY SYNONYM
CREATE ANY SYNONYM
CREATE SYNONYM
SQL>
There are two groups of synonym privileges; drop and create PUBLIC synonyms and drop and create ANY and one single privilege CREATE SYNONYM to create a single synonym in a schema. For the single create of synonym in your own schema you do not need a DROP because of the OBJECT OWNER PRINCIPAL as the owner of an object once its created does not need privileges to change their own objects.
Let me create a user and then add permissions and connect to the user:
SQL> create user syntest identified by syntest;
User created.
SQL> grant create session, create synonym to syntest;
Grant succeeded.
SQL> connect syntest/syntest@//192.168.56.33:1539/xepdb1
Connected.
SQL>
Now as this user create a synonym:
SQL> create synonym all_users for sys.all_users;
Synonym created.
SQL>
Now try and create another synonym pointing to this synonym and just for fun create another synonym to that synonym so that we have synonym to synonym to sys.all_users
SQL> create synonym all_users1 for all_users;
Synonym created.
SQL> create synonym all_users2 for all_users1;
Synonym created.
SQL>
That works. What does the meta data show:
SQL> set serveroutput on
SQL> @sc_print 'select * from dba_synonyms where synonym_name like ''''ALL_USERS%'''''
old 32: lv_str:=translate('&&1','''','''''');
new 32: lv_str:=translate('select * from dba_synonyms where synonym_name like ''ALL_USERS%''','''','''''');
Executing Query [select * from dba_synonyms where synonym_name like
'ALL_USERS%']
OWNER : PUBLIC
SYNONYM_NAME : ALL_USERS
TABLE_OWNER : SYS
TABLE_NAME : ALL_USERS
DB_LINK :
ORIGIN_CON_ID : 1
-------------------------------------------
OWNER : SYNTEST
SYNONYM_NAME : ALL_USERS
TABLE_OWNER : SYS
TABLE_NAME : ALL_USERS
DB_LINK :
ORIGIN_CON_ID : 3
-------------------------------------------
OWNER : SYNTEST
SYNONYM_NAME : ALL_USERS1
TABLE_OWNER : SYNTEST
TABLE_NAME : ALL_USERS
DB_LINK :
ORIGIN_CON_ID : 3
-------------------------------------------
OWNER : SYNTEST
SYNONYM_NAME : ALL_USERS2
TABLE_OWNER : SYNTEST
TABLE_NAME : ALL_USERS1
DB_LINK :
ORIGIN_CON_ID : 3
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
So, yes synonyms can point at synonyms ad-infinitum. We can also use these synonyms and they work:
SQL> connect syntest/syntest@//192.168.56.33:1539/xepdb1
Connected.
SQL> select count(*) from all_users2;
COUNT(*)
----------
87
SQL> select count(*) from all_users1;
COUNT(*)
----------
87
SQL> select count(*) from all_users;
COUNT(*)
----------
87
SQL> select count(*) from sys.all_users;
COUNT(*)
----------
87
SQL>
Hmm, this is a confusing situation to be in if you have a chain of synonyms pointing eventually to an object. From a security perspective this would be hard to understand and to ensure everything was correct.
#oracleace #oracleacepro #sym_42 #oracle #database #security #synonyms
