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
