-
Notifications
You must be signed in to change notification settings - Fork 18
/
sqlstatement.html
243 lines (181 loc) · 6.88 KB
/
sqlstatement.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
<!-- Creator : groff version 1.22.3 -->
<!-- CreationDate: Mon Mar 18 08:51:06 2024 -->
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta name="generator" content="groff -Thtml, see www.gnu.org">
<meta http-equiv="Content-Type" content="text/html; charset=US-ASCII">
<meta name="Content-Style" content="text/css">
<style type="text/css">
p { margin-top: 0; margin-bottom: 0; vertical-align: top }
pre { margin-top: 0; margin-bottom: 0; vertical-align: top }
table { margin-top: 0; margin-bottom: 0; vertical-align: top }
h1 { text-align: center }
</style>
<title>sqlstatement</title>
</head>
<body>
<h1 align="center">sqlstatement</h1>
<a href="#NAME">NAME</a><br>
<a href="#SYNTAX">SYNTAX</a><br>
<a href="#DESCRIPTION">DESCRIPTION</a><br>
<a href="#OPTIONS">OPTIONS</a><br>
<a href="#EXAMPLE">EXAMPLE</a><br>
<a href="#NOTES">NOTES</a><br>
<a href="#COPYRIGHT">COPYRIGHT</a><br>
<a href="#SEE ALSO">SEE ALSO</a><br>
<hr>
<h2>NAME
<a name="NAME"></a>
</h2>
<p style="margin-left:11%; margin-top: 1em">sqlstatement
− RWP*Load Simulator modify sql statements</p>
<h2>SYNTAX
<a name="SYNTAX"></a>
</h2>
<pre style="margin-left:11%; margin-top: 1em">sqlstatement ::=
<b>modify sql</b> identifier (
<b> cursorcache
</b>|<b> nocursorcache
</b>|<b> ignoreerror
</b>|<b> array</b> expression
|<b> array execute
</b>|<b> leak
</b>| modifydynamicsql
)
modifydynamicsql ::=
<b> for</b> concatenation
|<b> bind</b> expression identifier {<b> ,</b> expression identifier }
|<b> bindout</b> expression identifier {<b> ,</b> expression identifier }
|<b> define</b> expression identifier {<b> ,</b> expression identifier }
|<b> release</b></pre>
<h2>DESCRIPTION
<a name="DESCRIPTION"></a>
</h2>
<p style="margin-left:11%; margin-top: 1em">The <b>modify
sql</b> 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.</p>
<p style="margin-left:11%; margin-top: 1em">The statement
is always initiated by <b>modify sql name</b> where name is
the name of a sql variable declared earlier.</p>
<h2>OPTIONS
<a name="OPTIONS"></a>
</h2>
<p style="margin-left:11%; margin-top: 1em"><b>cursorcache</b>
or <b>nocursorcache</b></p>
<p style="margin-left:17%;">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.</p>
<p style="margin-left:11%; margin-top: 1em"><b>leak</b></p>
<p style="margin-left:17%;">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</p>
<pre style="margin-left:17%; margin-top: 1em">if uniform(0,1)<0.01 then
modify sql sqlname leak;
end if;</pre>
<p style="margin-left:17%; margin-top: 1em">before the code
that executes your sql statement.</p>
<p style="margin-left:11%; margin-top: 1em"><b>ignoreerror</b></p>
<p style="margin-left:17%;">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
<b>oraerror</b> and/or <b>oraerrortext</b></p>
<p style="margin-left:11%; margin-top: 1em"><b>array
expression</b></p>
<p style="margin-left:17%;">Set the number of rows in the
array interface for the sql statement.</p>
<p style="margin-left:11%; margin-top: 1em"><b>array
execute</b></p>
<p style="margin-left:17%;">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.</p>
<p style="margin-left:11%; margin-top: 1em"><b>for
concatenation</b></p>
<p style="margin-left:17%;">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.</p>
<p style="margin-left:11%; margin-top: 1em"><b>release</b></p>
<p style="margin-left:17%;">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.</p>
<p style="margin-left:11%; margin-top: 1em"><b>bind
placeholder variable [ , ... ]</b></p>
<p style="margin-left:11%; margin-top: 1em"><b>bindout
placeholder variable [ , ... ]</b></p>
<p style="margin-left:11%; margin-top: 1em"><b>define
position variable [ , ... ]</b></p>
<p style="margin-left:17%;">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 <b>:</b>. The
position is an expression resulting in a positive integer.
The variable is a named variable.</p>
<p style="margin-left:17%; margin-top: 1em">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 <b>modify sql</b> statement to do
so.</p>
<h2>EXAMPLE
<a name="EXAMPLE"></a>
</h2>
<p style="margin-left:11%; margin-top: 1em">This small
example shows how a dynamic sql can be declared and
used.</p>
<pre style="margin-left:11%; margin-top: 1em">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;</pre>
<h2>NOTES
<a name="NOTES"></a>
</h2>
<p style="margin-left:11%; margin-top: 1em">Embedded and
immediate sql do not have names and cannot be modified using
<b>modify sql</b>.</p>
<h2>COPYRIGHT
<a name="COPYRIGHT"></a>
</h2>
<p style="margin-left:11%; margin-top: 1em">Copyright
© 2023 Oracle Corporation</p>
<p style="margin-left:11%; margin-top: 1em">Licensed under
the Universal Permissive License v 1.0 as shown at
https://oss.oracle.com/licenses/upl</p>
<h2>SEE ALSO
<a name="SEE ALSO"></a>
</h2>
<p style="margin-left:11%; margin-top: 1em"><a href="sqldeclaration.html">sqldeclaration(1rwl)</a>,
<a href="sqlexecution.html">sqlexecution(1rwl)</a></p>
<hr>
</body>
</html>