Sven,
Of course, you were right. Never occurred to me that the \n would need
to be explicitly removed. However, I had to remove the HEXTORAW()
function in the preparation statement as it appears my code was trying
to double convert. Here is what finally worked:
$osaq = $dbh->prepare(q{
BEGIN
OSAQ_ENQUEUE(:ImmPacket,
:ImmPacketLen,
:ImmQSizeLimit,
:BufferedOpt,
:ImmEnqTimeMillis
) ;
END;
});
if ( ! defined($osaq) ) {
die "Prepare error: ",$dbh->errstr, "\n" ;
}
.
.
.
my @pkt = unpack("C*", $packetCard) ;
# $packetCard is binary here; convert back to hex
my $pkt = Bin2Hex_Packet([EMAIL PROTECTED]);
# $pkt is $packetCard converted to HEX string (414 btytes)
$osaq->bind_param(":ImmPacket", $pkt,
{ ORA_TYPE => ORA_RAW }) ;
$osaq->bind_param(':ImmPacketLen', length($pkt)/2) ;
# bind size = $pkt / 2
$osaq->bind_param(":ImmQSizeLimit", chomp($OSAQ_SL));
# OSAQ_SIZE_LIMIT
$osaq->bind_param(":BufferedOpt", chomp($OSAQ_Buf)) ;
# OSAQ_Buffering
$osaq->bind_param_inout(":ImmEnqTimeMillis", \$millis,
# immEnqMillis
, SIZMILI) ;
$osaq->execute ;
# packets sent via Oracle Streams
My sincere thanks to everyone who tried to help.
Joe Orcino
-----Original Message-----
From: Sven Miller [mailto:[EMAIL PROTECTED]
Sent: Monday, August 11, 2008 12:08 PM
To: Orcino, Joe
Subject: Re: Bug in DBD::Oracle v 1.21 ?
This part looks suspicious to me:
:immqsizelimit="5000
", :bufferedopt="0
", :immpacketlen=414,
It looks like $OSAQ_SL might contain the string "5000\n" and $OSAQ_Buf
might contain "0\n". Oracle would have a problem converting these
strings to numbers (because of the trailing newline), which could
explain the Oracle error you're getting: "ORA-06502: PL/SQL: numeric or
value error: character to number conversion error".
On Mon, Aug 11, 2008 at 1:45 PM, Orcino, Joe <[EMAIL PROTECTED]> wrote:
> Thanks very much for your response. However my code now looks like
> this:
>
> Prepare:
>
> #
> # prepare call to the PL/SQL stored procedure OSAQ_ENQUEUE
> #
> $osaq = $dbh->prepare(q{
> BEGIN
> OSAQ_ENQUEUE(HEXTORAW(:ImmPacket),
> :ImmPacketLen,
> :ImmQSizeLimit,
> :BufferedOpt,
> :ImmEnqTimeMillis
> ) ;
> END;
> });
> if ( ! defined($osaq) ) {
> die "Prepare error: ",$dbh->errstr, "\n" ;
> }
> .
> .
> .
> my @pkt = unpack("C*", $packetCard) ; # $packetCard is binary
> here, converted to array of values
> my $pkt = Bin2Hex_Packet([EMAIL PROTECTED]); # $pkt is $packetCard
> converted back to HEX string (414 btytes)
> print "$pkt\n" ;
> if ( defined $opts{G} ) {
> # OSAQ?
> print TST1 $packetCard ; # ===========>
> check validity with od -x tst1.bin
> $osaq->bind_param(":ImmPacket", $pkt,
> { ORA_TYPE => ORA_RAW }) ; #
> ============================> should my input to the bind parameter
> be 'HEX' or 'RAW' here?
> $osaq->bind_param(':ImmPacketLen', length($pkt)) ; # bind
> size
> printf ("length = %d\n", length($pkt)) ;
> $osaq->bind_param(":ImmQSizeLimit", $OSAQ_SL); #
> OSAQ_SIZE_LIMIT
> $osaq->bind_param(":BufferedOpt", $OSAQ_Buf) ; #
> OSAQ_Buffering
> $osaq->bind_param_inout(":ImmEnqTimeMillis", \$millis, #
> immEnqMillis
> , SIZMILI) ;
> $osaq->execute ;
> # packets sent via Oracle Streams Advance Queue
>
>
> But when I execute I get this mess:
>
> timer begins ...
> 0100BB0000669064400500032750000000000001000000000063EBBDAB090B481794B2
> 84
> 9B0CA2D85EF908F8145FAEF2043B308CE8A256E8B7E1ABE1198D84A519BEDD3B511075
> B2
> 17AD243B5D9B9A91BD837BB0158EDB42A3C372A39DC2B4FCF1BF5C21C25458714ECD71
> 64
> 0000000000000000000000000000000000000000000000000000000000000000000000
> 00
>
000000000000000000000000000000000000000000000000000000000000000000000000
> 0000000000000210D75363AC19659F00000CCB0000000148A0779D <==== this is
> my print statement of the hex packet ....
> length = 414
> DBD::Oracle::st execute failed: ORA-06502: PL/SQL: numeric or value
> error: character to number conversion error
> ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute) [for Statement "
> BEGIN
> OSAQ_ENQUEUE(HEXTORAW(:ImmPacket), <======= per
> your suggestion ....
> :ImmPacketLen,
> :ImmQSizeLimit,
> :BufferedOpt,
> :ImmEnqTimeMillis
> ) ;
> END;
> " with ParamValues: :immenqtimemillis=undef,
> :immqsizelimit="5000 ", :bufferedopt="0 ", :immpacketlen=414,
> :immpacket='0100BB0000669064400500032750000000000001000000000063EBBDAB
> 09
> 0B481794B2849B0CA2D85EF908F8145FAEF2043B308CE8A256E8B7E1ABE1198D84A519
> BE
> DD3B511075B217AD243B5D9B9A91BD837BB0158EDB42A3C372A39DC2B4FCF1BF5C21C2
> 54
> 58714ECD71640000000000000000000000000000000000000000000000000000000000
> 00
> 0000000000000000000000000000000000000000000000000000000000000000000000
> 00 0000000000000000000000000210D75363AC19659F00000...'] at
> ./immload.pl line 183.
>
>
> Am I doing something really boneheaded here or what?
>
>
> Joe Orcino
>
> -----Original Message-----
> From: Sven Miller [mailto:[EMAIL PROTECTED]
> Sent: Thursday, August 07, 2008 6:20 PM
> To: Orcino, Joe
> Subject: Re: Bug in DBD::Oracle v 1.21 ?
>
> Yes, HEXTORAW(:ImmPacket).
>
> On Thu, Aug 7, 2008 at 6:17 PM, Orcino, Joe <[EMAIL PROTECTED]> wrote:
>> Ok, I have added the commas, but perhaps I don't understand how you
>> mean to call HEXTORAW(). Should it be like HEXTORAW(:ImmPacket), or
> what?
>>
>> Thanks,
>>
>> Joe
>>
>> -----Original Message-----
>> From: Sven Miller [mailto:[EMAIL PROTECTED]
>> Sent: Thursday, August 07, 2008 7:10 AM
>> To: Orcino, Joe
>> Cc: dbi-users@perl.org
>> Subject: Re: Bug in DBD::Oracle v 1.21 ?
>>
>> Unless this is not an exact copy, there appears to be a bug in your
>> code. You are missing a comma between "$packetCard" and "{ ORA_TYPE
>> => ORA_RAW }", as well as in a few other places.
>>
>> Adding that comma may fix it. If not, I am not seeing any difference
>> in DBD::Oracle's handling between ORA_RAW and the default (VARCHAR2,
>> I
>
>> think). This means you may want to encode your string as pairs of
>> hex
>
>> digits (as you have done in a later post) and maybe explicitly call
>> HEXTORAW() in your call to OSAQ_ENQUEUE()
>>
>> On Tue, Aug 5, 2008 at 7:53 PM, Orcino, Joe <[EMAIL PROTECTED]> wrote:
>>> I am working with:
>>> Oracle relase 10.2.0.1.0,
>>> perl v 5.8.0,
>>> DBD::Oracle v1.21, and
>>> DBI 1.602. running on
>>> Red Hat Linux 2.4.21.
>>>
>>> I am trying to write to an Oracle Stored procedure defined as:
>>>
>>> CREATE OR REPLACE PROCEDURE JORCINO.osaq_enqueue(
>>> ImmPacket_IN RAW,
>>> ImmPacketLen_IN NUMBER,
>>> ImmQSizeLimit_IN NUMBER,
>>> BufferedOpt_IN NUMBER,
>>> ImmEnqTimeMillis_OUT OUT NUMBER) AS ....
>>>
>>>
>>> In perl ...
>>>
>>> #
>>> # declare the PL/SQL stored procedure OSAQ_ENQUEUE
>>> #
>>> $osaq = $dbh->prepare(q{
>>> BEGIN
>>>
>> OSAQ_ENQUEUE(:ImmPacket,:ImmPacketLen,:ImmQSizeLimit,
>>> :BufferedOpt,:ImmEnqTimeMillis) ;
>>> END;
>>> });
>>> if ( ! defined($osaq) ) {
>>> die "Prepare error: ",$dbh->errstr, "\n" ;
>>> }
>>> .
>>> .
>>> .
>>> $osaq->bind_param(':ImmPacket', $packetCard
>>> { ORA_TYPE => ORA_RAW }) ;
>>> $osaq->bind_param(':ImmPacketLen', length($packetCard))
>>> ;
>
>>> # bind size
>>> $osaq->bind_param(":ImmQSizeLimit", $OSAQ_SL #
>>> OSAQ_SIZE_LIMIT
>>> {ORA_TYPE => ORA_NUMBER});
>>> $osaq->bind_param(":BufferedOpt", $OSAQ_Buf #
>>> OSAQ_Buffered
>>> {ORA_TYPE => ORA_NUMBER}) ;
>>> $osaq->bind_param_inout(":ImmEnqTimeMillis", \$millis #
>>> immEnqMillis
>>> { ORA_TYPE => ORA_LONG }, SIZMILI) ;
>>> $osaq->execute ;
>>> # packets sent via Oracle Streams Advance Queue
>>>
>>> For each execution (in a loop), I get no errors and the queue is
>>> incremented (indicating receipt of a record), but in the database,
>>> my
>
>>> binary :ImmPacket field is empty in the queue!
>>> I have made sure that the variable I am passing ($packetCard) does
>>> contain 207 bytes of binary information. Our dequeue procedure is,
>>> of
>>
>>> course, not working because the user data has nothing in it except
>>> the
>>
>>> length field. The Enqueue procedure is known to work via an
>>> application
>>> C++ process and the dequeue java process is also working. The Perl
>>> program is a test tool I need to get working for QC load test
>> purposes.
>>>
>>> I have searched all over the internet for someone who is doing
>>> something similar and I see no method better than what I am using.
>>> Can anyone see something obvious that I am doing wrong? Is this a
>>> known bug with DBD::Oracle?
>>>
>>> Any help would be appreciated.
>>>
>>> Thanks,
>>>
>>> Joe Orcino
>>>
>>> ********************************************************************
>>> *
>>> *
>>> **************** This e-mail is confidential, the property of NDS
>>> Ltd
>
>>> and intended for the addressee only. Any dissemination, copying or
>> distribution of this message or any attachments by anyone other than
>> the intended recipient is strictly prohibited. If you have received
>> this message in error, please immediately notify the
>> [EMAIL PROTECTED] and destroy the original message. Messages sent
>> to
>
>> and from NDS may be monitored. NDS cannot guarantee any message
>> delivery method is secure or error-free. Information could be
>> intercepted, corrupted, lost, destroyed, arrive late or incomplete,
>> or
>
>> contain viruses. We do not accept responsibility for any errors or
>> omissions in this message and/or attachment that arise as a result of
>> transmission. You should carry out your own virus checks before
>> opening any attachment. Any views or opinions presented are solely
>> those of the author and do not necessarily represent those of NDS.
>>>
>>> To protect the environment please do not print this e-mail unless
>> necessary.
>>>
>>> NDS Limited Registered office: One Heathrow Boulevard, 286 Bath
>>> Road,
>> West Drayton, Middlesex, UB7 0DQ, United Kingdom. A company
registered
>> in England and Wales Registered no. 3080780 VAT no. GB 603 8808
> 40-00
>>> ********************************************************************
>>> *
>>> *
>>> ****************
>>>
>> *********************************************************************
>> *
>> **************** This e-mail is confidential, the property of NDS Ltd
>> and intended for the addressee only. Any dissemination, copying or
> distribution of this message or any attachments by anyone other than
> the intended recipient is strictly prohibited. If you have received
> this message in error, please immediately notify the
> [EMAIL PROTECTED] and destroy the original message. Messages sent to
> and from NDS may be monitored. NDS cannot guarantee any message
> delivery method is secure or error-free. Information could be
> intercepted, corrupted, lost, destroyed, arrive late or incomplete, or
> contain viruses. We do not accept responsibility for any errors or
> omissions in this message and/or attachment that arise as a result of
> transmission. You should carry out your own virus checks before
> opening any attachment. Any views or opinions presented are solely
> those of the author and do not necessarily represent those of NDS.
>>
>> To protect the environment please do not print this e-mail unless
> necessary.
>>
>> NDS Limited Registered office: One Heathrow Boulevard, 286 Bath Road,
> West Drayton, Middlesex, UB7 0DQ, United Kingdom. A company registered
> in England and Wales Registered no. 3080780 VAT no. GB 603 8808
40-00
>> *********************************************************************
>> *
>> ****************
>>
> **********************************************************************
> **************** This e-mail is confidential, the property of NDS Ltd
> and intended for the addressee only. Any dissemination, copying or
distribution of this message or any attachments by anyone other than the
intended recipient is strictly prohibited. If you have received this
message in error, please immediately notify the [EMAIL PROTECTED] and
destroy the original message. Messages sent to and from NDS may be
monitored. NDS cannot guarantee any message delivery method is secure
or error-free. Information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. We do not
accept responsibility for any errors or omissions in this message and/or
attachment that arise as a result of transmission. You should carry out
your own virus checks before opening any attachment. Any views or
opinions presented are solely those of the author and do not necessarily
represent those of NDS.
>
> To protect the environment please do not print this e-mail unless
necessary.
>
> NDS Limited Registered office: One Heathrow Boulevard, 286 Bath Road,
West Drayton, Middlesex, UB7 0DQ, United Kingdom. A company registered
in England and Wales Registered no. 3080780 VAT no. GB 603 8808 40-00
> **********************************************************************
> ****************
>
**************************************************************************************
This e-mail is confidential, the property of NDS Ltd and intended for the
addressee only. Any dissemination, copying or distribution of this message or
any attachments by anyone other than the intended recipient is strictly
prohibited. If you have received this message in error, please immediately
notify the [EMAIL PROTECTED] and destroy the original message. Messages sent
to and from NDS may be monitored. NDS cannot guarantee any message delivery
method is secure or error-free. Information could be intercepted, corrupted,
lost, destroyed, arrive late or incomplete, or contain viruses. We do not
accept responsibility for any errors or omissions in this message and/or
attachment that arise as a result of transmission. You should carry out your
own virus checks before opening any attachment. Any views or opinions
presented are solely those of the author and do not necessarily represent those
of NDS.
To protect the environment please do not print this e-mail unless necessary.
NDS Limited Registered office: One Heathrow Boulevard, 286 Bath Road, West
Drayton, Middlesex, UB7 0DQ, United Kingdom. A company registered in England
and Wales Registered no. 3080780 VAT no. GB 603 8808 40-00
**************************************************************************************
