Invantive SQL/PSQL provides means to execute SQL and PSQL-statements that are dynamically generated. The industry-term for such statements is “Dynamic SQL”. This topic provides some samples on use of dynamic SQL.
For execution of native SQL, T-SQL, PL/SQL, PL/pgSQL statements on SQL Server, PostgreSQL, Oracle, etc. instead of Invantive’s SQL/PSQL engine please refer to Evaluate Native SQL Statements from Invantive PSQL for practical guidance.
Dynamic SQL statements can use all Invantive SQL and PSQL features. Dynamic SQL statements execute within the security context of the calling PSQL block.
Dynamic SQL and PSQL statements on Invantive using the execute immediate statement. The SQL-grammar describes the syntax of the execute immediate statement.
An execute immediate statement can execute queries, stored procedures, DML-statements and DDL-statements such as create procedure or create table. When executing DDL-statements caution has to be applied to avoid locking issues or even deadlocks being generated.
Dynamic execution of SQL and PSQL-statements requires more time and attention regarding the correctness of the statements being executed, since trivial parts of the correctness check such as syntax are delayed from design-time to runtime.
For this topic, we will use an Exact Online database, but the samples can easily be mapped onto any supported cloud- or database-platform.
Typical Use Cases of Dynamic SQL
Dynamic SQL- and PSQL-statements are typically used when the logic must vary depending on parameters provided and requires a different SQL-statement that must be parsed and executed.
No need for dynamic SQL when different values
Dynamic SQL is not needed when solely parameters for a query or update statement need to be different; that functionality is automatically provided through parameter-binding such as:
select count(*)
from ExactOnlineREST..Journals@eol
where Code = '10'
The constant 10 can be replaced by a parameter for use with for instance the Invantive Query Tool as in:
select count(*)
from ExactOnlineREST..Journals@eol
where Code = :bindvariablename
Or for instance using PSQL-binding:
declare
l_cnt pls_integer;
l_journal_code varchar2;
begin
select count(*)
into l_cnt
from ExactOnlineREST..Journals@eol
where Code = l_journal_code
;
end;
Execution of Simple Dynamic Statement
The first example of dynamic SQL is to count the number of journals in the Exact Online company using a dynamic SQL statement. The non-dynamic variant is given above.
The dynamic SQL variant has a text constant containing the statement and then executes it:
begin
execute immediate
'select count(*) from exactonlinerest..journals@eol'
;
end
When executed, the Invantive parser will check the validity of the text as a SQL-statement and execute it. It will return a value like 56 (journals). But the count is quite useless in this. It is displayed nowhere. Using dynamic SQL without exchanging data is typically only useful to run DML-statements such as an delete or a stored procedure that performs and stores calculations.
Dynamic SQL with Output
In case you want to retrieve the outcome of the dynamic SQL-statement, the output can be stored into PSQL-variables as in:
declare
l_cnt1 pls_integer;
l_cnt2 pls_integer;
begin
use all@eol;
execute immediate
'select count(*), count(distinct code) from exactonlinerest..journals@eol'
into l_cnt1, l_cnt2
;
dbms_output.put_line
( 'There are '
|| to_char(l_cnt1)
|| ' journals with '
|| to_char(l_cnt2)
|| ' distinct codes.'
);
end;
After the into you can list output parameters to contain the output of the SQL-statement as a select-statement.
The output tab of the Query Tool will include output such as:
There are 44 journals with 17 distinct codes.
showing that there are 44 journals across all selected Exact Online companies.
Alternatively, you can also execute PSQL-statements dynamically which store outcome in a table to be queried later or use a stored procedure such as dbms_output.put_line.
Dynamic SQL with Input and Output
Often, you will want to provide parameter values to a dynamic SQL-statement. The brute force approach is to change the SQL-statement’s text according to your needs, such as:
declare
l_cnt1 pls_integer;
l_cnt2 pls_integer;
l_code varchar2 := '70';
begin
use all@eol;
execute immediate
'select count(*), count(distinct code) from exactonlinerest..journals@eol where code = '''
|| l_code
|| ''''
into l_cnt1, l_cnt2
;
dbms_output.put_line
( 'There are '
|| to_char(l_cnt1)
|| ' journals with '
|| to_char(l_cnt2)
|| ' distinct codes.'
);
end;
However, such an approach introduces security risks for SQL-injection that need to balanced. SQL injection is an attack vector that chooses the variable text in the SQL statement such that it is still a valid SQL-statement, but with a totally different meaning. For instance, through SQL injection stored procedures can be called or sensitive filters circumvented.
Also, unnecessary use of dynamic SQL reduces re-use of previously created execution plans, significantly lowering throughput in high-volume environments. Changing the SQL-statement’s text in this way is only recommended when making changes that lead to totally different SQL-statements, such as changing the source table’s name.
Remember to beforehand check that the value leaves no opening for SQL-injection!
For parameter values, a better approach is to use bind variables such as in the following code:
declare
l_cnt1 pls_integer;
l_cnt2 pls_integer;
l_code varchar2 := '70';
begin
use all@eol;
execute immediate
'select count(*), count(distinct code) from exactonlinerest..journals@eol where code = :1'
into l_cnt1, l_cnt2
using in l_code
;
dbms_output.put_line('There are ' || to_char(l_cnt1) || ' journals with ' || to_char(l_cnt2) || ' distinct codes.');
end;
The using clause specifies input parameters and output variables.
The value of l_code is bound to the first parameter :1. Multiple PSQL variables can be bound as input, output or exchanging values in both directions. For bi-directional data exchange you can use in out instead of in.
Using bind variables enables the Invantive SQL-engine to re-use previously created execution plans when the same statement is executed over and over again with different values for the bind variables.