1--TEST-- 2Test null data for CLOBs 3--SKIPIF-- 4<?php 5$target_dbs = array('oracledb' => true, 'timesten' => false); // test runs on these DBs 6require(dirname(__FILE__).'/skipif.inc'); 7?> 8--FILE-- 9<?php 10 11require dirname(__FILE__).'/connect.inc'; 12 13// Initialization 14 15$s = oci_parse($c, 'drop table lob_null_tab'); 16@oci_execute($s); 17 18$s = oci_parse($c, 'create table lob_null_tab (id number, data clob)'); 19oci_execute($s); 20 21$s = oci_parse($c, 22'create or replace procedure lob_null_proc_in (pid in number, pdata in CLOB) 23 as begin 24 insert into lob_null_tab (id, data) values (pid, pdata); 25 end;'); 26oci_execute($s); 27 28$s = oci_parse($c, 29'create or replace procedure lob_null_proc_out (pid in number, pdata out clob) 30 as begin 31 select data into pdata from lob_null_tab where id = pid; 32 end;'); 33oci_execute($s); 34 35// TEMPORARY CLOB 36 37echo "Temporary CLOB: NULL\n"; 38$s = oci_parse($c, "insert into lob_null_tab values (1, :b)"); 39$lob = oci_new_descriptor($c, OCI_D_LOB); 40oci_bind_by_name($s, ':b', $lob, -1, OCI_B_CLOB); 41$lob->writeTemporary(null); 42$r = @oci_execute($s); 43if (!$r) { 44 $m = oci_error($s); 45 echo $m['message'], "\n"; 46} 47else { 48 $lob->close(); 49} 50 51echo "Temporary CLOB: ''\n"; 52$s = oci_parse($c, "insert into lob_null_tab values (2, :b)"); 53$lob = oci_new_descriptor($c, OCI_D_LOB); 54oci_bind_by_name($s, ':b', $lob, -1, OCI_B_CLOB); 55$lob->writeTemporary(''); 56$r = @oci_execute($s); 57if (!$r) { 58 $m = oci_error($s); 59 echo $m['message'], "\n"; 60} 61else { 62 $lob->close(); 63} 64 65echo "Temporary CLOB: text\n"; 66$s = oci_parse($c, "insert into lob_null_tab values (3, :b)"); 67$lob = oci_new_descriptor($c, OCI_D_LOB); 68oci_bind_by_name($s, ':b', $lob, -1, OCI_B_CLOB); 69$lob->writeTemporary('Inserted via SQL statement'); 70$r = @oci_execute($s); 71if (!$r) { 72 $m = oci_error($s); 73 echo $m['message'], "\n"; 74} 75else { 76 $lob->close(); 77} 78 79// PROCEDURE PARAMETER 80 81echo "Procedure parameter: NULL\n"; 82$s = oci_parse($c, "call lob_null_proc_in(4, :b)"); 83$lob = oci_new_descriptor($c, OCI_D_LOB); 84oci_bind_by_name($s, ':b', $lob, -1, OCI_B_CLOB); 85$lob->writeTemporary(null); 86$r = @oci_execute($s); 87if (!$r) { 88 $m = oci_error($s); 89 echo $m['message'], "\n"; 90} 91else { 92 $lob->close(); 93} 94 95echo "Procedure parameter: ''\n"; 96$s = oci_parse($c, "call lob_null_proc_in(5, :b)"); 97$lob = oci_new_descriptor($c, OCI_D_LOB); 98oci_bind_by_name($s, ':b', $lob, -1, OCI_B_CLOB); 99$lob->writeTemporary(''); 100$r = @oci_execute($s); 101if (!$r) { 102 $m = oci_error($s); 103 echo $m['message'], "\n"; 104} 105else { 106 $lob->close(); 107} 108 109echo "Procedure parameter: text\n"; 110$s = oci_parse($c, "call lob_null_proc_in(6, :b)"); 111$lob = oci_new_descriptor($c, OCI_D_LOB); 112oci_bind_by_name($s, ':b', $lob, -1, OCI_B_CLOB); 113$lob->writeTemporary('Inserted via procedure parameter'); 114$r = @oci_execute($s); 115if (!$r) { 116 $m = oci_error($s); 117 echo $m['message'], "\n"; 118} 119else { 120 $lob->close(); 121} 122 123// RETURNING INTO 124 125echo "RETURNING INTO: null\n"; 126$s = oci_parse($c, "insert into lob_null_tab values (7, empty_clob()) returning data into :b"); 127$lob = oci_new_descriptor($c, OCI_D_LOB); 128oci_bind_by_name($s, ':b', $lob, -1, OCI_B_CLOB); 129oci_execute($s, OCI_DEFAULT); // Must have OCI_DEFAULT here so locator is still valid 130$lob->save(null); 131 132echo "RETURNING INTO: ''\n"; 133$s = oci_parse($c, "insert into lob_null_tab values (8, empty_clob()) returning data into :b"); 134$lob = oci_new_descriptor($c, OCI_D_LOB); 135oci_bind_by_name($s, ':b', $lob, -1, OCI_B_CLOB); 136oci_execute($s, OCI_DEFAULT); // Must have OCI_DEFAULT here so locator is still valid 137$lob->save(''); 138 139echo "RETURNING INTO: text\n"; 140$s = oci_parse($c, "insert into lob_null_tab values (9, empty_clob()) returning data into :b"); 141$lob = oci_new_descriptor($c, OCI_D_LOB); 142oci_bind_by_name($s, ':b', $lob, -1, OCI_B_CLOB); 143oci_execute($s, OCI_DEFAULT); // Must have OCI_DEFAULT here so locator is still valid 144$lob->save('Inserted with RETURNING INTO'); 145 146echo "Fetch as string\n"; 147$s = oci_parse ($c, 'select id, data from lob_null_tab order by id'); 148oci_execute($s); 149oci_fetch_all($s, $res); 150var_dump($res); 151 152echo "\nFetch as a descriptor\n"; 153$s = oci_parse ($c, 'select id, data from lob_null_tab order by id'); 154oci_execute($s); 155while ($arr = oci_fetch_assoc($s)) { 156 if (is_object($arr['DATA'])) { 157 echo $arr['ID'] . " is an object: "; 158 $r = $arr['DATA']->load(); 159 var_dump($r); 160 } 161 else { 162 echo $arr['ID'] . " is not an object\n"; 163 } 164} 165 166echo "\nFetch via the procedure parameter\n"; 167for ($i = 1; $i <= 9; $i++) 168{ 169 $s = oci_parse ($c, "call lob_null_proc_out($i, :b)"); 170 $lob = oci_new_descriptor($c, OCI_D_LOB); 171 oci_bind_by_name($s, ':b', $lob, -1, OCI_B_CLOB); 172 oci_execute($s); 173 if (is_object($lob)) { 174 echo $i . " is an object: "; 175 $r = $lob->load(); 176 var_dump($r); 177 } 178 else { 179 echo $i . " is not an object\n"; 180 } 181} 182 183// Cleanup 184 185$s = oci_parse($c, 'drop table lob_null_tab'); 186@oci_execute($s); 187 188echo "Done\n"; 189 190?> 191--EXPECT-- 192Temporary CLOB: NULL 193Temporary CLOB: '' 194Temporary CLOB: text 195Procedure parameter: NULL 196Procedure parameter: '' 197Procedure parameter: text 198RETURNING INTO: null 199RETURNING INTO: '' 200RETURNING INTO: text 201Fetch as string 202array(2) { 203 ["ID"]=> 204 array(9) { 205 [0]=> 206 string(1) "1" 207 [1]=> 208 string(1) "2" 209 [2]=> 210 string(1) "3" 211 [3]=> 212 string(1) "4" 213 [4]=> 214 string(1) "5" 215 [5]=> 216 string(1) "6" 217 [6]=> 218 string(1) "7" 219 [7]=> 220 string(1) "8" 221 [8]=> 222 string(1) "9" 223 } 224 ["DATA"]=> 225 array(9) { 226 [0]=> 227 string(0) "" 228 [1]=> 229 string(0) "" 230 [2]=> 231 string(26) "Inserted via SQL statement" 232 [3]=> 233 string(0) "" 234 [4]=> 235 string(0) "" 236 [5]=> 237 string(32) "Inserted via procedure parameter" 238 [6]=> 239 string(0) "" 240 [7]=> 241 string(0) "" 242 [8]=> 243 string(28) "Inserted with RETURNING INTO" 244 } 245} 246 247Fetch as a descriptor 2481 is an object: string(0) "" 2492 is an object: string(0) "" 2503 is an object: string(26) "Inserted via SQL statement" 2514 is an object: string(0) "" 2525 is an object: string(0) "" 2536 is an object: string(32) "Inserted via procedure parameter" 2547 is an object: string(0) "" 2558 is an object: string(0) "" 2569 is an object: string(28) "Inserted with RETURNING INTO" 257 258Fetch via the procedure parameter 2591 is an object: string(0) "" 2602 is an object: string(0) "" 2613 is an object: string(26) "Inserted via SQL statement" 2624 is an object: string(0) "" 2635 is an object: string(0) "" 2646 is an object: string(32) "Inserted via procedure parameter" 2657 is an object: string(0) "" 2668 is an object: string(0) "" 2679 is an object: string(28) "Inserted with RETURNING INTO" 268Done 269
