How to read CLOB return value of Stored Function in Perl

[Prakash Inuganti -X \(pinugant - Digital-X, Inc. at Cisco\)]

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