Newbie problem with Oracle functions

[David Bakkers]

Up until now I have only used DBI for generic SQL and never had a problem

I am having problems trying to access a function within Oracle via DBI / DBD::Oracle

The function in question is called "SPSIFINANCIAL" and returns single numeric when passed a 6 digit number. Here is an example of how it is called within Oracle:

DECLARE
RetVal NUMBER;

BEGIN
RetVal := COLLEGE.SPISFINANCIAL ( 123456 );
END;


... and here is my PERL

$dbh = DBI->connect(
'dbi:Oracle:sasdata',
'username',
'password',
{
RaiseError => 1,
AutoCommit => 0
}
) || die "Database connection not made: $DBI::errstr";

my $retval;
eval {
my $func = $dbh->prepare(q{
BEGIN
:retval := SPISFINANCIAL(
parameter1_in => :parameter1
);
END;
});

$func->bind_param(":parameter1", '123456');
$func->bind_param_inout(":retval", \$retval, 6);
$func->execute;
$dbh->commit;
};

if( $@ ) {
warn "!! Execution of stored procedure failed!!\n$DBI::errstr\n";
$dbh->rollback;
}

However, when I call $func->execute; DBD returns the error "DBD::Oracle::st execute failed." followed by "PLS00201: identifier 'COLLEGE.SPISFINANCIAL' must be declared"



David Bakkers, Systems Administrator.
--------------------------------------------------------------------
Central College, Sydney, Australia.
Ph:(02) 8217 9609. Fax:(02) 9211 0628
reply to [EMAIL PROTECTED]
--------------------------------------------------------------------