Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes
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.
Just to be clear in case anyone thinks I've just invented a scenario and there
is nothing actually wrong with DBD::Oracle - it is most definitely a bug for
DBD::Oracle to attempt to describe a non-executed stmt.
Possible solutions are complicated by these facts (some of which I only
discovered after a few attempts of patching this):
o DBD::Oracle magics a DBI sth into existence but it does this before the
Oracle's execute is called in dbd_execute.
o DBD::Oracle attempts to describe statements which are initialized but not
executed.
o DBD::Oracle will always call dbd_describe on any statement if it is not
marked active when you attempt to access any attribute of the sth.
So my first hack was to change dbd_describe to ask for the statement state and
if it was initialized but not executed it just returns having done nothing
else. This works because the sth is left in an Active state and dbd_describe
has been called so the internal flag dbd_describe_done is set. However the down
sides are a) you need to check to see if NUM_OF_FIELDS returns something before
using it b) the sth is not really Active c) dbd_describe is a critical function
and is used for all stmt handles, not just ones magicked into existence for
returned cursors.
My second attempt was to hijack the code in pp_exec_rset which is called just
before execute to magic the stmt handle into existence and after execute to set
the sth up and call dbd_describe. My thinking was that it was much safer
changing code here. The first sub attempt to simply to check the stmt state and
if initialized and not executed, don't call dbd_describe and don't set the sth
Active. The idea was to check Active outside in perl land. It does not work
because any time you attempt to access an attribute of a non-Active sth where
dbd_describe has not been called, you guessed it, DBD::Oracle calls
dbd_describe - so I am back where I started. My second sub attempt was to
outright lie and set dbd_describe_done and leave Active off so from perl land I
just need to test Active flag. This works and is a safer change since it ONLY
applies to sth handles magicked into existence for returned cursors. Also, if
you attempt to do anything else with the sth it errors as it should:
DBD::Oracle::st fetch failed: ERROR no statement executing (perhaps you need to
call execute first) at bz1245.pl line 16.
Wondered if anyone else had any thoughts on this. Ideally I'd like a solution
people are happy enough to go into DBD::Oracle officially as I maintained quite
a number of bespoke patches here for some years in the past and it was a PITA.
Also, the more simple the solution the better as the internals of DBD::Oracle
are quite complex and I'd rather not re-engineer a load of code just for this.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
RE: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes
Hmm you sure pick the head scratchers.
My first thoughts where why whould you make such a procedure?? But then I
relaisze it could have some use when prototying/develpopeing ect.
Looking at
'DBD::Oracle attempts to describe statements which are initialized but not
executed'
We might be able to do something here. I remember working on a patch some time
ago (ie 2008) to get the describe after the execute then do all the binding at
the end of an execute etc as the newer versions of Oracle return this data for
you. Not sure it that is the right time for this on as if I remember correctly
there was alot of reprogrmming involved.
Will have to look into that one.
Anyway I agree that pp_exec_rset is the place to make your change as the is
safe area to do it. Perhaps we can delay the pre_exec part untill after the
inital query is executed by that time you will know if you have a ref that you
will need to bind and return??
Been a while since I looked at this part of the code so bear with me if I sound
a little rusty.
Cheere
John
Date: Fri, 11 Jan 2013 16:04:13 +
From: martin.ev...@easysoft.com
To: dbi-dev@perl.org
Subject: Problem with procedures returning a SYS_REFCURSOR which is not
open/executed - possible fixes
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.
Just to be clear in case anyone thinks I've just invented a scenario and
there is nothing actually wrong with DBD::Oracle - it is most definitely a
bug for DBD::Oracle to attempt to describe a non-executed stmt.
Possible solutions are complicated by these facts (some of which I only
discovered after a few attempts of patching this):
o DBD::Oracle magics a DBI sth into existence but it does this before the
Oracle's execute is called in dbd_execute.
o DBD::Oracle attempts to describe statements which are initialized but not
executed.
o DBD::Oracle will always call dbd_describe on any statement if it is not
marked active when you attempt to access any attribute of the sth.
So my first hack was to change dbd_describe to ask for the statement state
and if it was initialized but not executed it just returns having done
nothing else. This works because the sth is left in an Active state and
dbd_describe has been called so the internal flag dbd_describe_done is set.
However the down sides are a) you need to check to see if NUM_OF_FIELDS
returns something before using it b) the sth is not really Active c)
dbd_describe is a critical function and is used for all stmt handles, not
just ones magicked into existence for returned cursors.
My second attempt was to hijack the code in pp_exec_rset which is called just
before execute to magic the stmt handle into existence and after execute to
set the sth up and call dbd_describe. My thinking was that it was much safer
changing code here. The first sub attempt to simply to check the stmt state
and if initialized and not executed, don't call dbd_describe and don't set
the sth Active. The idea was to check Active outside in perl land. It does
not work because any time you attempt to access an attribute of a non-Active
sth where dbd_describe has not been called, you guessed it, DBD::Oracle calls
dbd_describe - so I am back where I started. My second sub attempt was to
outright lie and set dbd_describe_done and leave Active off so from perl land
I just need to test Active flag. This works and is a safer change since it
ONLY applies to sth handles magicked into existence for returned cursors.
Also, if you attempt to do anything else with the sth it errors as it should:
DBD::Oracle::st fetch failed: ERROR no statement executing (perhaps you need
to call execute first) at bz1245.pl line 16.
Wondered if anyone else had any thoughts on this. Ideally I'd like a
