Re: Problem calling External Procedure with REFCURSOR

On 05/04/2005 02:40 PM, Denesa K Shaw said:

Any Ideas on why I'm getting this error? It appears not to like my date
that I'm passing to it. I printed the date to a log and it looks ok, any
ideas?


Error:
DBI::st=HASH(0x20391b00)->bind_param(...): attribute parameter
'TO_DATE('01/02/200','mm/dd/yyyy hh24:mi:ss')'

> is not a hash ref at CallTest_firstsp.pl line 90.

The error is exactly what it says it is. You put a string in a subroutine parameter where a hash refrence is expected.

My Code:
#!/usr/bin/perl -w

use lib "/usr/xxx/xxx/xxx/scripts";
use TMH;
use DBD::Oracle qw(:ora_types);

##################################################################################
# Declarations Needed for Scripts
##################################################################################

use constant SCRIPT_ERROR => "RELOAD_HISTORICAL_DATA";
$RELOAD_FILE_NM = "";
$LAST_ATTEMPT_TS = "";
$RELOAD_FILE_TS =  "";
$INB_OUTB_IN = "";
$LOGICAL_NM = "";
$starttimeStamp= "'01/01/2005 08:49:00'";
$endtimeStamp= "'01/02/2005 08:49:00'";
$In_Start_File_Ts = "TO_DATE(" . $starttimeStamp . ",
'mm/dd/yyyy hh24:mi:ss')";
$In_End_File_Ts = "TO_DATE(" . $endtimeStamp . ",'mm/dd/yyyy hh24:mi:ss')";
.. . . 
$sql = qq(
BEGIN
     PACKAGE.PROCEDURE(:p1,:cursor_name);
END;
);

my $func = $dbh->prepare($sql);
$func->bind_param(":p1",$In_Start_File_Ts,$In_End_File_Ts);
$func->bind_param_inout(":cursor_name", \$cursor_name, 0,{
ora_type=>ORA_RSET } );
$func->execute;

Placeholders (AKA bind variables) take __VALUES__, not substitution strings. Also you can only provide one value for each placeholder. If the procedure you are calling takes a start datetime and an end datetime, it needs two parameters for that.

Try this (requires a procedure that takes 2 date arguments and returns one cursor):

  my $sql = qq(
  BEGIN
    PACKAGE.PROCEDURE( TO_DATE( :start, :fmt ), TO_DATE( :end, :fmt ),
      :cursor );
  END;
  );

  my $func = $dbh -> prepare( $sql );
  $func -> bind_param( ":start", $In_Start_File_Ts );
  $func -> bind_param( ":end",   $In_End_File_Ts );
  $func -> bind_param( ":fmt",   "mm/dd/yyyy hh24:mi:ss" );
  $func -> bind_param_inout( ":cursor", \$cursor, 0,
    { ora_type=>ORA_RSET } );

--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
.

Relevant Pages

  • Re: UNSUBSCRIBING
    ... Eventually the list admin ... I think it's been three months since the last such request. ... Give a hobbit a ring and he eats fish for an age. ...
    (perl.dbi.users)
  • Re: DBD-Oracle question for Win2000
    ... provides pre-compiled packages to install using PPM. ... Give a hobbit a ring and he eats fish for an age. ...
    (perl.dbi.users)
  • Re: Nested query problem
    ... > efficient to write the WHERE clause conditions as most restricting ... > restrictive conditions first in the WHERE - is that right? ... Give a hobbit a ring and he eats fish for an age. ...
    (perl.dbi.users)
  • Re: MySQL UPDATE Question...
    ... > clue what language you are speaking. ... > development and automation work for our group. ... Give a hobbit a ring and he eats fish for an age. ...
    (perl.dbi.users)
  • Re: How to test status of handler sth?
    ... > either failing or I lost the handle (sth). ... Give a hobbit a ring and he eats fish for an age. ...
    (perl.dbi.users)