This discussion is archived
3 Replies Latest reply: Jan 11, 2010 4:43 PM by cj RSS

retrieving nested table columns through a REF CURSOR in php

441594 Newbie
Currently Being Moderated
Hello.

I have been able to execute REF CURSORS returned by pl/sql functions succesfully with php. I have also been able to bind collections to the input/output of pl/sql functions/procedures.

However, what I am unable to do, is to execute a cursor returned by a pl/sql function that has one of the columns a named datatype (a simple one-dimensional nested table):

create type stab is table of varchar2(255);
/

create table lp_landing (
token varchar2(255),
text varchar2(512),
country varchar2(255),
creator varchar2(255),
is_active char(1),
css_file char(1),
autofollowing stab default stab(),
constraint lp_landing_pk primary key (token)) organization index
nested table autofollowing store as lp_landings_af_nt
(
(constraint autofollowing_pk primary key (nested_table_id,column_value)) organization index compress
);
/

function landings_usercountry (in_uname in lp_users.uname%type, in_country in lp_country.cname%type) return Landing_curType
is
ret Landing_curType;
begin
open ret for
select * --token,text,country,creator,is_active,css_file,tab2str(autofollowing) as autofollowing
from lp_landing
where country = (select country
from lp_permissions
where country = in_country and uname = in_uname);
return ret;
end landings_usercountry;
/


here is the php:

$sql = 'BEGIN :res := LP_PKG.landings_usercountry(:user, :country); END;';

$stmt = oci_parse($c, $sql);
$cursor = oci_new_cursor($c);

oci_bind_by_name($stmt,':user',$name, 32);
oci_bind_by_name($stmt,':country',$country, 32);

oci_bind_by_name($stmt,':res', $cursor, -1, OCI_B_CURSOR);

$name = "root";
$country = "Spain";

try {
   @oci_execute($stmt);
   $m = oci_error($stmt);
   
   if($m){
       throw new Exception($m['message'], $m['code']);
   }else{
       @oci_execute($cursor);
       $m = oci_error($cursor);
       
       if($m){
           throw new Exception($m['message'], $m['code']);
       }else{
           while ( $entry = oci_fetch_object($cursor) ) {
                var_dump($entry);
            }  
       }      
   }
} catch (Exception $e) {
   print_r($e);
}

With "select *" in the function, the autofollowing column (of datatype stab) fails to bind, giving an ORA-932 error. The workaround for the moment is to convert the nested table to a comma delimited string (via the tab2str function).

However, I would like to be able to tell php to accept a collection within the cursor, but I cannot figure out how to do this.

Any ideas?

thx in advance

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points