xref: /PHP-8.2/ext/oci8/tests/lob_prefetch.phpt (revision 9a90bd70) 
1--TEST--
2LOB prefetching
3--EXTENSIONS--
4oci8
5--SKIPIF--
6<?php
7$target_dbs = array('oracledb' => true, 'timesten' => false);  // test runs on these DBs
8require(__DIR__.'/skipif.inc');
9?>
10--FILE--
11<?php
12
13require __DIR__.'/connect.inc';
14require __DIR__.'/create_table.inc';
15
16define("NUMROWS", 200);
17define("LOBSIZE", 64000);
18
19$ora_sql =
20  "declare
21    c clob;
22    b blob;
23    numrows number := " . NUMROWS . ";
24    dest_offset integer := 1;
25    src_offset  integer := 1;
26    warn        integer;
27    ctx         integer := dbms_lob.default_lang_ctx;
28  begin
29    for j in 1..numrows
30    loop
31      c := DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(1000,1000)));
32      for i in 1..6
33      loop
34        c := c||c;
35      end loop;
36      dbms_lob.createtemporary(b, false);
37      dbms_lob.converttoblob(b, c, dbms_lob.lobmaxsize, dest_offset, src_offset, dbms_lob.default_csid, ctx, warn);
38      insert /*+ APPEND */ into {$schema}{$table_name} (id, clob, blob) values (j, c, b);
39    end loop;
40    commit;
41  end;";
42
43$statement = oci_parse($c,$ora_sql);
44oci_execute($statement);
45
46
47function get_clob_loc($c, $sql, $pfl) {
48    $stid = oci_parse($c, $sql);
49    if ($pfl >= 0)
50        oci_set_prefetch_lob($stid, $pfl);
51    oci_execute($stid);
52    $l = [];
53    while (($row = oci_fetch_array($stid, OCI_ASSOC)) != false) {
54        $l[] = $row['CLOB']->load();
55        $row['CLOB']->free();
56        if (strlen($l[0]) != LOBSIZE) { print("strlen(l) failure" .  strlen($l)); exit; }
57    }
58    return($l);
59}
60
61function get_clob_inline($c, $sql, $pfl) {
62    $stid = oci_parse($c, $sql);
63    if ($pfl >= 0)
64        oci_set_prefetch_lob($stid, $pfl);
65    oci_execute($stid);
66    $l = [];
67    while (($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_LOBS)) != false) {
68        $l[] = $row['CLOB'];
69        if (strlen($l[0]) != LOBSIZE) { print("strlen(l) failure" . strlen($l)); exit; }
70    }
71    return($l);
72}
73
74function check_clobs($locarr, $inlinearr) {
75    print("Comparing CLOBS\n");
76    for ($i = 0; $i < NUMROWS; ++$i) {
77        if (strlen($locarr[$i]) != LOBSIZE) {
78            trigger_error("size mismatch at $i " . strlen($locarr[$i]), E_USER_ERROR);
79            exit;
80        }
81        if (strcmp($locarr[$i], $inlinearr[$i])) {
82            trigger_error("data mismatch at $i " . strlen($locarr[$i]) . " " . strlen($inlinearr[$i]), E_USER_ERROR);
83            exit;
84        }
85    }
86}
87
88function get_blob_loc($c, $sql, $pfl) {
89    $stid = oci_parse($c, $sql);
90    if ($pfl >= 0)
91        oci_set_prefetch_lob($stid, $pfl);
92    oci_execute($stid);
93    $l = [];
94    while (($row = oci_fetch_array($stid, OCI_ASSOC)) != false) {
95        $l[] = $row['BLOB']->load();
96        $row['BLOB']->free();
97        if (strlen($l[0]) != LOBSIZE) { print("strlen(l) failure" .  strlen($l)); exit; }
98    }
99    return($l);
100}
101
102
103print("Test 1 - Default prefetch_lob_size\n");
104
105$r = ini_get("oci8.prefetch_lob_size");
106var_dump($r);
107
108print("Test 2\n");
109
110$s = oci_parse($c, 'select * from dual');
111$r = oci_set_prefetch_lob($s, 0);
112var_dump($r);
113
114try {
115    oci_set_prefetch_lob($s, -1);
116} catch (ValueError $e) {
117    echo $e->getMessage(), "\n";
118}
119
120print("Test 3 - CLOB prefetch_lob_size 100000\n");
121
122$sql = "select clob from {$schema}{$table_name}" . " order by id";
123$locarr = get_clob_loc($c, $sql, 100000);
124$inlinearr = get_clob_inline($c, $sql, 100000);
125
126print(count($locarr) . "\n");
127print(count($inlinearr) . "\n");
128check_clobs($locarr, $inlinearr);
129
130print("Test 3 - CLOB prefetch_lob_size 100\n");
131
132$locarr = get_clob_loc($c, $sql, 100);
133$inlinearr = get_clob_inline($c, $sql, 100);
134
135print(count($locarr) . "\n");
136print(count($inlinearr) . "\n");
137check_clobs($locarr, $inlinearr);
138
139print("Test 4 - BLOB prefetch_lob_size 100000\n");
140
141$sql = "select blob from {$schema}{$table_name}" . " order by id";
142$locarr = get_blob_loc($c, $sql, 100000);
143
144print(count($locarr) . "\n");
145
146require __DIR__.'/drop_table.inc';
147
148?>
149DONE
150--EXPECTF--
151Test 1 - Default prefetch_lob_size
152string(1) "0"
153Test 2
154bool(true)
155oci_set_prefetch_lob(): Argument #2 ($prefetch_lob_size) must be greater than or equal to 0
156Test 3 - CLOB prefetch_lob_size 100000
157200
158200
159Comparing CLOBS
160Test 3 - CLOB prefetch_lob_size 100
161200
162200
163Comparing CLOBS
164Test 4 - BLOB prefetch_lob_size 100000
165200
166DONE
167