On 15/01/13 15:04, Charles Jardine wrote:
On 15/01/13 11:20, Martin J. Evans wrote:
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.
[snip]
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.
When a variable of a REF CURSOR type is declared is is initially
atomically null. It does not refer to cursor until it is OPENed,
or set to a non-null value in some other way.
Are you saying that, in the case of a NULL variable, the indicator
variable does not indicate nullity?
I am saying when you have:
procedure fred(pcur OUT SYS_REFCURSOR) as
begin
pcur := NULL;
end;
then the output parameter DBD::Oracle sees appears to be a valid Oracle
statement as you can call OCIAttrGet for OCI_ATTR_STMT_STATE and it works. The
following snippet illustrates this:
OCIAttrGet_stmhp_stat2(imp_sth, (OCIStmt*)phs->desc_h, &stmt_state, 0,
OCI_ATTR_STMT_STATE, status);
The desc_h in the parameters must be an OCIStmt * or surely this would not work.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
