Database Interaction

Executing SQL statements through query() and pquery()

To execute SQL commands, you can use the functions query() and pquery() that accept a string value as input parameter. For the string values, the alternate string notation [[SELECT ...]] is useful to avoid problems with single or double quotes within the SQL commands. The ending semicolon after the SQL commands is optional in the database scripts.

In case of an error during the call of query() function, the script terminates and returns the corresponding exception message. To protect such calls, you can use the special functions pquery() or pcall(). For more information, see Error Handling through pcall() and error().

Return Values of Function query()

The query() function returns the value depending on the query type:

1. SELECT statements

The query() function returns a two-dimensional array with read-only values. To process these values, you need to create another two-dimensional array and copy the values. However, the scripts are not applicable for operations on big data volume.

The first dimension of the result represents the rows, the second one represents the columns. Apart from addressing by numbers, you can also use column names. However, you have to consider the case (for example, result[i]["MY_COLUMN"]).

The number of rows can be determined through the #operator (#result) and the number of columns through the #operator on the first row (#result[1]). Additionally, you can access the SQL text through the key statement_text.

Example for SELECT: 

--/
create or replace script example() as
res = query([[SELECT user_name FROM EXA_ALL_SESSIONS]])

output("Number of rows returned by the SELECT query: ".. #res)   -- e.g., 12
output("Value of the USER_NAME column in the first row: ".. res[1].USER_NAME)  -- e.g., SYS
output("Value of the first column in the first row: ".. res[1][1])  -- e.g., SYS
/
execute script example() with output;

2. Other statements

The query() function returns a dictionary table containing the following keys:

• rows_inserted: Number of inserted rows.
• rows_updated: Number of updated rows.
• rows_deleted: Number of deleted rows.
• rows_affected: Sum of the other three values.
• statement_text: SQL text of the executed statement.
• statement_id: Statement ID within the session as string value.

When executing IMPORT and EXPORT commands, the following keys are defined:

• etl_rows_written: Number of inserted rows.
• etl_rows_with_error: Number of invalid rows (only reasonable if the REJECT LIMIT option was used).
• etl_rows_read: Number of read rows (sum of two numbers above).

Example for INSERT:

--/
create or replace script example() as
res = query([[INSERT INTO people(pid, name) VALUES (1, 'Julia')]])

output("Number of inserted rows: ".. res.rows_inserted)    -- shows: 1
output("Number of updated rows: ".. res.rows_updated)      -- shows: 0
output("Number of deleted rows: ".. res.rows_deleted)      -- shows: 0
output("Number of affected rows: ".. res.rows_affected)    -- shows: 1
/

execute script example() with output;

Return Values of Function pquery()

Similar to the pcall(), the pquery() function returns two values:

• Whether the execution was successful or not. It returns false in case of any error.
• The actual result of the query, if no error occurred. Otherwise a dictionary table containing the keys error_message and error_code. In both cases, you can access the SQL text through the key statement_text.

The result of the functions pcall() and pquery() is read-only.

Parametrized SQL Commands

You can use the parameterized SQL commands to execute dynamic SQL statements without creating a new SQL text. For example, you can execute a command within a script to create a table multiple times by passing different table names. In this case, you can use the table name as a parameter of the SQL statement.

However, such parameters are evaluated either as identifier or as a value before they are placed into the text. The reason for that approach is to avoid any security risk through SQL Injection.

You can use the following parameters within the SQL statements:

• :variable: Evaluates the content of the variable as constant value. For example, in a column filter query([[SELECT * FROM t WHERE i=:v]],{v=1}).
• ::variable: Evaluates the content of the variable as identifier. Delimited identifier have to be delimited by double quotes within the string. For example, table_name=[["t"]].

If you use a parametrized query() or pquery() function, you have to specify a dictionary table as second function parameter to define the variables. In this definition you can either specify constant values (for example, query([[SELECT * FROM t WHERE ::c=:v]],{c=column_name,v=1})) or assign script variables. Using script variables directly in the functions query()and pquery() is not possible.

If the second parameter is not specified, the SQL text is executed without interpretation. This is important if you want to create a script through query() or pquery() which contain such variables in the text.

Example

/*
 * just executing simple SQL statements
 */
query([[CREATE TABLE t(c CHAR(1))]])     -- creates table T
query([[CREATE TABLE "t"(c CHAR(1))]]) -- creates table t
local my_sql_text = [[INSERT INTO "T" VALUES 'a','b','c','d']]
query(my_sql_text)

/*
 * using result tables of a query by concatenating
 * all table entries into one result string
 */
local res_table = query([[SELECT c FROM t]])
local first_element = res_table[1][1] -- access first row of first column
local row_size = #res_table     -- row_size=4
if row_size==0 then
    col_size = 0
else
    col_size=#res_table[1]     -- col_size=1
end
local concat_str = ''
for col=1, col_size do
    for row=1,row_size do
        concat_str = concat_str..res_table[row][col] --> 'abcd'
    end
end

/*
 * using result information about manipulated rows
 */
local res = query([[
    MERGE INTO staff s USING update_table u ON (s.id=u.id)
    WHEN MATCHED THEN UPDATE SET s.salary=u.salary DELETE WHERE u.delete_flag
    WHEN NOT MATCHED THEN INSERT VALUES (u.id,u.salary)
]])
local i, u, d = res.rows_inserted, res.rows_updated, res.rows_deleted

/*
 * using pquery to avoid abort of script
 */
local success, result = pquery([[DROP USER my_user]])
if not success then
    -- accessing error message
    local error_txt = 'Could not drop user. Error: '..result.error_message
    query([[INSERT INTO my_errors VALUES (CURRENT_TIMESTAMP, :err)]],
        {err=error_txt})
end

/*
 * now using variables inside the SQL statements
 * and create 5 tables at once
 */
for i=1,5 do
    local tmp_table_name = 'TABLE_'..i
    query([[CREATE TABLE ::t (c CHAR(1))]], {t=tmp_table_name})
end

-- distribute 3 values to the first 3 tables created before
local values = {'x', 'y', 'z'}
for i=1,#values do
    local table_name='TABLE_'..i
    query([[INSERT INTO ::t VALUES :v]], {t=table_name,v=values[i]})
end

-- using both types of variables at once
query([[SELECT * FROM t WHERE ::x=:y]],{x='c',y='a'})

Scripting Parameters

To pass the parameters to a script, you can specify them as an input list within the script definition (CREATE SCRIPT). The parameters don't need any type definition. The simple variables are specified as identifiers which are case-sensitive within the script.

If you want to pass an array as a parameter, you need to declare that by using the keyword ARRAY before the identifier. When calling a script, you have to use the construct ARRAY(value1, value2, ...). By using this, you can pass the dynamic lists to a script. For example, a script can be called for a list of user names and can grant a certain system privilege, independent of the number of users.

Example

-- using script parameter for table name
--/
create script create_single_table (table_name) as
    query([[CREATE TABLE ::t (i INT)]],{t=table_name})
/

-- using ARRAY construct for multiple input
--/
create script create_multiple_tables (ARRAY table_names) as
    for i=1,#table_names do
        query([[CREATE TABLE ::t (i INT)]],{t=table_names[i]})
    end
/
EXECUTE SCRIPT create_single_table ('t1');
EXECUTE SCRIPT create_multiple_tables (ARRAY('t2','t3','t4'));
SELECT * FROM CAT;

Results

IMPORT of External Scripts

To access the functions that are defined in external scripts, you can use the import() function. The function executes the script for initialization reasons and provides all functions and globally-defined variables in a namespace that can be accessed through the script name. Alternatively, you can pass an alias as second parameter.

When including a script through the import() function, the imported script is executed and all functions and global variables are provided in the corresponding namespace.

In the following example, a script other_script is created that defines the function min_max(). You can access this function in the second script my_script after inclusion through import("schema1.other_script", "my_alias") by using the syntax my_alias.min_max().

If you want to import a parameterized script, you need to specify the parameters in the call of the import() function at the end after the optional alias. Such parameters can be constant values or script variables.

Example

--/
CREATE SCRIPT schema1.other_script AS
    function min_max(a,b,c)
        local min,max=a,b
        if a>b then min,max=b,a
        end
        if c>max then max=c
        elseif c<min then min=c
        end
        return min,max
    end
/

--/
CREATE SCRIPT schema2.my_script (param1, param2, param3) AS
import('schema1.other_script', 'my_alias')
-- accessing external function through alias
local lowest, highest = my_alias.min_max(param1, param2, param3)
output(lowest..','..highest)
/

Return Value of a Script

You can use the function exit() to return the values in a script. You can place this function at any place in the script, it terminates the script and returns the specified value.

The possible return type is specified in the CREATE SCRIPT command:

1. Option RETURNS ROWCOUNT or absence of any option

The value which is returned by the drivers as rowcount. If you omit the exit() parameter or if the script is not exited explicitly, then this value is 0.

If you directly return the result of a query execution through query() or pquery(), for example using exit(query([[INSERT INTO...]]), then the rowcount of that query is passed (not possible when executing a SELECT query).

Alternatively, you can specify the rowcount explicitly by passing a dictionary table which contains the key rows_affected (for example, exit({rows_affected=30})).

2. Option RETURNS TABLE

If you specify this option, the result of the script execution will be a table.

If no parameter is passed to the exit() function or if the script is not terminated explicitly, the result is an empty table. If you directly return the result of a query execution through query() or pquery() (for example, exit(query([[SELECT...]])), then the result table of this query is passed (only possible for SELECT queries).

Alternatively, you can specify a two-dimensional array. In that case you, have to specify the column types of the result table as second parameter (for more information see example of the CREATE TABLE command).

The result table of a script can be processed in other scripts, however, a persistent storage of that table in the database through SQL is not possible (similar to CREATE TABLE <table> AS SELECT...).
If you specify the WITH OUTPUT option within the EXECUTE SCRIPT statement, the RETURNS option of the script creation is overwritten (for more information, see Debug Output).

Example

-- script which returns rowcount
--/
CREATE SCRIPT script_1 AS
    function my_function(a, b)
        if a == true then
            exit()         -- exit whole script
        else
            return b, b+1     -- returning result of function
        end
    end
    local x, y = my_function(false, 5) --> y=6
    exit({rows_affected=y}) -- return rows_affected
/

-- script which returns result table of query
--/
CREATE SCRIPT script_2 RETURNS TABLE AS
    exit(query([[SELECT * FROM DUAL]]))
/

-- return explicitly created table
--/
CREATE SCRIPT script_3 RETURNS TABLE AS
    local result_table = {{decimal(1),"abc",true},
                   {decimal(2),"xyz",false},
                   {decimal(3),nil,nil}}
    exit(result_table, "i int, c char(3), b bool")
/
EXECUTE SCRIPT script_3;

Results

Metadata

You can access various metadata through global variables in each script to enhance the control of the script. The following table describes the metadata.

Debug Output

You can analyze the sequence of actions during the development of the scripts by using the debug output. The WITH OUTPUT option of the EXECUTE SCRIPT statement is provided for that. If you specify this option, every output created through output() is returned as a result table with one column and multiple rows (independent of the actual return value of the script).

The input parameter of the output() function is a single string. If you pass a parameter of a different type, an implicit conversion is tried, but if the conversion is not possible, the value NULL is inserted in the result table.

The column name of the result table is called output, and the column type is VARCHAR with the length of the longest string which was inserted through output().

If you omit the WITH OUTPUT option while executing the script, then debug option through output() is ignored.

Example

--/
CREATE SCRIPT my_script (param1, param2) AS
    output('SCRIPT started')
    
    if param1==false then
        output('PARAM1 is false, exit SCRIPT')
        exit()
    else
        output('PARAM2 is '..param2)
    end
    output('End of SCRIPT reached')
/

EXECUTE SCRIPT my_script (TRUE, 5) WITH OUTPUT;

Auxiliary Functions for Identifiers

You can use the following auxiliary functions to simplify working with the database:

• quote(param): Adds quotes around the parameter and doubles embedded quotes. This function is useful for delimited identifiers within SQL commands.
• join(p1, p2, p3, ...): Generates a string that includes all parameters p2,p3,..., separated by p1. By using the function call join(".",...), you can create schema qualified identifiers (for more information, see SQL Identifier.

Example

output("my_table")         -- regular identifier
output(quote("my_table"))     -- delimited identifier
output(join(".","my_schema","my_table","my_column"))
output(join(".",
    quote("my_schema"),
    quote("my_table"),
    quote("my_column")))

Considerations

--/
CREATE SCRIPT my_script AS
    -- this is the first line!
    function do_nothing()
    return
    end
    import(x) -- error: string expected
/