How to read CLOB return value of Stored Function in Perl

------_=_NextPart_001_01C6A0EC.D1A15B96
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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
=20
IS
=20
str CLOB;
CURSOR c1
IS
SELECT PID FROM TEMP_TABLE WHERE e_id=3Did and p_type=3Dtype;
=09
pid TEMP_TABLE.PID%TYPE;
i NUMBER;
BEGIN
str :=3D '';
i :=3D 0;
OPEN c1;

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

IF i =3D 1
THEN
str :=3D 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) =3D @_;

my $pid_str =3D '';
eval{
my $sth =3D $self->{_dbh}->prepare(q{
BEGIN
:pid_str :=3D f_load(:p1, :p2);=20
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 =3D "$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


------_=_NextPart_001_01C6A0EC.D1A15B96--

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

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)"

To:
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

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

Hi John,

Yes I tried that=20
$sth->bind_param_inout(":pid_str",\$pid_str, 2048000, {ora_type =3D>
ORA_CLOB});

and it gives me this error

ORA-01403: no data found (DBD ERROR: LOB refetch attempted for
unsupported statement type).DBD::Oracle::st execute failed: ORA-01403:
no data found (DBD ERROR: LOB refetch attempted for unsupported
statement type)=20

When I do $sth->bind_param_inout(":pid_str",\$pid_str, {ora_type =3D>
ORA_CLOB}); , it gives me this 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
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)=20

Do I need to import any latest module to get this to work?

Thanks
Prakash

-----Original Message-----
From: John Scoles [mailto:scoles@pythian.com]=20
Sent: Thursday, July 06, 2006 7:56 AM
To: Prakash Inuganti -X (pinugant - Digital-X, Inc. at Cisco);
dbi-users@perl.org
Subject: Re: How to read CLOB return value of Stored Function in Perl

Try binding like this

$sth->bind_param_inout(":pid_str",\$pid_str, {ora_type =3D> =
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)"

To:
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=3Did and p_type=3Dtype;

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

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

IF i =3D 1
THEN
str :=3D 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) =3D @_;

my $pid_str =3D '';
eval{
my $sth =3D $self->{_dbh}->prepare(q{
BEGIN
:pid_str :=3D 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 =3D "$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

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

Prakash Inuganti -X (pinugant - Digital-X, Inc. at Cisco) wrote:
>
[snip]
> my $sth =3D $self->{_dbh}->prepare(q{
> BEGIN
> :pid_str :=3D f_load(:p1, :p2);
> END; });


There's no need to use PL/SQL procedural style here. Just SELECT the
function, and Oracle will handle the CLOB part for you.

local $dbh->{LongReadLen} =3D 2048000; # biggest blob

my $sth =3D $dbh->prepare("select f_load(?,?) from dual")
|| die $dbh->errstr;
$sth->execute($id,$type)
|| die $sth->errstr;
my ($pid_str) =3D $sth->fetchrow;

Hth,
Philip

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

Thanks a lot Philip. That worked without any issues.=20


Thanks
Prakash

-----Original Message-----
From: Garrett, Philip (MAN-Corporate)
[mailto:Philip.Garrett@manheim.com]=20
Sent: Thursday, July 06, 2006 9:21 AM
To: Prakash Inuganti -X (pinugant - Digital-X, Inc. at Cisco);
dbi-users@perl.org
Subject: RE: How to read CLOB return value of Stored Function in Perl

Prakash Inuganti -X (pinugant - Digital-X, Inc. at Cisco) wrote:
>
[snip]
> my $sth =3D $self->{_dbh}->prepare(q{
> BEGIN
> :pid_str :=3D f_load(:p1, :p2);
> END; });


There's no need to use PL/SQL procedural style here. Just SELECT the
function, and Oracle will handle the CLOB part for you.

local $dbh->{LongReadLen} =3D 2048000; # biggest blob

my $sth =3D $dbh->prepare("select f_load(?,?) from dual")
|| die $dbh->errstr;
$sth->execute($id,$type)
|| die $sth->errstr;
my ($pid_str) =3D $sth->fetchrow;

Hth,
Philip