PL/SQL Translation Guidelines

Oracle’s proprietary PL/SQL language allows developers to create functions / procedures / packages in which queries can be executed and retrieved data can be processed in a procedural fashion. In some cases, for example, when implementing algorithms that need to work on data structures not natively supported by standard SQL datatypes and iteratively processed, PL/SQL can be helpful.

Sometimes PL/SQL is used to group multiple dependent SQL statements and execute them sequentially without loading data from the database into PL/SQL, as a surrogate for a multi-step ETL-Job for example. That’s usually the focus of ETL-Tools.

Every time the Oracle engine needs to switch from the SQL context to PL/SQL there is an overhead involved. These context switches have a negative impact on performance. So, it is highly recommended to use SQL instead of PL/SQL whenever possible.

When migrating from Oracle to Exasol, rethink the design choices made in Oracle that may no longer be the best option for Exasol, especially when migrating PL/SQL from Oracle to Exasol.

Sometimes, due to the complexity of the dependencies, interfaces and behaviors of old structures must be supported in a first stage of the migration, so that they can re-factored later.

This article helps you translate PL/SQL in Oracle to Lua in Exasol. Lua is a scripting language that is tightly integrated in Exasol. User Defined Functions (UDFs) in Exasol, which can be used in SQL statements, can be written in Lua, Python, Java and R. Within UDFs you can use the capabilities of the programming language of your choice, and even integrate external libraries, to write functions that can be used in SQL.

Scripts on the other hand can only be written in Lua and allow you, for example, to send queries to Exasol, fetch the results, transform the data, and write it back to Exasol again.

Scripts in Exasol, just as PL/SQL in Oracle, are not designed to process masses of data, but rather to design and control process flow and group processes into one executable script. Therefore, for mass processing data in the Database always use SQL.

Oracle provides built-in procedures and packages, and some of them might be in use in your Oracle environment. The DBMS_SCHEDULER package automatically executes processes depending on a defined time, event or dependency (to another process). Exasol does not have a built-in scheduler but an external scheduler, like cron can be used instead. For more information, see Scheduling Database Jobs.

For more information about:

Datatypes

While in PL/SQL variables and their datatypes must be pre-defined in the declare block of the code, variables in Lua can be created on the spot without datatype definition.

Oracle PL/SQL

Exasol Lua

Comments

Null

null / NULL

 

Numbers

DECIMAL

DECIMAL

 

PLS_INTEGER

DECIMAL

 

NUMBER

NUMBER

 

DOUBLE PRECISION

NUMBER

 

FLOAT

NUMBER

 

BINARY DOUBLE

NUMBER

 

BINARY FLOAT

NUMBER

 

Datetime

DATE

Not supported

For more information on os.date and os.time in Lua Documentation.

TIMESTAMP

Not supported

INTERVAL YEAR TO MONTH

Not supported

 

INTERVAL DAY TO SECOND

Not supported

 

Character

CHAR

STRING

 

NCHAR

STRING

 

VARCHAR2

STRING

 

NVARCHAR2

STRING

 

Data Structures

 

 

Array

TABLE

Indexing tables with Integers

Record

TABLE

The equivalent would be a table, but the fetched data from a query is loaded into a read-only userdata structure first.

 

Cursor

TABLE

Variables

The following table shows you examples of how variables are translated.

Description Oracle PL/SQL Exasol

Shows the definition of a variable and assignment of a value.

Within double square brackets, the whole string is interpreted as it is, including newlines. 

declare
v_string varchar2(100); 
begin
v_string := 'this "is" a' || chr(10) || '''newline'' in a string';
end;
 
v_string = 'this "is" a\n\'newline\' in a string'
--Or
v_string = [[this "is" a 
'newline' in a string]]

Shows the definition of a constant.

Constants are supported by Lua 5.4, which is used in Exasol 7.1.

 
declare
    v_string constant varchar2(100) := 'never changes'; 
begin 
…
end;
 
local a <const> = 'never changes'

Shows a variable with a referenced data type. In the example the variable v_string has the datatype of the column NAME, of the table EXMPLOYEE in the schema HR. This feature is not natively supported by Lua and has to be done manually.

To check if a variable contains a value that fits into a referenced data type, the data type of the reference must be retrieved, and the type of the value must be checked.

 
declare 
v_string hr.employee.name%type; 
begin 
v_string := 'James'; 
...
end;

Not supported

Shows how a single value form an SQL query can be assigned to a variable with a referenced data type. The query function in Exasol’s Lua returns a user data object, that can contain multiple rows and columns.

The first index of the returned object defines the row, the second index the column. Instead of an index, the column names / identifiers can be used too.

If the columns are not defined as case sensitive, they are accessed in uppercase column names. It is recommended to use standard SQL identifiers.

To send queries to Exasol from a script, the functions query or pquery must be used.

 
declare 
vname hr.employee.name%type; 
v_last_name hr.employee.last_name%type; 
v_birthday hr.employee.birthday%type; 
begin 
select name, last_name, birthday into v_name, v_last_name, v_birthday
from hr.employee 
where id = 101; 
dbms_output.put_line(r_emp.last_name || ', ' || r_emp.name || ' - ' || r_emp.birthday); 
...
end;
 
r_emp = query([[select name, “last_name”, birthday from hr.employee where id = 101]])[1]
output(r_emp.last_name .. ', ' .. r_emp.NAME .. ' - '  .. r_emp.BIRTHDAY)
Shows the data types of a whole row are referenced by the variable and loaded via a select into. 
declare
r_emp hr.employee%rowtype; 
begin 
select * into r_emp 
from hr.employee 
where id = 101; 
dbms_output.put_line(r_emp.last_name || ', ' || r_emp.name || 
    ' - ' || r_emp.address); 
…

 End;
 
r_emp = query([[select * from hr.employee where id = 101]])[1]
output(r_emp.LAST_NAME .. ', ' .. r_emp.NAME .. ' - ' .. r_emp.BIRTHDAY)

Control Structures

The following table shows you examples of how control structures are translated.

Description Oracle PL/SQL Exasol

Shows a basic loop that does 6 iterations (0 - 5), with a break/exit condition within the loop.

The CONTINUE ends the current iteration and starts with the next one. The conditional CONTINUE and EXIT commands must be translated using IF statement in Lua.

 
declare 
x NUMBER := 0; 
BEGIN 
LOOP 
DBMS_OUTPUT.PUT_LINE ('Inside loop: x = ' || TO_CHAR(x)); 
x := x + 1;
CONTINUE WHEN x < 3;
DBMS_OUTPUT.PUT_LINE ('Inside loop, after CONTINUE: x = ' || TO_CHAR(x));
EXIT WHEN x = 5; 
END LOOP; 
DBMS_OUTPUT.PUT_LINE (' After loop: x = ' || TO_CHAR(x)); 
END; 
/
 
--/
CREATE OR REPLACE SCRIPT util.exa_loop() AS 
x=0 
while (true) do 
print('inside loop - ' .. x)     
x = x + 1     
if(not (x < 3)) then         
print('inside loop, after "continue" - ' .. x)     
end     
if(x==5) then         
break     
end 
end 
print('outside loop - ' .. x) 
/
; 
EXECUTE SCRIPT util.exa_loop() WITH OUTPUT;

Shows three different variants of a FOR loop:

  • Loop 1: using numeric literals from -5 to 5
  • Loop 2: using variables in reverse order
  • Loop 3: using a numeric expression as an upper bound

For more information, see Numeric FOR and Generic FOR in Lua Documentation.

 
declare 
first INTEGER := 1; 
last INTEGER := 10; 
high INTEGER := 100;
low INTEGER := 12; 
BEGIN 
-- Bounds are numeric literals: 
FOR j IN -5..5 LOOP 
DBMS_OUTPUT.PUT_LINE ('loop 1 - ' || TO_CHAR(j)); 
END LOOP;
 -- Bounds are numeric variables: 
FOR k IN REVERSE first..last LOOP 
DBMS_OUTPUT.PUT_LINE ('loop 2 - ' || TO_CHAR(k)); 
END LOOP;
 -- Lower bound is numeric literal, 
-- Upper bound is numeric expression: 
FOR step IN 0..(TRUNC(high/low) * 2) LOOP 
DBMS_OUTPUT.PUT_LINE ('loop 3 - ' || TO_CHAR(step)); 
END LOOP; 
END; 
/
 
--/
CREATE OR REPLACE SCRIPT util.exa_for_loop() AS 
first = 1 
last = 10 
high = 100 
low = 12 
for j=-5, 5 do 
print('loop 1 - ' .. j) 
end 
for k=last, first, -1 do 
print('loop 2 - ' .. k) 
end 
for step=0, (math.floor(high/low)*2) do 
print('loop 3 - ' .. step) 
end 
/
; 
EXECUTE SCRIPT util.exa_for_loop() WITH OUTPUT;

Shows IF and ELSIF statements (functionally equivalent to the CASE statement in PL/SQL).

For more information, see IF Then ELSE in Lua Documentation.

 
declare 
a number := 2; 
b number := a * 2; 
c number := b * 2; 
begin 
IF a > b THEN 
DBMS_OUTPUT.PUT_LINE('a > b'); 
ELSIF b > c THEN 
DBMS_OUTPUT.PUT_LINE('b > c'); 
ELSE 
DBMS_OUTPUT.PUT_LINE('a < b < c'); 
END IF; 
end;
 
--/
CREATE OR REPLACE SCRIPT util.exa_if_case() AS 
a = 2 
b = a * 2 
c = b * 2 
if a > b then 
print('a > b') 
elseif b > c then 
print('b > c') 
else 
print('a < b < c ') 
end 
/
; 
EXECUTE SCRIPT util. exa_if_case () WITH OUTPUT;

Shows a WHILE loop with a condition is shown.

For more information, see WHILE in Lua Documentation.

The query function accepts a second parameter after the query, for string replacement. For object names in the database, the replacement string needs double colons, while other replacements just need one colon. For example,

Col=’NAME’
SCH=’UTIL’
TAB=’EMP’
ID=1337
Res = query([[select ::c from ::s.::t where id = :f]], {c=col, s=sch, t=tab, f=id})
 
declare
v_emp_id number := 6;
v_emp_birthday varchar2(20);
v_counter number := 3;

begin
while v_emp_id <= 400 loop
select emp_id, emp_birthday into v_emp_id, v_emp_birthday
from EMP2
where emp_id = v_emp_id;
DBMS_OUTPUT.PUT_LINE(v_emp_id || ' - ' || v_emp_birthday);
v_emp_id := v_emp_id * 2;
end loop;
end;
 
CREATE OR REPLACE SCRIPT util.exa_while(v_emp_id) AS 
while v_emp_id <= 400 do     
res = query([[select emp_id, emp_birthday from util.emp2 where emp_id = :i]],
{i=v_emp_id})[1]     
print(res.EMP_ID .. ' - ' .. res.EMP_BIRTHDAY) 
v_emp_id = v_emp_id * 2 
end 
/
; 
EXECUTE SCRIPT util.goto_1(6) WITH OUTPUT;

The GOTO command should be avoided and rewritten with other control structures and functions for readability and maintenance reasons, even though its supported. The following example shows how it can be translated without using GOTO.

For more information on GOTO, see Control Structure in Lua Documentation. 

declare 
p VARCHAR2(30); 
n PLS_INTEGER := 29; 
BEGIN 
FOR j in 2..ROUND(SQRT(n)) LOOP 
IF n MOD j = 0 THEN 
p := ' is not a prime number'; 
GOTO print_now; 
END IF; 
END LOOP;
p := ' is a prime number'; 
<<print_now>> 
DBMS_OUTPUT.PUT_LINE(TO_CHAR(n) || p); 
END; 
/
 
--/
CREATE OR REPLACE SCRIPT util.exa_goto(n) AS 
p = ' is a prime number' 
for j=2, math.floor(math.sqrt(n)+0.5) do
if n%j == 0 then
p = ' is not a prime number' 
break     
end 
end 
print(n .. p) 
/
; 
EXECUTE SCRIPT util.exa_goto(29) WITH OUTPUT;

Cursor

The following table shows you examples of how cursors are translated.

Description Oracle PL/SQL Exasol

Shows a cursor, with its underlying query, that defines the data to be loaded. When the cursor is opened, the underlying query is executed. Within the following loop each row of the cursor is fetched into a variable with the row type of the cursor, until no more rows are left. The memory is freed by closing the cursor. The alternative representation does all that in a shorter, implicit way.

In Lua the query function returns a user data object with the relevant data defined by the query, that can be used as cursor. Note that when the query function executes the query, all the data is loaded into the memory space of Lua. By setting the data variable to nil, the memory can be freed by the Lua garbage collector.

Avoid processing masses of data this way. 

declare 
cursor c_emp is
select id, name, last_name, address, birthday 
from hr.employee 
where id between 100 and 200; 
r_emp c_emp%rowtype; 
begin 
open c_emp; 
loop 
fetch c_emp into r_emp; 
exit when c_emp&notfound; 
dbms_output.put_line(r_emp.birthday); 
end loop; 
close c_emp; 
r_emp := null; 
end;

Or

begin 
for r in (select id, name, last_name, address, birthday from hr.employee where id between 100 and 200) loop 
dbms_output.put_line(r_emp.birthday); 
end loop; 
end;
 
--/
CREATE OR REPLACE SCRIPT util.exa_cursor_1() AS
c_emp = query([[select id, name, last_name, address, birthday from hr.employee where id between 100 and 200]]) 
for r_emp=1, #c_emp do 
output(c_emp[r_emp].BIRTHDAY) 
end 
c_emp = nil
/    
;
EXECUTE SCRIPT util. exa_cursor_1 () WITH OUTPUT;

Shows how variables are used in the query of the cursor definition.

 
declare 
cursor c_emp(l number, h number) is select id, name, last_name, address, birthday from hr.employee where id between l and h; 
begin 
for r_emp in c_emp(140, 150) loop 
dbms_output.put_line(r_emp.birthday); 
end loop; 
end;
 
--/
CREATE OR REPLACE SCRIPT util.exa_cursor_2(l, h) AS 
q = [[select emp_id, man_id, emp_birthday from util.emp3 where emp_id between :l and :h]] 
c_emp = query(q, {l=l, h=h}) 
for r_emp=1, #c_emp do 
output(c_emp[r_emp].EMP_BIRTHDAY)
 end 
c_emp = nil
/
; 
EXECUTE SCRIPT util.exa_cursor_2(140, 150) WITH OUTPUT;

Shows a cursor using BULK COLLECT and FORALL. BULK COLLECT defines a chunk size, so that instead of a single row into a variable, the defined number of rows can be loaded into a table at once. FORALL reduces the number of context switches between the PL/SQL and SQL processor. It collects all the queries using prepared statements and pushes them down to the database via a single context switch.

While Exasol returns the result set of a query in bulk, like bulk collect, chunking the result set is not supported. Chunking would have to be done manually within the query itself. When using data in Lua to write back to the database, each tuple is passed on from Lua to SQL individually, meaning data/statements cannot be passed from Lua to SQL in bulk (like in FORALL) and must be looped over.

This example could also be easily and efficiently implemented using normal SQL.

 
declare     
cursor c_name is select pnum, pname from util.src_name where mod(pnum, 17) = 4;
type t_name is table of c_name%rowtype index by pls_integer;     
v_name t_name; 
begin     
open c_name;     
loop         
fetch c_name bulk collect into v_name limit 20; 
exit when v_name.count = 0;         
dbms_output.put_line('-- new loop -- ' || v_name.count);         
forall i in indices of v_name             
Insert into util.trg_name(pnum, pname) values(v_name(i).pnum, v_name(i).pname);              
end loop;     
commit; 
end;
 
--/
CREATE OR REPLACE SCRIPT util.exa_bulk_collect_forall() AS 
c_name = query([[select pnum, pname from src_name where mod(pnum, 17) = 4]]) 
for i=1, #c_name do     
query([[insert into trg_name(pnum, pname) values(:n, :s)]], {n=c_name[i].PNUM, s=c_name[i].PNAME}) 
end 
/ 
;
EXECUTE SCRIPT util.exa_bulk_collect_forall();

Shows how external procedures are called. The procedure ora_proc_1 is called in an anonymous PL/SQL block. In order to call functions of external Scripts in Lua, those scripts need to be imported. 

create or replace procedure util.ora_proc_1(i_emp_id in number, o_emp_bd out date) as 
begin
select emp_birthday into o_emp_bd from util.emp where emp_id = i_emp_id;
end
/ 
;
declare
v_emp_bd date; 
begin  
util.ora_proc_1(187, v_emp_bd);  
for r in (select emp_id, emp_bd from util.emp where trunc(emp_bd, 'month') = trunc(v_emp_bd, 'month')) loop 
dbms_output.put_line(r.emp_id || ' ^ ' || r.emp_bd);  
end loop;
end
/
;
 
--/
CREATE OR REPLACE SCRIPT util.exa_proc_1() RETURNS TABLE AS     
function get_bd(emp_id) 
return query([[select trunc(emp_birthday, 'month') EMP_BIRTHDAY from emp3 where emp_id = :n]], {n=emp_id})[1].EMP_BD    
end     
/
 ; 

--/
create or replace script util.exa_proc_2(emp_id) as 
import('util.exa_proc_1', 'p1') 
bd = p1.get_bd(emp_id) 
res = query([[select emp_id, emp_birthday from emp3 where trunc(emp_birthday, 'month') = :s]], {s=bd}) 
for i=1, #res do     
output(res[i].EMP_ID .. ' ^ ' .. res[i].EMP_BD) 
end
/ 
; 
 EXECUTE SCRIPT util.exa_proc_2(233) WITH OUTPUT;

Shows the use of a SYS_REFCURSOR. The SYS_REFCURSOR is a weak cursor compared to a regular cursor, because its structure is only defined during runtime.

The procedure get_my_results returns a different cursor, depending on the input parameter. The anonymous block only knows that a cursor is expected, but not what it is going to look like. Since data types in Lua are defined during run-time, it can natively support this behavior.

 
create or replace procedure get_my_results (arg number) 
return 
sys_refcursor 
IS 
myrefcur sys_refcursor;
begin
if arg = 1 then
    open myrefcur for select country from t1;
elsif arg = 2 then
    OPEN myrefcur for select address from t1;
end if; 
return(myrefcur);
end
/
;
declare
mycur sys_refcursor; 
v_s varchar2(20);
begin
mycur := get_my_results(1);
fetch mycur into v_s;
dbms_output.put_line(v_s);

mycur := get_my_results(2);
fetch mycur into v_s;
dbms_output.put_line(v_s);
end;
 
--/
CREATE OR REPLACE SCRIPT util.rc_1 RETURNS TABLE AS     
function get_my_results(id)         
    if(id == 1) then
        return [[select country from t1]]
    elseif(id == 2) then
        return [[select address from t1]]
    end
end     
/
;

--/
CREATE OR REPLACE SCRIPT util.rc_2(id) AS 
import('util.rc_1', 'rc_1') 
res = rc_1.get_my_results(id) 
output(res[1][1]) 
/
;
EXECUTE SCRIPT util.rc_2(1) WITH OUTPUT;
EXECUTE SCRIPT util.rc_2(2) WITH OUTPUT;

Exceptions

Errors and exceptions in Lua can be handled easily by encapsulating the piece of code in a functional and calling that function through the pcall function. For more information, see Lua - Error Handling and Exceptions.

Errors can also occur when interacting with Exasol over SQL and for that there is a protected query method, pquery(). For more information, see Exasol – Database Interaction.