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

Relevant Pages

  • RE: How to read CLOB return value of Stored Function in Perl
    ... no data found (DBD ERROR: ... How to read CLOB return value of Stored Function in Perl ... pid TEMP_TABLE.PID%TYPE; ...
    (perl.dbi.users)
  • Problem with DBD::Oracle ora_auto_lob not working
    ... I am in the process of moving some select SQL which was in Perl into functions and procedures in an oracle package which return a cursor the perl can read i.e., to hide the SQL from outside the database. ... the clob is retrieved as data and not as a lob locator. ... The cursor is magicked into a DBI statement handle by DBD::Oracle but fetching on it returns a lob locator and not the data. ...
    (perl.dbi.users)
  • Re: 10g field varchar size
    ... Oracle the max varchar2 size was 2000 characters otherwise you had to ... CLOB will likely do what you want and can be many GB. ... INSERT INTO demo (clobcol) VALUES; ...
    (comp.databases.oracle.server)
  • Re: Fun with UTF-8 and Oracle
    ... when you used csform SQLCS_NCHAR and the database column is CHAR, VARCHAR2 or CLOB. ... perhaps we need to rework CSFORM_IMPLIES_UTF8 so it doesn't return true for csform SQLCS_NCHAR if the columns are CHAR, VARCHAR2 or CLOB? ...
    (perl.dbi.users)
  • Re: strip HTML tags from fields?
    ... I'm trying to take some HTML fields (varchar2) and strip out the HTML ... The Policy_Filter seems to want to use a Blob, ... POLICY_FILTER accepts VARCHAR2, CLOB, BLOB or BFILE as input. ...
    (comp.databases.oracle.misc)