sqlstatement − RWP*Load Simulator modify sql statements
sqlstatement ::=
modify sql identifier (
cursorcache
| nocursorcache
| ignoreerror
| array expression
| array execute
| leak
| modifydynamicsql
)
modifydynamicsql ::=
for concatenation
| bind expression identifier { , expression identifier }
| bindout expression identifier { , expression identifier }
| define expression identifier { , expression identifier }
| release
The modify sql statement is used to change simple attributes of static and dynamic sql and to associate text and potentially extra bind/define variables with dynamic SQL.
The statement is always initiated by modify sql name where name is the name of a sql variable declared earlier.
cursorcache or nocursorcache
Turn the cursorcache on or off for the sql statement. When on, the Oracle Call Interface will attempt keeping the associated statement handle open after execution such that the next execute of the same sql will not cause a parse. When off, the statement handle is closed after execution.
leak
When next execute of the sql statement is complete, do not close it. The purpose of this is to simulate and observe the behavior of applications leaking cursors; it will typically result in ORA-01000 errors at some point in time. If you want to implement a slow cursor leak of 1% per execute, you could use code like
if uniform(0,1)<0.01 then modify sql sqlname leak; end if;
before the code that executes your sql statement.
ignoreerror
Normally, rwloadsim will report any errors during execution of a sql statement and potentially save this in the repository database. This can be turned off using this option, in which case it is recommended to program error handling using the variables oraerror and/or oraerrortext
array expression
Set the number of rows in the array interface for the sql statement.
array execute
If the sql is a dml with an array, the array is flushed to the database. Normally, this is only done (implicitly) at commit time.
for concatenation
Provide the SQL text for a dynamic sql statement, i.e. a sql variable that has been declared without providing the SQL or PL/SQL text. This must be done exactly once; the sql can then be executed as many times as needed.
release
Disassociate the sql text from a dynamic sql statement and reset the bind and define variables to those that are included with the declaration. You can subsequently associate a new sql statement with it.
bind placeholder variable [ , ... ]
bindout placeholder variable [ , ... ]
define position variable [ , ... ]
Use any of these to supply extra binds and/or defines needed a by a dynamic sql. The placeholder is either an expression resulting in a positive integer, or a string with the first character :. The position is an expression resulting in a positive integer. The variable is a named variable.
If you want to use implicit bind and/or define, the appropriate specifications must be provided when you declare your SQL statement; there is no modify sql statement to do so.
This small example shows how a dynamic sql can be declared and used.
sql getemps
define 1 ename, 2 sal;
array 10;
end;
# Dynamically provide the text of the query
modify sql getemps for
"select e.ename, e.sal, d.loc"
" from emp e join dept d"
" on e.deptno = d.deptno"
" where d.deptno = :1";
# one extra define needed
modify sql getemps define 3 loc;
# and one bind needed
modify sql getemps bind 1 deptno;
for deptno in 1..5 loop
# execute the dynamic SQL several times
for getemps loop
printline ename, sal, loc;
end loop;
end loop;
modify sql getemps release;
Embedded and immediate sql do not have names and cannot be modified using modify sql.
Copyright © 2023 Oracle Corporation
Licensed under the Universal Permissive License v 1.0 as shown at https://oss.oracle.com/licenses/upl
sqldeclaration(1rwl), sqlexecution(1rwl)