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

    retrieving nested table columns through a REF CURSOR in php

    jœœl
      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