3 Replies Latest reply: Jan 11, 2010 6:43 PM by cj RSS

    retrieving nested table columns through a REF CURSOR in php


      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
      ret Landing_curType;
      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 {
         $m = oci_error($stmt);
             throw new Exception($m['message'], $m['code']);
             $m = oci_error($cursor);
                 throw new Exception($m['message'], $m['code']);
                 while ( $entry = oci_fetch_object($cursor) ) {
      } catch (Exception $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