DBD::Oracle & overloaded stored procedure

[Eugene Krivdyuk]

I'm trying to make a call of packaged stored procedure, e.g.:

=================== perl code ===================
$sSQL = q{
begin

package.function_name( :i_param1
, :i_param2
, :i_param3
, :o_param1
);
end;
};

$sth = $dbh->prepare($sSQL);

$sth->bind_param(':i_param1', 1);
$sth->bind_param(':i_param2', $sDateStart, { ora_type => ORA_VARCHAR2 });
$sth->bind_param(':i_param3', $sDateEnd, { ora_type => ORA_VARCHAR2 });

$sth->bind_param_inout( ':o_param1', \$iDaysCnt, 100000, { ora_type
=> ORA_NUMBER } );

$sth->execute;
=================== perl code ===================

function_name is an overloaded PL/SQL stored procedure, one accepts
i_param2 & i_param3 of type DATE, second accepts i_param2 & i_param3
of type VARCHAR2.
When executing code like above, I'm getting this error:

PLS-00307: too many declarations of 'function_name' match this call

Is there any way to make it work?

[John Scoles]

More a PL/SQL question than a DBI/DBD one but I will give it a shot but
I will need to have the original package or at least a dumbed down
version of it to test it out.

Seems it should work.

cheers
John Scoles

Post by Eugene Krivdyuk
=================== perl code ===================
$sSQL = q{
begin
package.function_name( :i_param1
, :i_param2
, :i_param3
, :o_param1
);
end;
};
$sth = $dbh->prepare($sSQL);
$sth->bind_param(':i_param1', 1);
$sth->bind_param(':i_param2', $sDateStart, { ora_type => ORA_VARCHAR2 });
$sth->bind_param(':i_param3', $sDateEnd, { ora_type => ORA_VARCHAR2 });
$sth->bind_param_inout( ':o_param1', \$iDaysCnt, 100000, { ora_type
=> ORA_NUMBER } );
$sth->execute;
=================== perl code ===================
function_name is an overloaded PL/SQL stored procedure, one accepts
i_param2 & i_param3 of type DATE, second accepts i_param2 & i_param3
of type VARCHAR2.
PLS-00307: too many declarations of 'function_name' match this call
Is there any way to make it work?

[John Scoles]

Well I spent the last few mins playing with a similar overloaded
procedure found here

http://www.psoug.org/reference/packages.html, you have to scroll down a
bit to '*Package Overloading'

*and I got it to work with this code

my $dbh =$dbh = DBI->connect('dbi:Oracle:',xxx','xxx');
$sth = $dbh->prepare("begin overloaded.insby(?); end;");
$sth->bind_param(1, 22);

$sth->execute();*

*and

$sth = $dbh->prepare("begin overloaded.insby(?); end;");
$sth->bind_param(1, 'test');

$sth->execute();

both worked for me.

See if you can get that example working and then build it up from there?

cheers
John Scoles

Post by John Scoles
More a PL/SQL question than a DBI/DBD one but I will give it a shot
but I will need to have the original package or at least a dumbed
down version of it to test it out.
Seems it should work.
cheers
John Scoles

Post by Eugene Krivdyuk
=================== perl code ===================
$sSQL = q{
begin
package.function_name( :i_param1
, :i_param2
, :i_param3
, :o_param1
);
end;
};
$sth = $dbh->prepare($sSQL);
$sth->bind_param(':i_param1', 1);
$sth->bind_param(':i_param2', $sDateStart, { ora_type =>
ORA_VARCHAR2 });
$sth->bind_param(':i_param3', $sDateEnd, { ora_type =>
ORA_VARCHAR2 });
$sth->bind_param_inout( ':o_param1', \$iDaysCnt, 100000, { ora_type
=> ORA_NUMBER } );
$sth->execute;
=================== perl code ===================
function_name is an overloaded PL/SQL stored procedure, one accepts
i_param2 & i_param3 of type DATE, second accepts i_param2 & i_param3
of type VARCHAR2.
PLS-00307: too many declarations of 'function_name' match this call
Is there any way to make it work?

[John Scoles]

unfortunately the second package does not compile as it 'returns 1'
which is not a valid pl/SQL command

More a PL/SQL question than a DBI/DBD one but I will give it a shot but I
will need to have the original package or at least a dumbed down version of
it to test it out.

Please, see attached package source.
I've found workaround for this issue (misunderstanding?). If two
procedure test_func(i_nParam1 in NUMBER
, i_dtParam2 in DATE
, i_dtParam3 in DATE
, o_nDays out NUMBER
) is
begin
return 1;
end test_func;
procedure test_func(i_nParam1 in NUMBER
, i_sParam2 in VARCHAR2
, i_sParam3 in VARCHAR2
, o_nDays out NUMBER
) is
begin
return 1;
end test_func;
$sSQL = q{
begin
package.function_name( i_nParam1 => :i_param1
, i_sParam2 => :i_param2
, i_sParam3 => :i_param3
, o_nDays => :o_param1
);
end;
};
The question here is: What if parameters named equally in both functions ?

[Martin Evans]

Post by Eugene Krivdyuk
=================== perl code ===================
$sSQL = q{
begin
package.function_name( :i_param1
, :i_param2
, :i_param3
, :o_param1
);
end;
};
$sth = $dbh->prepare($sSQL);
$sth->bind_param(':i_param1', 1);
$sth->bind_param(':i_param2', $sDateStart, { ora_type => ORA_VARCHAR2 });
$sth->bind_param(':i_param3', $sDateEnd, { ora_type => ORA_VARCHAR2 });
$sth->bind_param_inout( ':o_param1', \$iDaysCnt, 100000, { ora_type
=> ORA_NUMBER } );
$sth->execute;
=================== perl code ===================
function_name is an overloaded PL/SQL stored procedure, one accepts
i_param2 & i_param3 of type DATE, second accepts i_param2 & i_param3
of type VARCHAR2.
PLS-00307: too many declarations of 'function_name' match this call
Is there any way to make it work?

I believe this is the right error for what you have done because the
only way for oracle to know which function you meant is if the bound
parameters match the right function. Since you are binding dates as
varchars and oracle can convert varchars to various types how can it
know which function you meant? If it isn't that, then it may depend on
what is bound for the date/varchar parameters e.g., if they are NULL
then Oracle won't know which function you meant (there is an example of
this at
http://oraclequirks.blogspot.com/2007/05/pls-00307-too-many-declarations-of-xyz.html).
Obviously renaming the functions would be an answer or changing them so
they have different numbers of arguments but I presume you cannot do
this. I don't know what else to suggest and I don't think this is caused
by DBD::Oracle - I think you'd have a similar problem in pl/sql.

Personally I don't like overloaded functions in Oracle as whenever I've
come across them they have led to confusion; especially so when they
have defaults for parameters too.

Martin

[Eugene Krivdyuk]

I believe this is the right error for what you have done because the only
way for oracle to know which function you meant is if the bound parameters
match the right function. Since you are binding dates as varchars and oracle
can convert varchars to various types how can it know which function you
meant?

Error occurs even when I define type for bind_param explicitly, e.g.:

$sth->bind_param(':i_param2', $sDateStart, { ora_type => ORA_VARCHAR2 });
$sth->bind_param(':i_param3', $sDateEnd, { ora_type => ORA_VARCHAR2 });

If it isn't that, then it may depend on what is bound for the
date/varchar parameters e.g., if they are NULL then Oracle won't know which
function you meant

They are always not NULLs in my case.

[Martin Evans]

Post by Eugene Krivdyuk

I believe this is the right error for what you have done because the only
way for oracle to know which function you meant is if the bound parameters
match the right function. Since you are binding dates as varchars and oracle
can convert varchars to various types how can it know which function you
meant?

$sth->bind_param(':i_param2', $sDateStart, { ora_type => ORA_VARCHAR2 });
$sth->bind_param(':i_param3', $sDateEnd, { ora_type => ORA_VARCHAR2 });

Yes, I saw that but varchars can be converted to dates and oracle
supports implicit conversion of varchars to dates so it is still not
clear to Oracle which function to use. I suspect if you had parameter
differences in the functions where an implicit conversion could not be
performed then Oracle would happily match up your call to the right
function.

Post by Eugene Krivdyuk

If it isn't that, then it may depend on what is bound for the
date/varchar parameters e.g., if they are NULL then Oracle won't know which
function you meant

They are always not NULLs in my case.

I only mentioned that in case as I'd seen that before and your example
did not rule it out.

Anyway you have a sensible workaround now with the advantage that anyone
looking at the call can also be sure which version of the function you
are using.

Martin

[Eugene Krivdyuk]

Yes, I saw that but varchars can be converted to dates and oracle supports
implicit conversion of varchars to dates so it is still not clear to Oracle
which function to use. I suspect if you had parameter differences in the
functions where an implicit conversion could not be performed then Oracle
would happily match up your call to the right function.

Just a thought: wouldn't it be better (for us, users) if Oracle do not
perform implicit
coneversion when this conversion make function call ambiguous?