Re: How to read CLOB return value of Stored Function in Perl

[John Scoles]

Try binding like this

   $sth->bind_param_inout(":pid_str",\$pid_str, {ora_type => ORA_CLOB});

read the section in the DBD::Oracle pod on Handling LOBs  as well

cheers
John Scoles
----- Original Message ----- 
From: "Prakash Inuganti -X (pinugant - Digital-X, Inc. at Cisco)"
<[EMAIL PROTECTED]>
To: <dbi-users@perl.org>
Sent: Thursday, July 06, 2006 7:10 AM
Subject: How to read CLOB return value of Stored Function in Perl


Hi,

I am trying to read a CLOB value in Perl that is returned from an Oracle
function. This CLOB is created by appending VARCHAR2 values from a
column. But my Perl code gives me the following error.

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute).DBD::Oracle::st execute
failed: ORA-06502: PL/SQL: numeric or value error

The function is
CREATE OR REPLACE FUNCTION f_load(id varchar2 ,type varchar2)

   RETURN CLOB

IS

   str   CLOB;
   CURSOR c1
   IS
      SELECT  PID FROM TEMP_TABLE WHERE e_id=id  and p_type=type;

   pid     TEMP_TABLE.PID%TYPE;
   i       NUMBER;
BEGIN
   str := '';
   i := 0;
   OPEN c1;

   LOOP
      FETCH c1 INTO pid;
   i := i + 1;
      EXIT WHEN c1%NOTFOUND;

      IF i = 1
      THEN
         str := pid;
      ELSE
         dbms_lob.append(str, ',' || pid);
      END IF;


   END LOOP;

   RETURN pid_str;
END f_load;
/


Perl code is

sub getPids {
  my($self, $id, $type) = @_;

    my $pid_str = '';
      eval{
          my $sth = $self->{_dbh}->prepare(q{
                      BEGIN
                        :pid_str := f_load(:p1, :p2);
                      END; });
          $sth->bind_param(":p1", $id);
          $sth->bind_param(":p2", $type);
          $sth->bind_param_inout(":pid_str",\$pid_str, 2048000);

          $sth->execute();
      };

      if($@) {
        my $err = "$DBI::errstr.$@";
        return $err;
      }

      return $pid_str;
}

I get ORA-06502: PL/SQL: numeric or value error in $err above. How do I
solve this problem? All I want is the Appended string from the function.
I had return value as LONG but when the string becomes large, it throws
the same error so I am using CLOB but the error remains same. Any
help/pointers to solve this is appreciated. Thanks in advance for your
help.

Thanks
Prakash