reference/oci8/examples.xml
5e41012cfdf8f2eff5fa56de446c7656afac536c
5e41012cfdf8f2eff5fa56de446c7656afac536c
...
...
@@ -2,100 +2,167 @@
2
2
<!-- $Revision$ -->
3
3
<chapter xml:id="oci8.examples" xmlns="http://docbook.org/ns/docbook" xmlns:xlink="http://www.w3.org/1999/xlink">
4
4
&reftitle.examples;
5
-
<section xml:id="oci8.examples-basic">
5
+
<para>
6
+
These examples connect as the <literal>HR</literal> user, which is
7
+
the sample "Human Resources" schema supplied with the
8
+
Oracle database. The account may need to be unlocked and the
9
+
password reset before you can use it.
10
+
</para>
11
+
12
+
<para>
13
+
The examples connect to the <literal>XE</literal> database on your
14
+
machine. Change the connect string to your database before running
15
+
the examples.
16
+
</para>
17
+
18
+
<example>
19
+
<title>Basic query</title>
6
20
<para>
7
-
<example>
8
-
<title>Basic query</title>
9
-
<programlisting role="php">
21
+
This shows querying and displaying results. Statements in OCI8 use
22
+
a prepare-execute-fetch sequence of steps.
23
+
</para>
24
+
<programlisting role="php">
10
25
<![CDATA[
11
26
<?php
12
27
13
-
$conn = oci_connect('hr', 'hr', 'orcl');
14
-
if (!$conn) {
28
+
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
29
+
if (!$conn) {
15
30
$e = oci_error();
16
-
print htmlentities($e['message']);
17
-
exit;
18
-
}
19
-
20
-
$query = 'SELECT * FROM DEPARTMENTS';
31
+
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
32
+
}
21
33
22
-
$stid = oci_parse($conn, $query);
23
-
if (!$stid) {
34
+
// Prepare the statement
35
+
$stid = oci_parse($conn, 'SELECT * FROM departments');
36
+
if (!$stid) {
24
37
$e = oci_error($conn);
25
-
print htmlentities($e['message']);
26
-
exit;
27
-
}
38
+
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
39
+
}
28
40
29
-
$r = oci_execute($stid, OCI_DEFAULT);
30
-
if (!$r) {
41
+
// Perform the logic of the query
42
+
$r = oci_execute($stid);
43
+
if (!$r) {
31
44
$e = oci_error($stid);
32
-
echo htmlentities($e['message']);
33
-
exit;
34
-
}
35
-
36
-
print '<table border="1">';
37
-
while ($row = oci_fetch_array($stid, OCI_RETURN_NULLS)) {
38
-
print '<tr>';
39
-
foreach ($row as $item) {
40
-
print '<td>'.($item?htmlentities($item):' ').'</td>';
41
-
}
42
-
print '</tr>';
43
-
}
44
-
print '</table>';
45
-
46
-
oci_close($conn);
45
+
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
46
+
}
47
+
48
+
// Fetch the results of the query
49
+
print "<table border='1'>\n";
50
+
while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
51
+
print "<tr>\n";
52
+
foreach ($row as $item) {
53
+
print " <td>" . ($item !== null ? htmlentities($item, ENT_QUOTES) : " ") . "</td>\n";
54
+
}
55
+
print "</tr>\n";
56
+
}
57
+
print "</table>\n";
58
+
59
+
oci_free_statement($stid);
60
+
oci_close($conn);
61
+
47
62
?>
48
63
]]>
49
-
</programlisting>
50
-
</example>
51
-
</para>
64
+
</programlisting>
65
+
</example>
66
+
67
+
<example>
68
+
<title>Inserting with bind variables</title>
52
69
<para>
53
-
<example>
54
-
<title>Insert with bind variables</title>
55
-
<programlisting role="php">
70
+
Bind variables improve performance by allowing reuse of execution
71
+
contexts and caches. Bind variables improve security by preventing
72
+
some kinds of SQL Injection problems.
73
+
</para>
74
+
<programlisting role="php">
56
75
<![CDATA[
57
76
<?php
58
77
59
-
// Before running, create the table:
60
-
// CREATE TABLE MYTABLE (mid NUMBER, myd VARCHAR2(20));
61
-
62
-
$conn = oci_connect('scott', 'tiger', 'orcl');
78
+
// Before running, create the table:
79
+
// CREATE TABLE MYTABLE (mid NUMBER, myd VARCHAR2(20));
63
80
64
-
$query = 'INSERT INTO MYTABLE VALUES(:myid, :mydata)';
81
+
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
82
+
if (!$conn) {
83
+
$e = oci_error();
84
+
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
85
+
}
65
86
66
-
$stid = oci_parse($conn, $query);
87
+
$stid = oci_parse($conn, 'INSERT INTO MYTABLE (mid, myd) VALUES(:myid, :mydata)');
67
88
68
-
$id = 60;
69
-
$data = 'Some data';
89
+
$id = 60;
90
+
$data = 'Some data';
70
91
71
-
oci_bind_by_name($stid, ':myid', $id);
72
-
oci_bind_by_name($stid, ':mydata', $data);
92
+
oci_bind_by_name($stid, ':myid', $id);
93
+
oci_bind_by_name($stid, ':mydata', $data);
73
94
74
-
$r = oci_execute($stid);
95
+
$r = oci_execute($stid); // executes and commits
75
96
76
-
if ($r)
97
+
if ($r) {
77
98
print "One row inserted";
99
+
}
78
100
79
-
oci_close($conn);
101
+
oci_free_statement($stid);
102
+
oci_close($conn);
80
103
81
104
?>
82
105
]]>
83
-
</programlisting>
84
-
</example>
106
+
</programlisting>
107
+
</example>
108
+
109
+
<example>
110
+
<title>Binding in the WHERE clause of a query</title>
111
+
<para>
112
+
This shows a single scalar bind.
85
113
</para>
114
+
<programlisting role="php">
115
+
<![CDATA[
116
+
<?php
117
+
118
+
$conn = oci_connect("hr", "hrpwd", "localhost/XE");
119
+
if (!$conn) {
120
+
$m = oci_error();
121
+
trigger_error(htmlentities($m['message']), E_USER_ERROR);
122
+
}
123
+
124
+
$sql = 'SELECT last_name FROM employees WHERE department_id = :didbv ORDER BY last_name';
125
+
$stid = oci_parse($conn, $sql);
126
+
$didbv = 60;
127
+
oci_bind_by_name($stid, ':didbv', $didbv);
128
+
oci_execute($stid);
129
+
while (($row = oci_fetch_array($stid, OCI_ASSOC)) != false) {
130
+
echo $row['LAST_NAME'] ."<br>\n";
131
+
}
132
+
133
+
// Output is
134
+
// Austin
135
+
// Ernst
136
+
// Hunold
137
+
// Lorentz
138
+
// Pataballa
86
139
140
+
oci_free_statement($stid);
141
+
oci_close($conn);
87
142
143
+
?>
144
+
]]>
145
+
</programlisting>
146
+
</example>
147
+
148
+
<example>
149
+
<title>Inserting and fetching a CLOB</title>
88
150
<para>
89
-
<example>
90
-
<title>Inserting data into a CLOB column</title>
91
-
<programlisting role="php">
151
+
For large data use binary long object (BLOB) or character long
152
+
object (CLOB) types. This example uses CLOB.
153
+
</para>
154
+
<programlisting role="php">
92
155
<![CDATA[
93
156
<?php
94
157
95
158
// Before running, create the table:
96
159
// CREATE TABLE MYTABLE (mykey NUMBER, myclob CLOB);
97
160
98
-
$conn = oci_connect('scott', 'tiger', 'orcl');
161
+
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
162
+
if (!$conn) {
163
+
$e = oci_error();
164
+
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
165
+
}
99
166
100
167
$mykey = 12343; // arbitrary key for this example;
101
168
...
...
@@ -107,7 +174,7 @@ $stid = oci_parse($conn, $sql);
107
174
$clob = oci_new_descriptor($conn, OCI_D_LOB);
108
175
oci_bind_by_name($stid, ":mykey", $mykey, 5);
109
176
oci_bind_by_name($stid, ":myclob", $clob, -1, OCI_B_CLOB);
110
-
oci_execute($stid, OCI_DEFAULT);
177
+
oci_execute($stid, OCI_NO_AUTO_COMMIT);
111
178
$clob->save("A very long string");
112
179
113
180
oci_commit($conn);
...
...
@@ -118,47 +185,161 @@ $query = 'SELECT myclob FROM mytable WHERE mykey = :mykey';
118
185
119
186
$stid = oci_parse ($conn, $query);
120
187
oci_bind_by_name($stid, ":mykey", $mykey, 5);
121
-
oci_execute($stid, OCI_DEFAULT);
188
+
oci_execute($stid);
122
189
123
190
print '<table border="1">';
124
-
while ($row = oci_fetch_array($stid, OCI_ASSOC)) {
125
-
$result = $row['MYCLOB']->load();
126
-
print '<tr><td>'.$result.'</td></tr>';
191
+
while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_LOBS)) {
192
+
print '<tr><td>'.$row['MYCLOB'].'</td></tr>';
193
+
// In a loop, freeing the large variable before the 2nd fetch reduces PHP's peak memory usage
194
+
unset($row);
127
195
}
128
196
print '</table>';
129
197
130
198
?>
131
199
]]>
132
-
</programlisting>
133
-
</example>
200
+
</programlisting>
201
+
</example>
202
+
203
+
<example>
204
+
<title>Using a PL/SQL stored function</title>
205
+
<para>
206
+
You must bind a variable for the return value and optionally for
207
+
any PL/SQL function arguments.
134
208
</para>
209
+
<programlisting role="php">
210
+
<![CDATA[
211
+
<?php
212
+
213
+
/*
214
+
Before running the PHP program, create a stored function in
215
+
SQL*Plus or SQL Developer:
216
+
217
+
CREATE OR REPLACE FUNCTION myfunc(p IN NUMBER) RETURN NUMBER AS
218
+
BEGIN
219
+
RETURN p * 3;
220
+
END;
221
+
222
+
*/
223
+
224
+
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
225
+
if (!$conn) {
226
+
$e = oci_error();
227
+
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
228
+
}
229
+
230
+
$p = 8;
231
+
232
+
$stid = oci_parse($conn, 'begin :r := myfunc(:p); end;');
233
+
oci_bind_by_name($stid, ':p', $p);
234
+
oci_bind_by_name($stid, ':r', $r, 40);
235
+
236
+
oci_execute($stid);
237
+
238
+
print "$r\n"; // prints 24
239
+
240
+
oci_free_statement($stid);
241
+
oci_close($conn);
242
+
243
+
?>
244
+
]]>
245
+
</programlisting>
246
+
</example>
247
+
248
+
<example>
249
+
<title>Using a PL/SQL stored procedure</title>
135
250
<para>
136
-
You can easily access stored procedures in the same way as you
137
-
would from the command line.
138
-
<example>
139
-
<title>Using Stored Procedures</title>
140
-
<programlisting role="php">
251
+
With stored procedures, you should bind variables for any arguments.
252
+
</para>
253
+
<programlisting role="php">
141
254
<![CDATA[
142
255
<?php
143
-
// by webmaster at remoterealty dot com
144
-
$sth = oci_parse($dbh, "begin sp_newaddress( :address_id, '$firstname',
145
-
'$lastname', '$company', '$address1', '$address2', '$city', '$state',
146
-
'$postalcode', '$country', :error_code );end;");
147
256
148
-
// This calls stored procedure sp_newaddress, with :address_id being an
149
-
// in/out variable and :error_code being an out variable.
150
-
// Then you do the binding:
257
+
/*
258
+
Before running the PHP program, create a stored procedure in
259
+
SQL*Plus or SQL Developer:
260
+
261
+
CREATE OR REPLACE PROCEDURE myproc(p1 IN NUMBER, p2 OUT NUMBER) AS
262
+
BEGIN
263
+
p2 := p1 * 2;
264
+
END;
151
265
152
-
oci_bind_by_name($sth, ":address_id", $addr_id, 10);
153
-
oci_bind_by_name($sth, ":error_code", $errorcode, 10);
154
-
oci_execute($sth);
266
+
*/
267
+
268
+
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
269
+
if (!$conn) {
270
+
$e = oci_error();
271
+
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
272
+
}
273
+
274
+
$p1 = 8;
275
+
276
+
$stid = oci_parse($conn, 'begin myproc(:p1, :p2); end;');
277
+
oci_bind_by_name($stid, ':p1', $p1);
278
+
oci_bind_by_name($stid, ':p2', $p2, 40);
279
+
280
+
oci_execute($stid);
281
+
282
+
print "$p2\n"; // prints 16
283
+
284
+
oci_free_statement($stid);
285
+
oci_close($conn);
155
286
156
287
?>
157
288
]]>
158
-
</programlisting>
159
-
</example>
289
+
</programlisting>
290
+
</example>
291
+
292
+
<example>
293
+
<title>Calling a PL/SQL function that returns a <literal>REF CURSOR</literal></title>
294
+
<para>
295
+
Each returned value from the query is a <literal>REF
296
+
CURSOR</literal> that can be fetched from.
160
297
</para>
161
-
</section>
298
+
<programlisting role="php">
299
+
<![CDATA[
300
+
<?php
301
+
/*
302
+
Create the PL/SQL stored function as:
303
+
304
+
CREATE OR REPLACE FUNCTION myfunc(p1 IN NUMBER) RETURN SYS_REFCURSOR AS
305
+
rc SYS_REFCURSOR;
306
+
BEGIN
307
+
OPEN rc FOR SELECT city FROM locations WHERE ROWNUM < p1;
308
+
RETURN rc;
309
+
END;
310
+
*/
311
+
312
+
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
313
+
314
+
$stid = oci_parse($conn, 'SELECT myfunc(5) AS mfrc FROM dual');
315
+
oci_execute($stid);
316
+
317
+
echo "<table border='1'>\n";
318
+
while (($row = oci_fetch_array($stid, OCI_ASSOC))) {
319
+
echo "<tr>\n";
320
+
$rc = $row['MFRC'];
321
+
oci_execute($rc); // returned column value from the query is a ref cursor
322
+
while (($rc_row = oci_fetch_array($rc, OCI_ASSOC))) {
323
+
echo " <td>" . $rc_row['CITY'] . "</td>\n";
324
+
}
325
+
oci_free_statement($rc);
326
+
echo "</tr>\n";
327
+
}
328
+
echo "</table>\n";
329
+
330
+
// Output is:
331
+
// Beijing
332
+
// Bern
333
+
// Bombay
334
+
// Geneva
335
+
336
+
oci_free_statement($stid);
337
+
oci_close($conn);
338
+
339
+
?>
340
+
]]>
341
+
</programlisting>
342
+
</example>
162
343
</chapter>
163
344
164
345
<!-- Keep this comment at the end of the file
165
346