ID: 40186 User updated by: tony at marston-home dot demon dot co dot uk Reported By: tony at marston-home dot demon dot co dot uk -Status: Bogus +Status: Open Bug Type: OCI8 related Operating System: Windows XP PHP Version: 5.2.0 Assigned To: tony2001 New Comment:
Then I suggest you provide a working example, based on the code which I supplied, which shows how I can write to and read from a VARRAY column on a database table. The example in the manual does not do this, so it is totally useless. Both TOAD and Oracle SQL Developer can handle VARRAY columns without a hitch, so don't tell me it can't be done. Previous Comments: ------------------------------------------------------------------------ [2007-01-22 11:53:44] [EMAIL PROTECTED] >Unless you can show how my sample code can be made to >work, just like the SET datatye does with MySQL and the >ARRAY datatype does with PostgreSQL, this bug will remain open. As long as OCI API does not provide a certain way to distinguish between collections, objects and VARRAYS in OCIDefineByPos(), oci_bind_array_by_name() will remain the only method to fetch VARRAY data and this bug will remain bogus. If you know OCI API and can propose a patch - feel free to contact me. But I do not see any way to do it using the functions available. OCI API limitations are not PHP problems -> bogus. ------------------------------------------------------------------------ [2007-01-22 11:19:54] tony at marston-home dot demon dot co dot uk Your response is both unusable and impractical. The example in the manual for oci_bind_array_by_name() at http://www.php.net/manual/en/function.oci-bind-array-by-name.php shows how a VARRAY is built in memory from the contents of a database table. This is not how VARRAY fields are used in the real world. In my example I have a VARRAY field on a database table, not in memory, and I can write to this table without a problem, but I cannot read from it with gettingan error. Unless you can show how my sample code can be made to work, just like the SET datatye does with MySQL and the ARRAY datatype does with PostgreSQL, this bug will remain open. I expect, at the very least, to be able to read a record containing VARRAY fields into memory so that I can use the oci-collection methods to manipulate their contents. This would then make it similar to the way LOB fields are currently handled. I do NOT want a method which requires me to select the VARRAY and non-VARRAY fields with separate queries. Idealy I would like the contents of any VARRAY field returned to my program just like it is with TOAD or Oracle's SQL Developer, which is a string where each array is enclosed in parentheses. If that software can do it then why can't yours? ------------------------------------------------------------------------ [2007-01-22 07:37:05] [EMAIL PROTECTED] Use oci_bind_array_by_name() to fetch VARRAYs. ------------------------------------------------------------------------ [2007-01-21 13:51:22] tony at marston-home dot demon dot co dot uk Description: ------------ I am using the latest OCI8 extension from PECL. I have created a database table which contains a VARRAY user-defined type as follows: CREATE OR REPLACE TYPE t_fav_food IS VARRAY(10) OF NUMBER(2); CREATE TABLE x_person ( person_id varchar2(8) NOT NULL, first_name varchar2(20) NOT NULL, last_name varchar2(30) NOT NULL, favourite_food t_fav_food, PRIMARY KEY (person_id) ); I can write a record containing a VARRAY type, but I am unable to read it as oci_fetch_array() fails with error ORA-00932. Reproduce code: --------------- <?php $dbconn = ociLogon('tony', 'tony', '//localhost/xe') or die('unable to connect to database'); $query = "TRUNCATE TABLE x_person"; $stmt = oci_parse($dbconn, $query); $result = oci_execute($stmt, OCI_COMMIT_ON_SUCCESS) or die('truncate failed'); $query = "INSERT INTO x_person (person_id, first_name, last_name, favourite_food) VALUES ('AJM','Tony','Marston', t_fav_food(1,3,5))"; $stmt = oci_parse($dbconn, $query); $result = oci_execute($stmt, OCI_COMMIT_ON_SUCCESS) or die('insert #1 failed'); $query = "INSERT INTO x_person (person_id, first_name, last_name, favourite_food) VALUES ('FB','Fred','Bloggs', t_fav_food(2,4,6))"; $stmt = oci_parse($dbconn, $query); $result = oci_execute($stmt, OCI_COMMIT_ON_SUCCESS) or die('insert #2 failed'); $query = "SELECT * FROM x_person"; $stmt = oci_parse($dbconn, $query); $result = oci_execute($stmt) or die('select failed'); while ($row = @oci_fetch_array ($stmt, OCI_ASSOC+OCI_RETURN_NULLS)) { $array[] = array_change_key_case($row, CASE_LOWER); } // while if ($error_array = oci_error($stmt)) { echo 'Error code: ' .$error_array['code'] ."\r\n"; echo 'Error msg : ' .$error_array['message'] ."\r\n"; exit(); } // if echo 'Done'; ?> Expected result: ---------------- I expect the VARRAY column to be made available in my program so that it can be processed using the oci-collection methods, similar to the way CLOBs and BLOBs are handled. This is what I can already do with the SET type in MySQL, and the ARRAY type in PostgreSQL. Actual result: -------------- The call to oci_fetch_array() fails with ORA-00932: inconsistent datatypes: expected CHAR got ARRAY. This means that I am unable to read table that contains a VARRAY column. ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=40186&edit=1
