Re: Trying to use perl to call oracle stored proc to return an object.

Did you specify the direction for each parameter?

----- Original Message -----
From: "Scott Stricker (IS)" <[email protected]>
To: [email protected]
Sent: Wednesday, May 2, 2012 12:06:05 PM
Subject: Trying to use perl to call oracle stored proc to return an object.

All:

I'm trying to use perl to invoke a stored procedure to return an object.

Oracle Procedure is defined as

PROCEDURE FetchTrackObjbyID  (  p_ID IN TRACK.ID%TYPE  , p_TARGET OUT TARGET  )

Where p_ID is numeric and p_TARGET is the TARGET object type.

When I 'execute', I get

DBD::Oracle::st execute failed: ORA-06550: line 2, column 5:
PLS-00306: wrong number or types of arguments in call to 'FETCHTRACKOBJBYID'
ORA-06550: line 2, column 5:
PL/SQL: Statement ignored (DBD ERROR: error possibly near <*> indicator at char 10 in 'BEGIN
    <*>FetchTrackObjbyID(:p_id, :p_target);
END;
') [for Statement "BEGIN
    FetchTrackObjbyID(:p_ID, :p_Target);
END;
" with ParamValues: :p_id=1, :p_target=undef] at


So far, I've got

    my $i = 5;
    my $p_Target;  # should this be either @p_Target or %p_Target
     my $sqlstmt= <<"END_SQL";
BEGIN
    FetchTrackObjbyID(:p_ID, :p_Target);
END;
END_SQL

        my $sth = $dbh->prepare($sqlstmt) or die "Can't prepare statement\n$sqlstmt\n$DBI::errstr\n";
        $sth->bind_param_inout(":p_Target",\$p_Target, 20) or die "bind(target) err is $DBI::errstr";  # Should I specify a bind type and if so, what type?
        $sth->bind_param(":p_ID",$i) or die "bind(ID) err is  $DBI::errstr\n";
        $sth->execute();

Hoping it is something really simple.  Thanks for any help

E. Scott Stricker @ Work




-- 


Robert Durgin 


Sr. Software Engineer 

Texterity • 144 Turnpike Road 

Southborough , MA 01772 

[email protected] 

www.texterity.com