xref: /PHP-8.1/ext/oci8/tests/bug72524.phpt (revision b5a14e6c) 
1--TEST--
2Bug #72524 (Binding null values triggers ORA-24816 error)
3--EXTENSIONS--
4oci8
5--SKIPIF--
6<?php
7$target_dbs = array('oracledb' => true, 'timesten' => true);  // test runs on these DBs
8require(__DIR__.'/skipif.inc');
9?>
10--FILE--
11<?php
12
13require(__DIR__.'/connect.inc');
14
15// Initialize
16
17$stmtarray = array(
18         "CREATE TABLE mytable (clob_col CLOB DEFAULT NULL, varchar2_col VARCHAR2(255) DEFAULT NULL)"
19);
20
21oci8_test_sql_execute($c, $stmtarray);
22
23// Run test
24
25$sql = "INSERT INTO mytable VALUES (:clob_col, :varchar2_col)";
26
27echo "Test 1 - P1 Value: NULL  P1 Length: Default  P1 Type: Default  P2 Value: NULL P2 Length: Default  P2 Type: Default\n";
28$stmt = oci_parse($c, $sql);
29
30$clob = NULL;
31$varchar2 = NULL;
32oci_bind_by_name($stmt, ':clob_col', $clob);
33oci_bind_by_name($stmt, ':varchar2_col', $varchar2);
34
35var_dump(oci_execute($stmt));
36
37echo "Test 2 - P1 Value: ''    P1 Length: Default  P1 Type: Default  P2 Value: ''   P2 Length: Default  P2 Type: Default\n";
38
39$clob = '';
40$varchar2 = '';
41oci_bind_by_name($stmt, ':clob_col', $clob);
42oci_bind_by_name($stmt, ':varchar2_col', $varchar2);
43
44var_dump(oci_execute($stmt));
45
46echo "Test 3 - P1 Value: 'abc' P1 Length: 0        P1 Type: Default  P2 Value: ''   P2 Length: 0        P2 Type: Default\n";
47$clob = 'abc';
48$varchar2 = 'abc';
49oci_bind_by_name($stmt, ':clob_col', $clob, 0);
50oci_bind_by_name($stmt, ':varchar2_col', $varchar2, 0);
51
52var_dump(oci_execute($stmt));
53
54echo "Test 4 - P1 Value: NULL  P1 Length: -1       P1 Type: SQLT_LNG P2 Value: NULL P2 Length: -1       P2 Type:Default\n";
55$clob = NULL;
56$varchar2 = NULL;
57oci_bind_by_name($stmt, ':clob_col', $clob, -1, SQLT_LNG);
58oci_bind_by_name($stmt, ':varchar2_col', $varchar2, -1, SQLT_LNG);
59
60var_dump(oci_execute($stmt));
61
62echo "Test 5 - P1 Value: NULL  P1 Length: 0        P1 Type: SQLT_LNG P2 Value: NULL P2 Length: 0        P2 Type:Default\n";
63$clob = NULL;
64$varchar2 = NULL;
65oci_bind_by_name($stmt, ':clob_col', $clob, 0, SQLT_LNG);
66oci_bind_by_name($stmt, ':varchar2_col', $varchar2, 0, SQLT_LNG);
67
68
69var_dump(oci_execute($stmt));
70
71// Cleanup
72
73$stmtarray = array(
74    "DROP TABLE mytable"
75);
76
77oci8_test_sql_execute($c, $stmtarray);
78
79?>
80--EXPECTF--
81Test 1 - P1 Value: NULL  P1 Length: Default  P1 Type: Default  P2 Value: NULL P2 Length: Default  P2 Type: Default
82bool(true)
83Test 2 - P1 Value: ''    P1 Length: Default  P1 Type: Default  P2 Value: ''   P2 Length: Default  P2 Type: Default
84bool(true)
85Test 3 - P1 Value: 'abc' P1 Length: 0        P1 Type: Default  P2 Value: ''   P2 Length: 0        P2 Type: Default
86bool(true)
87Test 4 - P1 Value: NULL  P1 Length: -1       P1 Type: SQLT_LNG P2 Value: NULL P2 Length: -1       P2 Type:Default
88
89Warning: oci_execute(): ORA-24816: %s in %s on line %d
90bool(false)
91Test 5 - P1 Value: NULL  P1 Length: 0        P1 Type: SQLT_LNG P2 Value: NULL P2 Length: 0        P2 Type:Default
92
93Warning: oci_execute(): ORA-24816: %s in %s on line %d
94bool(false)
95