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