RE: (Fwd) DBD:: Oracle Problems

• Previous message: Tim Bunce: "(Fwd) DBD:: Oracle Problems"
• In reply to: Tim Bunce: "(Fwd) DBD:: Oracle Problems"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

To: <dbi-users@perl.org>
Date: Fri, 19 Nov 2004 10:33:31 -0000

The <*> is an indicator as to the location of the possible problem. This is
useful in large statements as it tells you which part the oracle error
refers to.

If you want to see the data which is being passed to the stored proc then
you can use DBI->trace
(http://www.google.co.uk/search?hl=en&safe=off&q=DBI-%3Etrace&meta=) to let
you know.

The error is being thrown inside the pl/sql procedure itself not the DBI!

I would

1.) Use DBI-> trace to find out exactly what you are passing to the
procedure
2.) Use sqlplus to call the procedure to replicate the problem

If sqlplus gives the same results then it is likely that you have a
character set issue (i.e. you are calling your function with data in a
difference character set to the instance it is running on or you NLS_LANG is
wrong).

If the results differ then I would try to create a simple and self contained
test case and post the pl/sql and DBI code along with a DBI level 9 trace to
the list.

HTH

Andy

-----Original Message-----
From: Tim Bunce [mailto:Tim.Bunce@pobox.com]
Sent: 18 November 2004 23:26
To: dbi-users@perl.org
Cc: Paul.Rowe@gartner.com
Subject: (Fwd) DBD:: Oracle Problems

----- Forwarded message from "Rowe,Paul" <Paul.Rowe@gartner.com> -----

Delivered-To: tim.bunce@pobox.com
X-SPF-Guess: pass (seems reasonable for Paul.Rowe@gartner.com to mail
through 207.140.148.126)
X-Pobox-Antispam: Bad HELO hostname returned DENY: no A or MX records found
for parakeet.ent.gartner.com
Subject: DBD:: Oracle Problems
Date: Thu, 18 Nov 2004 18:19:41 -0500
From: "Rowe,Paul" <Paul.Rowe@gartner.com>
To: <Tim.Bunce@pobox.com>
Cc: "Colon,Anthony" <Anthony.Colon@gartner.com>
X-OriginalArrivalTime: 18 Nov 2004 23:19:46.0178 (UTC)
FILETIME=[179F3E20:01C4CDC5]

   Tim, we just recently upgraded to perl 5.8.2 and Oracle:DBD 9.2. We
moved over our production scripts
   and
   are receiving the following error when we execute any stored procedures.
The code is attached as well.

   Here is the procedure:

   sub kmap_add_resource {
     my $res_id = shift;
     my $sth = $kmap_dbh->prepare("BEGIN link_que_mod_pkg.add_resource(:p1,
:p2, :p3, :p4, :p5, :p6, :p7);
   END;") ;
       $sth->bind_param(":p1", $res_id);
       $sth->bind_param(":p2", undef);
       $sth->bind_param(":p3", "N");
       $sth->bind_param(":p4", undef);
       $sth->bind_param(":p5", "TEAMSITE");
       my $return1 = 0;
       my $return2 = "";
       $sth->bind_param_inout(":p6", \$return1, 100);
       $sth->bind_param_inout(":p7", \$return2, 100);
       #$sth->execute() || die new AGG_Exception(-message => '2.3', -details
=> 'BEGIN
   link_que_mod_pkg.add_resource(:p1, :p2, :p3, :p4, :p5, :p6, :p7); END;:
$DBI::errstr');

       $sth->execute();
   }

   The error we are getting is as follows:

   Execution of stored procedure failed: ORA-06550: line 5, column 41:
   PLS-00561: character set mismatch on value for parameter 'P_RES_ID'
   ORA-06550: line 5, column 11:
   PL/SQL: Statement ignored (DBD ERROR: error possibly near <*> indicator
at char 98 in '
   link_que_mod_pkg.add_resource(<*>:p1, :p2, :p3, :p4, :p5, :p6, :p7);

   :p1 references P_RES_ID, which I believe is getting filled with the
garbage <*>.

   We have no idea how to get rid of the <*> or why it is even being
inserted. We've tried several
   different
   ways to execute this to no avail. Any help at this point would be
greatly appreciated. We did see in
   the
   dbd/dbi mail group there was a possible error that may pertain to this,
but it didn;t exactly apply.

   Thank You,

   Paul Rowe
   Sr. Engineer
   Gartner
   Strategic Technology Group (STG)
   Tel: +1 716 633 0720 x217
   Cell: +1 203 918 2847
   Fax: +1 716 633 9506
   E-mail: paul.rowe@gartner.com
   www.gartner.com
   80 Holtz Drive
   Cheektowaga, NY 14225-1470

----- End forwarded message -----

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________

__________________________
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. The contents are confidential and may be privileged.
Any views or opinions expressed are those of the sender and may not reflect the views or opinions of the company and the company accepts no liability in respect thereof.
If you have received this email in error please notify postmaster@abibuildingdata.com immediately.

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________

• Previous message: Tim Bunce: "(Fwd) DBD:: Oracle Problems"
• In reply to: Tim Bunce: "(Fwd) DBD:: Oracle Problems"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]