I tested three models, qwen2.5-coder, qwen3-coder:30b and qwen3-coder-next.
I used the same prompt for each of the models. The prompt is:
please write a PL/SQL public package procedures and any supporting private procedures and functions where that public procedure can encrypt and decrypt data passed and base64 encode/decode the result
I ran qwen2.5-coder and it generated this text at the start:
Certainly! Below is a complete PL/SQL package named encrypt_decrypt_pkg that includes public procedures for encrypting and decrypting data, as well as base64 encoding and decoding. The package uses AES256 encryption with CBC mode and PKCS#5 padding.
and it went on to generate a PL/SQL package header, package body and some example use cases of the resultant code. The header generated is:
CREATE OR REPLACE PACKAGE encrypt_decrypt_pkg AS
-- Public procedure to encrypt data and base64 encode it
PROCEDURE encrypt_and_encode(
p_data IN VARCHAR2,
p_encrypted_data OUT VARCHAR2,
p_key IN VARCHAR2 DEFAULT NULL
);
-- Public procedure to decrypt base64 decoded data
PROCEDURE decode_and_decrypt(
p_encrypted_data IN VARCHAR2,
p_decrypted_data OUT VARCHAR2,
p_key IN VARCHAR2 DEFAULT NULL
);
-- Public procedure to base64 encode data
PROCEDURE base64_encode(
p_data IN VARCHAR2,
p_encoded_data OUT VARCHAR2
);
-- Public procedure to base64 decode data
PROCEDURE base64_decode(
p_encoded_data IN VARCHAR2,
p_decoded_data OUT VARCHAR2
);
END encrypt_decrypt_pkg;
/
The package body generated is:
CREATE OR REPLACE PACKAGE BODY encrypt_decrypt_pkg AS
-- Private function to generate a key for encryption
FUNCTION generate_key(p_key IN VARCHAR2 DEFAULT NULL) RETURN RAW IS
v_key RAW(32);
BEGIN
IF p_key IS NOT NULL THEN
-- Use provided key and hash it to 256 bits
v_key := DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW(p_key, 'AL32UTF8'), DBMS_CRYPTO.HASH_SH256);
ELSE
-- Generate a random key
v_key := DBMS_CRYPTO.RANDOMBYTES(32);
END IF;
RETURN v_key;
END generate_key;
-- Private function to base64 encode raw data
FUNCTION base64_encode_raw(p_data IN RAW) RETURN VARCHAR2 IS
v_encoded RAW(32767);
BEGIN
v_encoded := UTL_ENCODE.BASE64_ENCODE(p_data, TRUE);
RETURN UTL_RAW.CAST_TO_VARCHAR2(v_encoded);
END base64_encode_raw;
-- Private function to base64 decode string to raw data
FUNCTION base64_decode_raw(p_data IN VARCHAR2) RETURN RAW IS
v_decoded RAW(32767);
BEGIN
v_decoded := UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(p_data));
RETURN v_decoded;
END base64_decode_raw;
-- Public procedure to encrypt data and base64 encode it
PROCEDURE encrypt_and_encode(
p_data IN VARCHAR2,
p_encrypted_data OUT VARCHAR2,
p_key IN VARCHAR2 DEFAULT NULL
) IS
v_key RAW(32);
v_iv RAW(16); -- Initialization Vector for AES-256-CBC
v_encrypted_raw RAW(32767);
BEGIN
-- Generate encryption key
v_key := generate_key(p_key);
-- Generate a random initialization vector (IV)
v_iv := DBMS_CRYPTO.RANDOMBYTES(16);
-- Encrypt data using AES256 in CBC mode with PKCS#5 padding
v_encrypted_raw := DBMS_CRYPTO.ENCRYPT(
src => UTL_I18N.STRING_TO_RAW(p_data, 'AL32UTF8'),
typ => DBMS_CRYPTO.AES256 + DBMS_CRYPTO.CBC_PKCS5,
key => v_key,
iv => v_iv
);
-- Prepend the IV to the encrypted data and base64 encode it
p_encrypted_data := base64_encode_raw(v_iv || v_encrypted_raw);
END encrypt_and_encode;
-- Public procedure to decrypt base64 decoded data
PROCEDURE decode_and_decrypt(
p_encrypted_data IN VARCHAR2,
p_decrypted_data OUT VARCHAR2,
p_key IN VARCHAR2 DEFAULT NULL
) IS
v_key RAW(32);
v_iv RAW(16); -- Initialization Vector for AES-256-CBC
v_combined_raw RAW(32767);
v_decrypted_raw RAW(32767);
BEGIN
-- Generate decryption key
v_key := generate_key(p_key);
-- Base64 decode the input data
v_combined_raw := base64_decode_raw(p_encrypted_data);
-- Extract the IV from the beginning of the combined data
v_iv := UTL_RAW.SUBSTR(v_combined_raw, 1, 16);
-- Extract the encrypted data after the IV
v_decrypted_raw := UTL_RAW.SUBSTR(v_combined_raw, 17);
-- Decrypt the data using AES256 in CBC mode with PKCS#5 padding
p_decrypted_data := UTL_I18N.RAW_TO_STRING(
DBMS_CRYPTO.DECRYPT(
src => v_decrypted_raw,
typ => DBMS_CRYPTO.AES256 + DBMS_CRYPTO.CBC_PKCS5,
key => v_key,
iv => v_iv
),
'AL32UTF8'
);
END decode_and_decrypt;
-- Public procedure to base64 encode data
PROCEDURE base64_encode(
p_data IN VARCHAR2,
p_encoded_data OUT VARCHAR2
) IS
v_raw_data RAW(32767);
BEGIN
-- Convert string to raw data
v_raw_data := UTL_I18N.STRING_TO_RAW(p_data, 'AL32UTF8');
-- Base64 encode the raw data
p_encoded_data := base64_encode_raw(v_raw_data);
END base64_encode;
-- Public procedure to base64 decode data
PROCEDURE base64_decode(
p_encoded_data IN VARCHAR2,
p_decoded_data OUT VARCHAR2
) IS
v_decoded_raw RAW(32767);
BEGIN
-- Base64 decode the input data
v_decoded_raw := base64_decode_raw(p_encoded_data);
-- Convert raw data back to string
p_decoded_data := UTL_I18N.RAW_TO_STRING(v_decoded_raw, 'AL32UTF8');
END base64_decode;
END encrypt_decrypt_pkg;
/
The example usage code is:
-- Example 1: Encrypt and encode data
DECLARE
v_encrypted VARCHAR2(4000);
BEGIN
encrypt_decrypt_pkg.encrypt_and_encode(
p_data => 'Hello World',
p_encrypted_data => v_encrypted,
p_key => 'my_secret_key'
);
DBMS_OUTPUT.PUT_LINE('Encrypted and encoded: ' || v_encrypted);
END;
/
-- Example 2: Decode and decrypt data
DECLARE
v_decrypted VARCHAR2(4000);
BEGIN
encrypt_decrypt_pkg.decode_and_decrypt(
p_encrypted_data => 'your_encrypted_string_here',
p_decrypted_data => v_decrypted,
p_key => 'my_secret_key'
);
DBMS_OUTPUT.PUT_LINE('Decrypted data: ' || v_decrypted);
END;
/
-- Example 3: Simple base64 encoding
DECLARE
v_encoded VARCHAR2(4000);
BEGIN
encrypt_decrypt_pkg.base64_encode(
p_data => 'Hello World',
p_encoded_data => v_encoded
);
DBMS_OUTPUT.PUT_LINE('Base64 encoded: ' || v_encoded);
END;
/
-- Example 4: Simple base64 decoding
DECLARE
v_decoded VARCHAR2(4000);
BEGIN
encrypt_decrypt_pkg.base64_decode(
p_encoded_data => 'SGVsbG8gV29ybGQ=',
p_decoded_data => v_decoded
);
DBMS_OUTPUT.PUT_LINE('Base64 decoded: ' || v_decoded);
END;
/
No privileges are generated for DBMS_CRYPTO or UTL_ENCODE so we will do these manually at the end but first what did qwen3-coder:30b generate?
Here's a complete PL/SQL package for encryption/decryption with Base64 encoding/decoding. It generated the package header and body together unlike qwen2.5-coder:
CREATE OR REPLACE PACKAGE encrypt_decrypt_pkg AS
-- Public procedure to encrypt data and base64 encode it
PROCEDURE encrypt_and_encode(
p_data IN VARCHAR2,
p_encrypted_data OUT VARCHAR2,
p_key IN VARCHAR2 DEFAULT NULL
);
-- Public procedure to decrypt base64 decoded data
PROCEDURE decode_and_decrypt(
p_encrypted_data IN VARCHAR2,
p_decrypted_data OUT VARCHAR2,
p_key IN VARCHAR2 DEFAULT NULL
);
-- Public procedure to base64 encode data
PROCEDURE base64_encode(
p_data IN VARCHAR2,
p_encoded_data OUT VARCHAR2
);
-- Public procedure to base64 decode data
PROCEDURE base64_decode(
p_encoded_data IN VARCHAR2,
p_decoded_data OUT VARCHAR2
);
END encrypt_decrypt_pkg;
/
CREATE OR REPLACE PACKAGE BODY encrypt_decrypt_pkg AS
-- Private function to generate a key for encryption
FUNCTION generate_key(p_key IN VARCHAR2 DEFAULT NULL) RETURN RAW IS
v_key RAW(32);
BEGIN
IF p_key IS NOT NULL THEN
-- Use provided key
v_key := DBMS_CRYPTO.HASH(p_key, DBMS_CRYPTO.HASH_SH256);
ELSE
-- Generate a random key
v_key := DBMS_CRYPTO.RANDOMBYTES(32);
END IF;
RETURN v_key;
END generate_key;
-- Private function to base64 encode raw data
FUNCTION base64_encode_raw(p_data IN RAW) RETURN VARCHAR2 IS
v_encoded VARCHAR2(32767);
BEGIN
v_encoded := UTL_ENCODE.BASE64_ENCODE(p_data);
RETURN v_encoded;
END base64_encode_raw;
-- Private function to base64 decode string to raw data
FUNCTION base64_decode_raw(p_data IN VARCHAR2) RETURN RAW IS
v_decoded RAW(32767);
BEGIN
v_decoded := UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(p_data));
RETURN v_decoded;
END base64_decode_raw;
-- Public procedure to encrypt data and base64 encode it
PROCEDURE encrypt_and_encode(
p_data IN VARCHAR2,
p_encrypted_data OUT VARCHAR2,
p_key IN VARCHAR2 DEFAULT NULL
) IS
v_key RAW(32);
v_encrypted_raw RAW(32767);
BEGIN
-- Generate encryption key
v_key := generate_key(p_key);
-- Encrypt data using AES256 in CBC mode with PKCS#5 padding
v_encrypted_raw := DBMS_CRYPTO.ENCRYPT(
src => UTL_I18N.STRING_TO_RAW(p_data, 'AL32UTF8'),
typ => DBMS_CRYPTO.AES256 + DBMS_CRYPTO.CBC + DBMS_CRYPTO.PKCS5,
key => v_key
);
-- Base64 encode the encrypted data
p_encrypted_data := base64_encode_raw(v_encrypted_raw);
END encrypt_and_encode;
-- Public procedure to decrypt base64 decoded data
PROCEDURE decode_and_decrypt(
p_encrypted_data IN VARCHAR2,
p_decrypted_data OUT VARCHAR2,
p_key IN VARCHAR2 DEFAULT NULL
) IS
v_key RAW(32);
v_decrypted_raw RAW(32767);
BEGIN
-- Generate decryption key
v_key := generate_key(p_key);
-- Base64 decode the input data
v_decrypted_raw := base64_decode_raw(p_encrypted_data);
-- Decrypt the data using AES256 in CBC mode with PKCS#5 padding
p_decrypted_data := UTL_I18N.RAW_TO_STRING(
DBMS_CRYPTO.DECRYPT(
src => v_decrypted_raw,
typ => DBMS_CRYPTO.AES256 + DBMS_CRYPTO.CBC + DBMS_CRYPTO.PKCS5,
key => v_key
),
'AL32UTF8'
);
END decode_and_decrypt;
-- Public procedure to base64 encode data
PROCEDURE base64_encode(
p_data IN VARCHAR2,
p_encoded_data OUT VARCHAR2
) IS
v_raw_data RAW(32767);
BEGIN
-- Convert string to raw data
v_raw_data := UTL_I18N.STRING_TO_RAW(p_data, 'AL32UTF8');
-- Base64 encode the raw data
p_encoded_data := base64_encode_raw(v_raw_data);
END base64_encode;
-- Public procedure to base64 decode data
PROCEDURE base64_decode(
p_encoded_data IN VARCHAR2,
p_decoded_data OUT VARCHAR2
) IS
v_decoded_raw RAW(32767);
BEGIN
-- Base64 decode the input data
v_decoded_raw := base64_decode_raw(p_encoded_data);
-- Convert raw data back to string
p_decoded_data := UTL_I18N.RAW_TO_STRING(v_decoded_raw, 'AL32UTF8');
END base64_decode;
END encrypt_decrypt_pkg;
/
Some sample usage code was generated:
-- Example 1: Encrypt and encode data
DECLARE
v_encrypted VARCHAR2(4000);
BEGIN
encrypt_decrypt_pkg.encrypt_and_encode(
p_data => 'Hello World',
p_encrypted_data => v_encrypted,
p_key => 'my_secret_key'
);
DBMS_OUTPUT.PUT_LINE('Encrypted and encoded: ' || v_encrypted);
END;
/
-- Example 2: Decode and decrypt data
DECLARE
v_decrypted VARCHAR2(4000);
BEGIN
encrypt_decrypt_pkg.decode_and_decrypt(
p_encrypted_data => 'your_encrypted_string_here',
p_decrypted_data => v_decrypted,
p_key => 'my_secret_key'
);
DBMS_OUTPUT.PUT_LINE('Decrypted data: ' || v_decrypted);
END;
/
-- Example 3: Simple base64 encoding
DECLARE
v_encoded VARCHAR2(4000);
BEGIN
encrypt_decrypt_pkg.base64_encode(
p_data => 'Hello World',
p_encoded_data => v_encoded
);
DBMS_OUTPUT.PUT_LINE('Base64 encoded: ' || v_encoded);
END;
/
-- Example 4: Simple base64 decoding
DECLARE
v_decoded VARCHAR2(4000);
BEGIN
encrypt_decrypt_pkg.base64_decode(
p_encoded_data => 'SGVsbG8gV29ybGQ=',
p_decoded_data => v_decoded
);
DBMS_OUTPUT.PUT_LINE('Base64 decoded: ' || v_decoded);
END;
/
As with qwen2.5-coder this qwen3-coder also did not generate the grants needed to run this code.
OK, lets put the 2.5 and 3 code generated into scripts and see if this will compile and run. In both cases I first granted CREATE PROCEDURE, EXECUTE ON DBMS_CRYPTO and EXECUTE ON UTL_ENCODE and created sample users.
I created qwen25.sql and qwen3.sql and added the exact package headers and bodies generated by each LLM model. I have had to add code to create users in each case, either qwen25 or qwen3 and granted privileges to allow the packages to be installed and used. The code for qwen25 for instance to create the user and make grants is:
connect sys/oracle@//192.168.56.34:1521/freepdb1 as sysdba
drop user qwen25 cascade;
create user qwen25 identified by qwen25;
grant create session to qwen25;
grant create procedure to qwen25;
grant execute on dbms_crypto to qwen25;
grant execute on utl_encode to qwen25;
connect qwen25/qwen25@//192.168.56.34:1521/freepdb1
set serveroutput on
I also added show err for each of the package header and body creations for each code generated by the LLM. I also did not include the demos of each package as we are interested initially as to whether the code compiles or not.
Now let us try and run qwen25.sql
SQL> @qwen25
Connected.
User dropped.
User created.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Connected.
Package created.
No errors.
Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY ENCRYPT_DECRYPT_PKG:
LINE/COL ERROR
-------- -----------------------------------------------------------------
21/9 PL/SQL: Statement ignored
21/22 PLS-00306: wrong number or types of arguments in call to
'BASE64_ENCODE'
50/9 PL/SQL: Statement ignored
52/32 PLS-00302: component 'AES256' must be declared
85/9 PL/SQL: Statement ignored
85/38 PLS-00302: component 'RAW_TO_STRING' must be declared
121/9 PL/SQL: Statement ignored
121/36 PLS-00302: component 'RAW_TO_STRING' must be declared
SQL>
The first error at line 21 is because the code shows:
v_encoded := UTL_ENCODE.BASE64_ENCODE(p_data, TRUE);
And UTL_ENCODE.BASE64_ENCODE() only accepts one parameter, the data itself. Even checking Oracle 11 this function did not have an extra parameter. The next error is line 52:
typ => DBMS_CRYPTO.AES256 + DBMS_CRYPTO.CBC_PKCS5,
The global constants are wrong; for instance DBMS_CRYPTO.AES256 should be DBMS_CRYPTO.ENCRYPT_AES256 and DBMS_CRYPTO.CBC_PKCS5 should probably be DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5.
The next two errors are for RAW_TO_STRING() for instance:
p_decoded_data := UTL_I18N.RAW_TO_STRING(v_decoded_raw, 'AL32UTF8');
The function RAW_TO_STRING() does not exist. It should probably be RAW_TO_CHAR() that returns a VARCHAR2.
OK, so a few errors, let us now try the qwen3 generated code by running my qwen3.sql script:
SQL> @qwen3
Connected.
drop user qwen3 cascade
*
ERROR at line 1:
ORA-01918: user 'QWEN3' does not exist
User created.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Connected.
Package created.
No errors.
Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY ENCRYPT_DECRYPT_PKG:
LINE/COL ERROR
-------- -----------------------------------------------------------------
46/9 PL/SQL: Statement ignored
48/32 PLS-00302: component 'AES256' must be declared
72/9 PL/SQL: Statement ignored
72/38 PLS-00302: component 'RAW_TO_STRING' must be declared
107/9 PL/SQL: Statement ignored
107/36 PLS-00302: component 'RAW_TO_STRING' must be declared
SQL>
Almost the same errors but less; the AES256 constant needs to be changed as well as its compatriots around PKCS5 and CBV the same as the 2.5 model. Also the same RAW_TO_STRING() function needs to be changed.
I am not going to fix them as part of this demo as I have learned enough on this case This was an experiment to see if we can use 3 different LLM models to generate PL/SQL code to encrypt and decrypt data in an Oracle database. A reminder of the prompt I used is here
please write a PL/SQL public package procedures and any supporting private procedures and functions where that public procedure can encrypt and decrypt data passed and base64 encode/decode the result
I thought I was clear; but maybe not. I wanted to have an encrypt() and decrypt() function that privately converted the encrypted data to BASE64 and on decrypt accept BASE64 data and decode it first and then decrypt. In other words only two public functions; encrypt and decrypt that are public. There is a combined function BUT I would have left the BASE64 functions private.
So, we had three LLMs and one failed and fell over; this was qwen3-coder-next. This ran for 51 minutes before falling over completely in the open-webui interface and where it did not generate any code. So this is not included further here.
The qwen2.5-coder LLM took 3723 ms to run and complete generating the code; which is 3.73 seconds. The model was processing at around 24 tokens per second. Not shabby.
The qwen3-coder:30b LLM took 1023 ms which is 1.023 seconds and was processing 134 tokens per second.
In summary, qwen3-coder-next is not useful at least on my mac; it did do a lot of reasoning before it failed so is clearly a better LLM for deeper analysis BUT if it failed then this does not matter. Qwen3-coder:30b ran in time in one third of the time of qwen2.5-coder and in terms of tokens per second it was processing 5.58 tokens per second faster than the 2.5 model.
So, qwen3-coder:30b is clearly faster than qwen2.5-coder and its code that it generated is slightly less broken than the 2.5 model BUT both produced code that did not work. I could have fed the output from each back into the LLM and it may have helped but the purpose of this post was to show two things; 1) the speed of three models compared to each other to generate PL/SQL and the quality of the code.
As I have said in my last blog. I think these models are more useful if you already know what you are doing. They can be productivity assistants and get you some way along the process but the code in each case needed tweaking/fixing but certainly the qwen3-coder:30b is fast enough for daily use.
#oracleace #sym_42 #ai #llm #qwen3-coder #plsql #oracle #security #generate #code
