PHP OCI Collections

When we use oracle type, and need to write php oci. This is example use varray and fetch data by php:

create or replace type tel_obj as
varray(10) of varchar2(20);

CREATE TABLE user_info2 (
name varchar2(30),
surname varchar2(30),
tel tel_obj);


INSERT INTO user_info2
(name, surname,tel)
VALUES
('JOHN', 'SMITH', tel_obj('1111111','Bangkok'));

INSERT INTO user_info2
(name, surname,tel)
VALUES
('JOHN', 'DOWN', tel_obj('1567751234', 'Chiangmai, Thailand'));


select * from user_info2;

NAME SURNAME TEL
---------- ---------- --------------------------------------------------------

JOHN                           SMITH    TEL_OBJ('1111111', 'Bangkok')

JOHN                           DOWN       TEL_OBJ('1567751234', 'Chiangmai, Thailand')

create or replace PROCEDURE get_tel2 (v_name in VARCHAR2, v_surname in VARCHAR2, v_tel out tel_obj) IS
BEGIN
SELECT tel INTO v_tel FROM user_info2 u WHERE u.surname = v_surname and u.name = v_name;
END;
/


Example: OCI-Collection (PHP)


$name = "JOHN";
$surname = "DOWN";
$conn = oci_connect ('username','password','DB');

$tel_coll = oci_new_collection ($conn, 'TEL_OBJ'); //Allocates new collection object

$stmt = oci_parse($conn, "begin GET_TEL2(:name, :surname, :tel); end;");

oci_bind_by_name($stmt, ":name", $name, 30);
oci_bind_by_name($stmt, ":surname", $surname, 30);
oci_bind_by_name($stmt, ":tel", $tel_coll, -1, OCI_B_NTY); //bind with collection

oci_execute($stmt,OCI_DEFAULT);

print "TEL: " . $tel_coll->getElem(0) . ", Location: " . $tel_coll->getElem(1);

oci_close($conn);
?>

Show:
TEL: 1567751234, Location: Chiangmai, Thailand
.
.
.