Saturday, October 25, 2008

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
.
.
.

3 comments:

Anonymous said...

Thanks Surachart!!

In php.net and Oracle is impossible find an example like this.

I think it is really good, short, clear and usefull.

Greetings from Argentina!!

Unknown said...

Thanks a lot for the explanation. Is there any max limit for this collection object?Actually my requirement is like to that collection object i need to append data and then pass to procedure to execute, but eventually i am getting an error saying "No Response from Application Web Server".
Please help me on this

Surachart Opun said...

"No Response from Application Web Server".
I don't think it's collection limit. Do you know how many data you get? It might be limitd on your Web Application/cache. when you fetch lots of data.

Please check log on web cache and try to limit data on 1 page.