In that blog I used qwen2.5-coder, qwen3-coder and qwen3-coder-next - in summary; qwen3-coder-next ran for 51 minutes and went into a loop and hung; the other two generated code but both hallucinated Oracle package names and procedures. Qwen2.5 was a third as fast as qwen3-coder but neither created code that would compile.
Whilst I could have fixed the code and made it work that was not the focus of that particular test. The local LLMs can be used as an assistant to help progress rapid development but only if you know what you are doing. Yes, we could better use an agent and a loop and get it to compile the code and fix the bugs found and recompile until it works.
Qwen3.8:27b was released very recently so I updated ollama and downloaded the model (ollama pull qwen3.8:27b) and then ran the model through AnythingLLM via its chat prompt where ollama is running on my macbook pro M5 max 64gb laptop. I decided with this much heralded new model to test the same PL/SQL encrypt / decrypt package generation.
I ran this prompt:
please write a pl/sql package to include an encrypt and decrypt function and also private base64 code and decode functions that should be used to base64 encrypted data and base64 decode encrypted data in the encrypt and decrypt functions
This ran really quickly in 2minutes 4 seconds and at 35.12 tokens per second. It created a package header:
CREATE OR REPLACE PACKAGE sec_crypto_pkg IS
-- Public: Encrypt plain text and return Base64-encoded ciphertext.
-- p_plain_text : The text to encrypt.
-- p_key : The encryption key (will be hashed to 32 bytes for AES-256).
-- p_iv : Optional initialization vector (16 bytes). Auto-generated if NULL.
-- Returns: Base64-encoded string of (IV || ciphertext || HMAC).
FUNCTION encrypt(
p_plain_text IN VARCHAR2,
p_key IN VARCHAR2,
p_iv IN VARCHAR2 DEFAULT NULL
) RETURN VARCHAR2;
-- Public: Decrypt a Base64-encoded ciphertext back to plain text.
-- p_cipher_text : Base64-encoded string produced by ENCRYPT.
-- p_key : The same key used during encryption.
-- Returns: The original plain text.
FUNCTION decrypt(
p_cipher_text IN VARCHAR2,
p_key IN VARCHAR2
) RETURN VARCHAR2;
END sec_crypto_pkg;
/
And a package body:
CREATE OR REPLACE PACKAGE BODY sec_crypto_pkg IS
--------------------------------------------------------------------------
-- CONSTANTS
--------------------------------------------------------------------------
c_aes256 CONSTANT PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES256;
c_hmac_sha256 CONSTANT PLS_INTEGER := DBMS_CRYPTO.HMAC_SHA256;
c_iv_len CONSTANT PLS_INTEGER := 16; -- AES block size in bytes
c_hmac_len CONSTANT PLS_INTEGER := 32; -- SHA-256 output in bytes
--------------------------------------------------------------------------
-- PRIVATE: Base64 Encode
-- Converts a RAW value into its Base64 VARCHAR2 representation.
--------------------------------------------------------------------------
FUNCTION base64_encode(p_raw IN RAW) RETURN VARCHAR2 IS
l_result VARCHAR2(32767);
l_chunk VARCHAR2(32767);
l_offset PLS_INTEGER := 1;
l_chunk_len CONSTANT PLS_INTEGER := 4000; -- process in chunks to avoid limits
BEGIN
IF p_raw IS NULL OR UTL_RAW.LENGTH(p_raw) = 0 THEN
RETURN NULL;
END IF;
LOOP
l_chunk := UTL_RAW.SUBSTR(p_raw, l_offset, l_chunk_len);
l_result := l_result || UTL_RAW.RAWTOSTR(
UTL_I18N.STRINGTOHEX(UTL_RAW.CAST_TO_RAW(l_chunk))
);
-- Simpler and correct approach:
EXIT WHEN l_offset + l_chunk_len > UTL_RAW.LENGTH(p_raw);
l_offset := l_offset + l_chunk_len;
END LOOP;
-- The simplest correct approach for Base64 in Oracle:
RETURN (SELECT UTL_RAW.CAST_TO_VARCHAR2(
UTL_I18N.STRINGTOHEX(
-- We'll use a proper method below
)
) FROM DUAL);
END base64_encode;
--------------------------------------------------------------------------
-- PRIVATE: Base64 Decode
-- Converts a Base64 VARCHAR2 string back into a RAW value.
--------------------------------------------------------------------------
FUNCTION base64_decode(p_b64 IN VARCHAR2) RETURN RAW IS
BEGIN
IF p_b64 IS NULL THEN
RETURN NULL;
END IF;
RETURN NULL; -- placeholder, see corrected implementation below
END base64_decode;
--------------------------------------------------------------------------
-- PRIVATE: Derive a 32-byte key from an arbitrary passphrase.
--------------------------------------------------------------------------
FUNCTION derive_key(p_key IN VARCHAR2) RETURN RAW IS
BEGIN
-- Hash the key material to a fixed 32-byte (AES-256) key.
RETURN DBMS_CRYPTO.HASH(
UTL_I18N.STRINGTORAW(p_key, 'UTF8'),
c_hmac_sha256
);
END derive_key;
--------------------------------------------------------------------------
-- PUBLIC: ENCRYPT
--------------------------------------------------------------------------
FUNCTION encrypt(
p_plain_text IN VARCHAR2,
p_key IN VARCHAR2,
p_iv IN VARCHAR2 DEFAULT NULL
) RETURN VARCHAR2 IS
l_key RAW(32);
l_iv RAW(16);
l_plaintext RAW;
l_ciphertext RAW;
l_hmac RAW(32);
l_combined RAW(32767);
l_b64 VARCHAR2(32767);
BEGIN
IF p_plain_text IS NULL OR p_key IS NULL THEN
RETURN NULL;
END IF;
-- Derive fixed-length key
l_key := derive_key(p_key);
-- Use provided IV or generate a random one
IF p_iv IS NOT NULL THEN
l_iv := UTL_I18N.STRINGTORAW(p_iv, 'UTF8');
ELSE
l_iv := DBMS_CRYPTO.RANDOMBYTES(c_iv_len);
END IF;
-- Convert plain text to RAW
l_plaintext := UTL_I18N.STRINGTORAW(p_plain_text, 'UTF8');
-- Encrypt with AES-256
l_ciphertext := DBMS_CRYPTO.ENCRYPT(
src => l_plaintext,
typ => c_aes256,
key => l_key,
iv => l_iv
);
-- Compute HMAC over (IV || ciphertext) for integrity
l_combined := l_iv || l_ciphertext;
l_hmac := DBMS_CRYPTO MAC(
src => l_combined,
typ => c_hmac_sha256,
key => l_key
);
-- Final payload: IV (16) || ciphertext (variable) || HMAC (32)
l_combined := l_iv || l_ciphertext || l_hmac;
-- Base64 encode the binary payload
l_b64 := base64_encode(l_combined);
RETURN l_b64;
END encrypt;
--------------------------------------------------------------------------
-- PUBLIC: DECRYPT
--------------------------------------------------------------------------
FUNCTION decrypt(
p_cipher_text IN VARCHAR2,
p_key IN VARCHAR2
) RETURN VARCHAR2 IS
l_key RAW(32);
l_combined RAW;
l_iv RAW(16);
l_ciphertext RAW;
l_hmac_stored RAW(32);
l_hmac_calc RAW(32);
l_plaintext RAW;
BEGIN
IF p_cipher_text IS NULL OR p_key IS NULL THEN
RETURN NULL;
END IF;
l_key := derive_key(p_key);
-- Base64 decode the input
l_combined := base64_decode(p_cipher_text);
IF UTL_RAW.LENGTH(l_combined) < c_iv_len + c_hmac_len THEN
RAISE_APPLICATION_ERROR(-20001, 'Invalid or truncated ciphertext.');
END IF;
-- Parse: IV (16) || ciphertext (rest - 32) || HMAC (32)
l_iv := UTL_RAW.SUBSTR(l_combined, 1, c_iv_len);
l_hmac_stored := UTL_RAW.SUBSTR(l_combined, UTL_RAW.LENGTH(l_combined) - c_hmac_len + 1, c_hmac_len);
l_ciphertext := UTL_RAW.SUBSTR(l_combined, c_iv_len + 1,
UTL_RAW.LENGTH(l_combined) - c_iv_len - c_hmac_len);
-- Verify HMAC
l_hmac_calc := DBMS_CRYPTO.MAC(
src => l_iv || l_ciphertext,
typ => c_hmac_sha256,
key => l_key
);
IF l_hmac_calc <> l_hmac_stored THEN
RAISE_APPLICATION_ERROR(-20002, 'HMAC verification failed " data may be tampered.');
END IF;
-- Decrypt
l_plaintext := DBMS_CRYPTO.DECRYPT(
src => l_ciphertext,
typ => c_aes256,
key => l_key,
iv => l_iv
);
-- Convert RAW back to UTF-8 VARCHAR2
RETURN UTL_I18N.RAWTOHEX(l_plaintext); -- placeholder; see note below
END decrypt;
END sec_crypto_pkg;
/
And it also generated a completely standalone Base64 encode and decode to replace the placeholder above and the encoder using native code and no build in packages from Oracle. The only dependencies are DBMS_CRYPTO and UTL_I18N according to the final summary text. I have used the non-dependant PL/SQL base64 code and pasted that into the package body in the correct place.
To run this code we need database permissions on the packages and the ability to create PL/SQL so lets just cheat for this demo and install the code as SYS. The complete package with the custom base64 code is:
-- PL/SQL encrypt/decrypt package header
CREATE OR REPLACE PACKAGE sec_crypto_pkg IS
-- Public: Encrypt plain text and return Base64-encoded ciphertext.
-- p_plain_text : The text to encrypt.
-- p_key : The encryption key (will be hashed to 32 bytes for AES-256).
-- p_iv : Optional initialization vector (16 bytes). Auto-generated if NULL.
-- Returns: Base64-encoded string of (IV || ciphertext || HMAC).
FUNCTION encrypt(
p_plain_text IN VARCHAR2,
p_key IN VARCHAR2,
p_iv IN VARCHAR2 DEFAULT NULL
) RETURN VARCHAR2;
-- Public: Decrypt a Base64-encoded ciphertext back to plain text.
-- p_cipher_text : Base64-encoded string produced by ENCRYPT.
-- p_key : The same key used during encryption.
-- Returns: The original plain text.
FUNCTION decrypt(
p_cipher_text IN VARCHAR2,
p_key IN VARCHAR2
) RETURN VARCHAR2;
END sec_crypto_pkg;
/
sho err
-- PL/SQL package body
CREATE OR REPLACE PACKAGE BODY sec_crypto_pkg IS
--------------------------------------------------------------------------
-- CONSTANTS
--------------------------------------------------------------------------
c_aes256 CONSTANT PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES256;
c_hmac_sha256 CONSTANT PLS_INTEGER := DBMS_CRYPTO.HMAC_SHA256;
c_iv_len CONSTANT PLS_INTEGER := 16; -- AES block size in bytes
c_hmac_len CONSTANT PLS_INTEGER := 32; -- SHA-256 output in bytes
-- ------------------------------------------------------------------------
-- PRIVATE: Base64 Encode (pure PL/SQL, no UTL_ENCODE dependency)
--------------------------------------------------------------------------
FUNCTION base64_encode(p_raw IN RAW) RETURN VARCHAR2 IS
BEGIN
RETURN UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_VARCHAR2(p_raw));
END;
--------------------------------------------------------------------------
-- PRIVATE: Base64 Decode (pure PL/SQL, no UTL_ENCODE dependency)
--------------------------------------------------------------------------
FUNCTION base64_decode(p_b64 IN VARCHAR2) RETURN RAW IS
BEGIN
RETURN UTL_RAW.CAST_TO_RAW(UTL_ENCODE.BASE64_DECODE(p_b64));
END;
--------------------------------------------------------------------------
-- PRIVATE: Derive a 32-byte key from an arbitrary passphrase.
--------------------------------------------------------------------------
FUNCTION derive_key(p_key IN VARCHAR2) RETURN RAW IS
BEGIN
-- Hash the key material to a fixed 32-byte (AES-256) key.
RETURN DBMS_CRYPTO.HASH(
UTL_I18N.STRINGTORAW(p_key, 'UTF8'),
c_hmac_sha256
);
END derive_key;
--------------------------------------------------------------------------
-- PUBLIC: ENCRYPT
--------------------------------------------------------------------------
FUNCTION encrypt(
p_plain_text IN VARCHAR2,
p_key IN VARCHAR2,
p_iv IN VARCHAR2 DEFAULT NULL
) RETURN VARCHAR2 IS
l_key RAW(32);
l_iv RAW(16);
l_plaintext RAW;
l_ciphertext RAW;
l_hmac RAW(32);
l_combined RAW(32767);
l_b64 VARCHAR2(32767);
BEGIN
IF p_plain_text IS NULL OR p_key IS NULL THEN
RETURN NULL;
END IF;
-- Derive fixed-length key
l_key := derive_key(p_key);
-- Use provided IV or generate a random one
IF p_iv IS NOT NULL THEN
l_iv := UTL_I18N.STRINGTORAW(p_iv, 'UTF8');
ELSE
l_iv := DBMS_CRYPTO.RANDOMBYTES(c_iv_len);
END IF;
-- Convert plain text to RAW
l_plaintext := UTL_I18N.STRINGTORAW(p_plain_text, 'UTF8');
-- Encrypt with AES-256
l_ciphertext := DBMS_CRYPTO.ENCRYPT(
src => l_plaintext,
typ => c_aes256,
key => l_key,
iv => l_iv
);
-- Compute HMAC over (IV || ciphertext) for integrity
l_combined := l_iv || l_ciphertext;
l_hmac := DBMS_CRYPTO MAC(
src => l_combined,
typ => c_hmac_sha256,
key => l_key
);
-- Final payload: IV (16) || ciphertext (variable) || HMAC (32)
l_combined := l_iv || l_ciphertext || l_hmac;
-- Base64 encode the binary payload
l_b64 := base64_encode(l_combined);
RETURN l_b64;
END encrypt;
--------------------------------------------------------------------------
-- PUBLIC: DECRYPT
--------------------------------------------------------------------------
FUNCTION decrypt(
p_cipher_text IN VARCHAR2,
p_key IN VARCHAR2
) RETURN VARCHAR2 IS
l_key RAW(32);
l_combined RAW;
l_iv RAW(16);
l_ciphertext RAW;
l_hmac_stored RAW(32);
l_hmac_calc RAW(32);
l_plaintext RAW;
BEGIN
IF p_cipher_text IS NULL OR p_key IS NULL THEN
RETURN NULL;
END IF;
l_key := derive_key(p_key);
-- Base64 decode the input
l_combined := base64_decode(p_cipher_text);
IF UTL_RAW.LENGTH(l_combined) < c_iv_len + c_hmac_len THEN
RAISE_APPLICATION_ERROR(-20001, 'Invalid or truncated ciphertext.');
END IF;
-- Parse: IV (16) || ciphertext (rest - 32) || HMAC (32)
l_iv := UTL_RAW.SUBSTR(l_combined, 1, c_iv_len);
l_hmac_stored := UTL_RAW.SUBSTR(l_combined, UTL_RAW.LENGTH(l_combined) - c_hmac_len + 1, c_hmac_len);
l_ciphertext := UTL_RAW.SUBSTR(l_combined, c_iv_len + 1,
UTL_RAW.LENGTH(l_combined) - c_iv_len - c_hmac_len);
-- Verify HMAC
l_hmac_calc := DBMS_CRYPTO.MAC(
src => l_iv || l_ciphertext,
typ => c_hmac_sha256,
key => l_key
);
IF l_hmac_calc <> l_hmac_stored THEN
RAISE_APPLICATION_ERROR(-20002, 'HMAC verification failed " data may be tampered.');
END IF;
-- Decrypt
l_plaintext := DBMS_CRYPTO.DECRYPT(
src => l_ciphertext,
typ => c_aes256,
key => l_key,
iv => l_iv
);
-- Convert RAW back to UTF-8 VARCHAR2
RETURN UTL_I18N.RAWTOHEX(l_plaintext); -- placeholder; see note below
END decrypt;
END sec_crypto_pkg;
/
Now, we can try and compile this in my 26ai Free database:
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 Wed Aug 19 12:49:35 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> @enc
Package created.
No errors.
Warning: Package Body created with compilation errors.
SQL> sho err
Errors for PACKAGE BODY SEC_CRYPTO_PKG:
LINE/COL ERROR
-------- -----------------------------------------------------------------
82/35 PLS-00103: Encountered the symbol "MAC" when expecting one of the
following:
. ( * @ % & = - + ; < / > at in is mod remainder not rem
<> or != or ~= >= <= <> and or like like2
like4 likec between || multiset member submultiset <=> <->
<#>
The symbol "." was substituted for "MAC" to continue.
SQL> 82
82* l_hmac := DBMS_CRYPTO MAC(
SQL>
So, unlike qwen2.5-coder and qwen3-coder there is no major hallucinated code; yet!! Let us look at the issue and see if it can be fixed:
...
l_hmac := DBMS_CRYPTO MAC(
src => l_combined,
typ => c_hmac_sha256,
key => l_key
);
...
The DOT is missing between the DBMS_CRYPTO package and the function MAC, in the decrypt function the DOT is not missing. So fixing that we can try again:
SQL> @enc
Package created.
No errors.
Warning: Package Body created with compilation errors.
SQL> sho err
Errors for PACKAGE BODY SEC_CRYPTO_PKG:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
7/57 PLS-00302: component 'HMAC_SHA256' must be declared
SQL>
So we can check the Oracle documents or look at the source code of DBMS_CRYPTO header for the constants and it should be HMAC_SH256 not HMAC_SHA256 - one letter error / hallucinated?
Fixed we can try again:
SQL> @enc
Package created.
No errors.
Warning: Package Body created with compilation errors.
SQL> sho err
Errors for PACKAGE BODY SEC_CRYPTO_PKG:
LINE/COL ERROR
-------- -----------------------------------------------------------------
33/9 PL/SQL: Statement ignored
34/29 PLS-00302: component 'STRINGTORAW' must be declared
49/21 PLS-00215: String length constraints must be in range (1 ..
32767)
50/22 PLS-00215: String length constraints must be in range (1 ..
32767)
64/13 PL/SQL: Statement ignored
64/30 PLS-00302: component 'STRINGTORAW' must be declared
70/9 PL/SQL: Statement ignored
LINE/COL ERROR
-------- -----------------------------------------------------------------
70/33 PLS-00302: component 'STRINGTORAW' must be declared
106/21 PLS-00215: String length constraints must be in range (1 ..
32767)
108/22 PLS-00215: String length constraints must be in range (1 ..
32767)
111/22 PLS-00215: String length constraints must be in range (1 ..
32767)
152/9 PL/SQL: Statement ignored
LINE/COL ERROR
-------- -----------------------------------------------------------------
152/25 PLS-00302: component 'RAWTOHEX' must be declared
SQL>
OK, it is time to stop the experiment and take stock. The STRINGTORAW functions should be STRING_TO_RAW but the RAWTOHEX does not exist at all in the UTL_I18N package. The five places where there is an index constraint issue is because RAW is used with no size.
At least qwen3.8:27b did not major hallucinate Oracle package names like its predecessor but it did get function names wrong but they were close or PL/SQL syntax wrong for RAW size constraints and more.
To be fair I did not change the system prompt to tell the model that it is a PL/SQL coder and to stick to syntax and so on. I think if I added a much better system prompt it could work.
Is it better than the 2.5 and 3 models; Yes, I think so and seemed faster. I could also change the temperature and the reasoning to get better code results BUT I think a much better system prompt would be better.
I have been also using gtp-oss:20b with a comprehensive set of changes, reasoning, temperature, system context and more and got very good results with coding in Lua where code compiled and ran every time so I think qwen3.8:27b with better tweaking looks promising for PL/SQL. More soon!!
#oracleace #oracleacepro #sym_42 #ai #llm #qwen38 #macbook #m5 #max
