Hi Tim,
Following is my perl file:
-------------------------
#!/usr/bin/perl
require "config.pl";
my $conn = DBI->connect($DATA_SOURCE, $USERNAME, $PASSWORD) or die
"$DBI::errstr";
my $query = "SELECT
nvl(to_char(topup_datetime,'yyyy-mm-dd'),to_char(pt_val_datetime,'yyyy-mm-dd
')),pt_status,interface_type,spe_id,count(*),sum(topup_amount),network_id,fa
ilure_stage,pami_id,pretups_card_group.GET_CARD_GROUP(network_id,pami_id,msi
sdn,pe_id,spe_id,topup_amount,nvl(topup_datetime,pt_val_datetime)),sum(decod
e((MSISDN_BALAFTER - MSISDN_BALBEFORE), NULL, 0, (MSISDN_BALAFTER -
MSISDN_BALBEFORE))), TOPUP_PAYMTMODE FROM validate_topup where stat_flag =
'U' group by
nvl(to_char(topup_datetime,'yyyy-mm-dd'),to_char(pt_val_datetime,'yyyy-mm-dd
')),pt_status,failure_stage,interface_type,spe_id,network_id,pami_id,pretups
_card_group.GET_CARD_GROUP(network_id,pami_id,msisdn,pe_id,spe_id,topup_amou
nt,nvl(topup_datetime,pt_val_datetime)),TOPUP_PAYMTMODE order by
1,2,4,5,3,8,9";
my $pre = $conn->prepare($query);
my $result = $pre->execute();
while(@arr = $pre->fetchrow_array())
{
my $date = $arr[0];
my $status = $arr[1];
my $interface = $arr[2];
my $spe_id = $arr[3];
my $count = $arr[4];
my $sum = $arr[5];
my $network_id = $arr[6];
my $failure_stage= $arr[7];
my $pami = $arr[8];
my $cgrp = $arr[9];
my $sum_msisdn = $arr[10];
my $mode = $arr[11];
}
Where,
pretups_card_group = Stored procedure of ORACLE
and GET_CARD_GROUP = function in the stored procedure
Definition of the function:
FUNCTION GET_CARD_GROUP(V_NETWORK_ID IN NUMBER, V_PAMI_ID IN NUMBER,
V_MSISDN IN NUMBER, V_PE_ID IN NUMBER, V_SPE_ID IN NUMBER, V_AMOUNT IN
NUMBER, V_CURR_DATE IN DATE) RETURN VARCHAR2 IS
V_CGRP_SET_ID PE_SPE_NET_CGRP.CGRP_SET_ID%TYPE;
V_CGRP CARD_GROUP_DETAILS.PAMI_CGRP%TYPE;
V_NEW_AMOUNT CARD_GROUP_DETAILS.AMOUNT%TYPE; --this is NUMBER(12)
in
oracle
V_RET VARCHAR2(256);
--V_NEW_AMOUNT VARCHAR2(256);
I have tried by changing the V_NEW_AMOUNT variable's datatype to
VARCHAR2(256) but still no luck. :-(
Thanks & Regards,
Rachita
-----Original Message-----
From: Tim Bunce [mailto:[EMAIL PROTECTED]
Sent: Friday, October 29, 2004 3:13 PM
To: Rachita
Cc: Tim Bunce; Reidy, Ron; [EMAIL PROTECTED]
Subject: Re: Perl Oracle incompatibility
It would help if you could show us a small piece of code that
demonstrates the problem. At least the SQL + bind_param calls etc.
http://www.catb.org/~esr/faqs/smart-questions.html
Tim.
On Fri, Oct 29, 2004 at 10:37:21AM +0530, Rachita wrote:
> Hi Tim / Reidy,
>
> Thanks for the info.
>
> The same procedure is running perfectly fine when I run it at SQL Plus
> prompt. Moreover i have not done any changes except to upgrade my Oracle
> from 8.1.7 to Oracle 9.2.0.1.0
>
> I have 2 servers. On the production server, I have just upgraded the
> database and nothing else. So there are libclntsh.so.8.0
> and libclntsh.so files present.
>
> But on the other server i.e. Test server I have upgraded the database and
> then rebuilded perl. So I have libclntsh.so.9.0 and libclntsh.so files
> present
>
> In either case it is not working. I have not made any changes in the
> procedure at all and it was working fine before I upgraded the databse.
>
> Plz. help as this is very urgent and my clients are waiting for the
reports.
>
> With regards,
> Rachita
>
> -----Original Message-----
> From: Tim Bunce [mailto:[EMAIL PROTECTED]
> Sent: Friday, October 29, 2004 2:51 AM
> To: Reidy, Ron
> Cc: Rachita; [EMAIL PROTECTED]
> Subject: Re: Perl Oracle incompatibility
>
>
> On Thu, Oct 28, 2004 at 02:28:52PM -0600, Reidy, Ron wrote:
> > This is not a Perl issue. There is an issue with PL/SQL proc - maybe
some
> variable is too small for the data? Or did you change character sets? Or
> ...?
>
> Or forget to upgrade DBD::Oracle...
>
> Tim.
>
> > -----------------
> > Ron Reidy
> > Lead DBA
> > Array BioPharma, Inc.
> >
> >
> > -----Original Message-----
> > From: Rachita [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, October 28, 2004 10:48 AM
> > To: [EMAIL PROTECTED]
> > Subject: FW: Perl Oracle incompatibility
> >
> >
> > Hi dbi users,
> >
> > I have been struggling with this for quite some time, going thru various
> > articles, faqs and mailing lists, but still not able to go thru.
> >
> > I have perl, v5.6.0 built for i386-linux and DBI 1.20 and DBD::ORACLE
1.12
> > running with Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production.
> >
> > I have a perl file for generating summary reports by interacting with
the
> > Oracle database. All the database queries work fine till it encounters
the
> > query where a stored procedure is being called. At this poing I get the
> > following error:
> >
> > DBD::Oracle::st execute failed: ORA-06502: PL/SQL: numeric or value
error:
> > character string buffer too small
> > DBD ERROR: OCIStmtExecute
> > DBD::Oracle::st fetchrow_array failed: ERROR no statement executing
> (perhaps
> > you need to call execute first)
> >
> > It was working fine with oracle 8.1.6 and 8.1.7 I guess some
configuration
> > settings have to be done for my perl to execute the stored procedure
> written
> > in oracle.
> >
> > Thanks in advance. Awaiting ur reply.
> >
> > Regards,
> > Rachita
> >
> >
> > This electronic message transmission is a PRIVATE communication which
> contains
> > information which may be confidential or privileged. The information is
> intended
> > to be for the use of the individual or entity named above. If you are
not
> the
> > intended recipient, please be aware that any disclosure, copying,
> distribution
> > or use of the contents of this information is prohibited. Please notify
> the
> > sender of the delivery error by replying to this message, or notify us
by
> > telephone (877-633-2436, ext. 0), and then delete it from your system.
> >
> >
>
