On 15/01/13 10:56, Charles Jardine wrote:
On 11/01/13 16:04, Martin J. Evans wrote:
I am using DBD::Oracle and calling a procedure which returns a reference
cursor. However, sometimes the reference cursor is not opened and only
the procedure knows this. The problem is if I call the procedure from
DBD::Oracle and the cursor is not opened I get an Oracle error saying
the cursor is not executed:
test procedure:
procedure p_n2(pcur OUT SYS_REFCURSOR) AS
begin
pcur := NULL;
end;
example perl:
my $s = $h->prepare(q/begin mypkg.p_n2(?); end;/);
$s->bind_param_inout(1, \my $cursor, 100, {ora_type => ORA_RSET});
$s->execute; # errors
The error occurs because DBD::Oracle attempts to call dbd_describe on
the returned cursor (before perl land even sees it) and that code does
things like call OCIAttrGet for PARAM_COUNT etc which Oracle disallows
if the statement is not executed.
An easy solution is to just open an empty cursor if the procedure cannot
open a real one by doing something like:
open pcur for select 1 from dual;
but I don't like that as DBD::Oracle will make dozens of calls and do
quite a bit of work in dbd_describe which is wasting time and the
purpose of the change to my procedure is to speed this application up
not slow it down.
Martin, I agree that you have found a bug which ought to be fixed.
However I think that the bug is much more fundamental, and much
simpler than your analysis suggests.
The DBI convention for database null values is to represent them
by Perl undefined values. There is no reason why this convention
should not apply to result sets.
The perl code needs to test the indicator variable associated with
the supposed result set. If this variable indicates a null value,
all processing of actual value returned by Oracle should be skipped,
as with any other null value. The pre-created perl statement handle
should be left alone. It may me used in a subsequent execute of the
same statement with different bind values. The value returned to
perl should be undef, not a reference to the magic statement handle.
Thanks for your comments Charles.
In effect I believe my second patch does what you describe. If you define an
output SYS_REFCURSOR in a procedure but don't open it you still get a cursor
back but it is only initialised and not executed. My second patch (the one I
prefer) looks at the state of the cursor and if it is initialised but not
executed it avoids creating a DBI sth and that output parameter is seen as
undef in perl land.
If I've misunderstood you please put me right.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
