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
