Skip to Main Content

Passing oracle object from php to oracle

Andrew HallMar 7 2011 — edited Jun 1 2011
Hi all

I'm trying to work out whether it is possible to :

1. Generate a table of oracle objects in PHP
2. Pass that table of oracle objects to Oracle.

This is as far as I've got :

Oracle Code :
create table x ( a int , b int );

create type ty_x as object ( a int , b int )
/

create type tty_x as table of ty_x
/
That defines the Oracle portion - what I am trying to do with the PHP below is create a variable of type tty_x, append a variable of type ty_x to it, and then pass my variable of type tty_x to Oracle :

PHP Code
<?php

// Connects to the XE service (i.e. database) on the "localhost" machine
$conn = oci_connect('system', 'hello', 'localhost/XE');
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}

$stid = oci_parse($conn, 'SELECT * FROM x');
oci_execute($stid);

echo "<table border='1'>\n";
while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
    echo "<tr>\n";
    foreach ($row as $item) {
        echo "    <td>" . ($item !== null ? htmlentities($item, ENT_QUOTES) : "&nbsp;") . "</td>\n";
    }
    echo "</tr>\n";
}
echo "</table>\n";

$ts = oci_parse($conn , 'insert into x select * from table ( cast ( :d as tty_x ) )');

$tyx = oci_new_collection($conn, 'TY_X');

//do something to populate the elements of TY_X here ... 

$ttyx = oci_new_collection($conn, 'TTY_X');

$ttyx->append ( $tyx );

oci_bind_by_name ($ts, ':d', $ttyx, -1, OCI_B_NTY);

oci_execute($ts);

oci_commit($conn);

?>
Successive executions of the PHP do insert records into the table x, but what I am struggling with now is populating the attributes of type ty_x ,so that data appears in my table.

Does anybody know if this is possible?

Thanks,

Andrew.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked due to inactivity on Jun 29 2011
Added on Mar 7 2011
1 comment
892 views