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

On 02/05/12 17:06, Stricker, Scott (IS) wrote:
> All:
> 
> I'm trying to use perl to invoke a stored procedure to return an object.
>

DBD::Oracle does not support support the return of objects via parameters.
It does have some support for fetching object as select list elements.
See the POD for DBD::Oracle.
 
> 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
> 
> 
> 
> 


-- 
Charles Jardine - Computing Service, University of Cambridge
[email protected]    Tel: +44 1223 334506, Fax: +44 1223 334679