Re: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes
On 15/01/13 23:21, Martin J. Evans wrote: I see loads of code setting indp so I created an indp2 in the phs and passed it to OCIBindByName above and it is always 0 (Oracle assigned an intact value to the host variable) whether a null cursor is returned or not. It also did not seem to trigger ORA-01001 (invalid cursor) errors. Also the test suite works with the indp set in the OCIBindByName. What a PITA. I really wish when people write code like this they comment why better. So it would seem resurrecting the phs-indp in the OCIBindByName does not currently give me -1. Starting to wish I never started this. My current change is better (in that it at least works whereas the previous code did not at all) but you seem to suggest it is incomplete and that concerns me. However, I've not been able to see what you suggested should happen. I've already proved this speeds our application up a lot compared with having to put a daft select 1 from dual in to just make DBD::Oracle work so it would be a shame to fall at the last hurdle. Any other ideas Charles? Bear in mind I cannot be getting a value from a previous execute as my test code only does one execute so perhaps when you bind a SYS_REFCURSOR you get a valid stmt handle back even if it is not executed. It seems this must be the case since I can call OCIAtrrGet(OCI_ATTR_STMT_STATE) on it after only one execute and it returns OCI_STMT_STATE_INITIALIZED. Martin, I have reproduced your results. If you supply an indp, Oracle does not ignore it. It explicitly sets it to 0, indicating a non-null value. It seems that OCI does not represent a null PL/SQL cursor reference as a normal null value. As you originally thought, it represents it with a statement handle which is not open. You were right - I was wrong. I can't find any documentation of this special treatment of null values of type SQLT_RSET, but, in trawling through the OCI manual, and Oracle's knowledge base, I have found several examples of binds of type SQLT_RSET, none of which use indicator variables. I think is is important that PL/SQL null cursor references should reach Perl as undefined values. In the light of the above, I think that what you have already done is probably the best which can be done. It will treat cursor references which have explicitly closed as if they were null, but I think this is acceptable. I hope I haven't wasted too much if your time. -- Charles Jardine - Computing Service, University of Cambridge c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes
On 17/01/13 12:26, Charles Jardine wrote: On 15/01/13 23:21, Martin J. Evans wrote: I see loads of code setting indp so I created an indp2 in the phs and passed it to OCIBindByName above and it is always 0 (Oracle assigned an intact value to the host variable) whether a null cursor is returned or not. It also did not seem to trigger ORA-01001 (invalid cursor) errors. Also the test suite works with the indp set in the OCIBindByName. What a PITA. I really wish when people write code like this they comment why better. So it would seem resurrecting the phs-indp in the OCIBindByName does not currently give me -1. Starting to wish I never started this. My current change is better (in that it at least works whereas the previous code did not at all) but you seem to suggest it is incomplete and that concerns me. However, I've not been able to see what you suggested should happen. I've already proved this speeds our application up a lot compared with having to put a daft select 1 from dual in to just make DBD::Oracle work so it would be a shame to fall at the last hurdle. Any other ideas Charles? Bear in mind I cannot be getting a value from a previous execute as my test code only does one execute so perhaps when you bind a SYS_REFCURSOR you get a valid stmt handle back even if it is not executed. It seems this must be the case since I can call OCIAtrrGet(OCI_ATTR_STMT_STATE) on it after only one execute and it returns OCI_STMT_STATE_INITIALIZED. Martin, I have reproduced your results. If you supply an indp, Oracle does not ignore it. It explicitly sets it to 0, indicating a non-null value. It seems that OCI does not represent a null PL/SQL cursor reference as a normal null value. As you originally thought, it represents it with a statement handle which is not open. You were right - I was wrong. My post was simply based on observation and not what the docs said. However, I'm comforted to know that you get the same results. I can't find any documentation of this special treatment of null values of type SQLT_RSET, but, in trawling through the OCI manual, and Oracle's knowledge base, I have found several examples of binds of type SQLT_RSET, none of which use indicator variables. I think I'll undo the change which passes an indicator. I wish I knew what that comment meant by causes an error but so long as I don't need the indicator it is irrelevant. I think is is important that PL/SQL null cursor references should reach Perl as undefined values. In the light of the above, I think that what you have already done is probably the best which can be done. It will treat cursor references which have explicitly closed as if they were null, but I think this is acceptable. Excellent. In addition someone else who reported a similar RT a while ago tested my patch and it fixed their problem too. I hope I haven't wasted too much if your time. Not at all. I am always grateful for your input. It was a avenue worth exploring. Thanks again. I'll apply this patch later today. It will return undef for a non executed output cursor. 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
Date: Thu, 17 Jan 2013 13:48:15 + From: martin.ev...@easysoft.com To: c...@cam.ac.uk CC: dbi-dev@perl.org Subject: Re: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes On 17/01/13 12:26, Charles Jardine wrote: On 15/01/13 23:21, Martin J. Evans wrote: I see loads of code setting indp so I created an indp2 in the phs and passed it to OCIBindByName above and it is always 0 (Oracle assigned an intact value to the host variable) whether a null cursor is returned or not. It also did not seem to trigger ORA-01001 (invalid cursor) errors. Also the test suite works with the indp set in the OCIBindByName. What a PITA. I really wish when people write code like this they comment why better. So it would seem resurrecting the phs-indp in the OCIBindByName does not currently give me -1. Starting to wish I never started this. My current change is better (in that it at least works whereas the previous code did not at all) but you seem to suggest it is incomplete and that concerns me. However, I've not been able to see what you suggested should happen. I've already proved this speeds our application up a lot compared with having to put a daft select 1 from dual in to just make DBD::Oracle work so it would be a shame to fall at the last hurdle. Any other ideas Charles? Bear in mind I cannot be getting a value from a previous execute as my test code only does one execute so perhaps when you bind a SYS_REFCURSOR you get a valid stmt handle back even if it is not executed. It seems this must be the case since I can call OCIAtrrGet(OCI_ATTR_STMT_STATE) on it after only one execute and it returns OCI_STMT_STATE_INITIALIZED. Martin, I have reproduced your results. If you supply an indp, Oracle does not ignore it. It explicitly sets it to 0, indicating a non-null value. It seems that OCI does not represent a null PL/SQL cursor reference as a normal null value. As you originally thought, it represents it with a statement handle which is not open. You were right - I was wrong. My post was simply based on observation and not what the docs said. However, I'm comforted to know that you get the same results. I can't find any documentation of this special treatment of null values of type SQLT_RSET, but, in trawling through the OCI manual, and Oracle's knowledge base, I have found several examples of binds of type SQLT_RSET, none of which use indicator variables. Just my 2p on some verbage. I think 'null' would be the 'wrong' word to use here when refering to a this type of 'SQLT_RSET' .This type is a referance so it would either be in one of two states 'initialized' or 'unitiliazied' points to something or doesn't, and even when it doesn't point to somthing is still takes up memory!! You have to love 'C' ;) From my deailing with OCI what I would expect to get is an 'itilaized' referance no matter what state the object the ref is pointing to? I wish I had some time to play with this one as it is a goodie. Maybe tonight:) Did you check this in Martin or is it on a branch someplace?? Cheers I think I'll undo the change which passes an indicator. I wish I knew what that comment meant by causes an error but so long as I don't need the indicator it is irrelevant. I think is is important that PL/SQL null cursor references should reach Perl as undefined values. In the light of the above, I think that what you have already done is probably the best which can be done. It will treat cursor references which have explicitly closed as if they were null, but I think this is acceptable. Excellent. In addition someone else who reported a similar RT a while ago tested my patch and it fixed their problem too. I hope I haven't wasted too much if your time. Not at all. I am always grateful for your input. It was a avenue worth exploring. Thanks again. I'll apply this patch later today. It will return undef for a non executed output cursor. 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
On 17/01/2013 18:32, John Scoles wrote: Date: Thu, 17 Jan 2013 13:48:15 + From: martin.ev...@easysoft.com To: c...@cam.ac.uk CC: dbi-dev@perl.org Subject: Re: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes On 17/01/13 12:26, Charles Jardine wrote: On 15/01/13 23:21, Martin J. Evans wrote: I see loads of code setting indp so I created an indp2 in the phs and passed it to OCIBindByName above and it is always 0 (Oracle assigned an intact value to the host variable) whether a null cursor is returned or not. It also did not seem to trigger ORA-01001 (invalid cursor) errors. Also the test suite works with the indp set in the OCIBindByName. What a PITA. I really wish when people write code like this they comment why better. So it would seem resurrecting the phs-indp in the OCIBindByName does not currently give me -1. Starting to wish I never started this. My current change is better (in that it at least works whereas the previous code did not at all) but you seem to suggest it is incomplete and that concerns me. However, I've not been able to see what you suggested should happen. I've already proved this speeds our application up a lot compared with having to put a daft select 1 from dual in to just make DBD::Oracle work so it would be a shame to fall at the last hurdle. Any other ideas Charles? Bear in mind I cannot be getting a value from a previous execute as my test code only does one execute so perhaps when you bind a SYS_REFCURSOR you get a valid stmt handle back even if it is not executed. It seems this must be the case since I can call OCIAtrrGet(OCI_ATTR_STMT_STATE) on it after only one execute and it returns OCI_STMT_STATE_INITIALIZED. Martin, I have reproduced your results. If you supply an indp, Oracle does not ignore it. It explicitly sets it to 0, indicating a non-null value. It seems that OCI does not represent a null PL/SQL cursor reference as a normal null value. As you originally thought, it represents it with a statement handle which is not open. You were right - I was wrong. My post was simply based on observation and not what the docs said. However, I'm comforted to know that you get the same results. I can't find any documentation of this special treatment of null values of type SQLT_RSET, but, in trawling through the OCI manual, and Oracle's knowledge base, I have found several examples of binds of type SQLT_RSET, none of which use indicator variables. Just my 2p on some verbage. I think 'null' would be the 'wrong' word to use here when refering to a this type of 'SQLT_RSET' . This type is a referance so it would either be in one of two states 'initialized' or 'unitiliazied' points to something or doesn't, and even when it doesn't point to somthing is still takes up memory!! You have to love 'C' ;) To be honest the cursor is uninitialised, executed or finished and in this case it it is uninitialised and useless i.e., you cannot fetch from from it. undef is the the only reasonable value to return to Perl. As for any structure allocated in C land it is still deallocated as it is in the descriptor, all my change does is: a) return undef if the cursor is unusable b) stop DBD::Oracle attempting to describe an unusable cursor and erroring From my deailing with OCI what I would expect to get is an 'itilaized' referance no matter what state the object the ref is pointing to? All I can say is it is uninitialised and Charles has found that also. I wish I had some time to play with this one as it is a goodie. Maybe tonight:) Did you check this in Martin or is it on a branch someplace?? The patch is earlier in this thread. If I get time tonight I'm going to check it in to the trunk because I believe it is a legitimate fix for an existing bug that if anyone else had hit they'd be as stymied as I am. In fact, someone else did hit it - see the RT I referenced earlier in the thread - their problem resulted in a segfault due to the destroy method attempting to fetch from an invalid cursor. Cheers Martin I think I'll undo the change which passes an indicator. I wish I knew what that comment meant by causes an error but so long as I don't need the indicator it is irrelevant. I think is is important that PL/SQL null cursor references should reach Perl as undefined values. In the light of the above, I think that what you have already done is probably the best which can be done. It will treat cursor references which have explicitly closed as if they were null, but I think this is acceptable. Excellent. In addition someone else who reported a similar RT a while ago tested my patch and it fixed their problem too. I hope I haven't wasted too much if your time. Not at all. I am always grateful for your input. It was a avenue worth exploring. Thanks again. I'll apply this patch
RE: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes
Date: Thu, 17 Jan 2013 19:47:06 + From: martin.ev...@easysoft.com To: byter...@hotmail.com CC: c...@cam.ac.uk; dbi-dev@perl.org Subject: Re: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes On 17/01/2013 18:32, John Scoles wrote: Date: Thu, 17 Jan 2013 13:48:15 + From: martin.ev...@easysoft.com To: c...@cam.ac.uk CC: dbi-dev@perl.org Subject: Re: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes On 17/01/13 12:26, Charles Jardine wrote: On 15/01/13 23:21, Martin J. Evans wrote: I see loads of code setting indp so I created an indp2 in the phs and passed it to OCIBindByName above and it is always 0 (Oracle assigned an intact value to the host variable) whether a null cursor is returned or not. It also did not seem to trigger ORA-01001 (invalid cursor) errors. Also the test suite works with the indp set in the OCIBindByName. What a PITA. I really wish when people write code like this they comment why better. So it would seem resurrecting the phs-indp in the OCIBindByName does not currently give me -1. Starting to wish I never started this. My current change is better (in that it at least works whereas the previous code did not at all) but you seem to suggest it is incomplete and that concerns me. However, I've not been able to see what you suggested should happen. I've already proved this speeds our application up a lot compared with having to put a daft select 1 from dual in to just make DBD::Oracle work so it would be a shame to fall at the last hurdle. Any other ideas Charles? Bear in mind I cannot be getting a value from a previous execute as my test code only does one execute so perhaps when you bind a SYS_REFCURSOR you get a valid stmt handle back even if it is not executed. It seems this must be the case since I can call OCIAtrrGet(OCI_ATTR_STMT_STATE) on it after only one execute and it returns OCI_STMT_STATE_INITIALIZED. Martin, I have reproduced your results. If you supply an indp, Oracle does not ignore it. It explicitly sets it to 0, indicating a non-null value. It seems that OCI does not represent a null PL/SQL cursor reference as a normal null value. As you originally thought, it represents it with a statement handle which is not open. You were right - I was wrong. My post was simply based on observation and not what the docs said. However, I'm comforted to know that you get the same results. I can't find any documentation of this special treatment of null values of type SQLT_RSET, but, in trawling through the OCI manual, and Oracle's knowledge base, I have found several examples of binds of type SQLT_RSET, none of which use indicator variables. Just my 2p on some verbage. I think 'null' would be the 'wrong' word to use here when refering to a this type of 'SQLT_RSET' . This type is a referance so it would either be in one of two states 'initialized' or 'unitiliazied' points to something or doesn't, and even when it doesn't point to somthing is still takes up memory!! You have to love 'C' ;) To be honest the cursor is uninitialised, executed or finished and in this case it it is uninitialised and useless i.e., you cannot fetch from from it. undef is the the only reasonable value to return to Perl. As for any structure allocated in C land it is still deallocated as it is in the descriptor, all my change does is: a) return undef if the cursor is unusable b) stop DBD::Oracle attempting to describe an unusable cursor and erroring From my deailing with OCI what I would expect to get is an 'itilaized' referance no matter what state the object the ref is pointing to? All I can say is it is uninitialised and Charles has found that also. I wish I had some time to play with this one as it is a goodie. Maybe tonight:) Did you check this in Martin or is it on a branch someplace?? The patch is earlier in this thread. If I get time tonight I'm going to check it in to the trunk because I believe it is a legitimate fix for an existing bug that if anyone else had hit they'd be as stymied as I am. In fact, someone else did hit it - see the RT I referenced earlier in the thread - their problem resulted in a segfault due to the destroy method attempting to fetch from an invalid cursor. That would be great Martin. Seems I will have a little time today after all to look at it. So far I do not see any issues with the solution you came up with. CheersJohn Cheers Martin I think I'll undo the change which passes an indicator. I wish I knew what that comment meant by causes an error but so long as I don't need the indicator it is irrelevant. I think is is important that
Re: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes
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.
--
Charles Jardine - Computing Service, University of Cambridge
c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes
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
Re: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes
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?
--
Charles Jardine - Computing Service, University of Cambridge
c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes
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
Re: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes
On 15/01/13 16:01, Martin J. Evans wrote: On 15/01/13 15:04, Charles Jardine wrote: 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. In this case the REF CURSOR variable in question is explicitly null. I would expect the value returned via OCI to be accompanied by an indicator variable with a value of -1,indicating a null value. If this is the case, the value of the output variable is, to quote the OCI manual, 'unchanged'. It should be ignored. I am suggesting that the indicator variable should be tested before the looking at the value. If indicator is -1, the value could be the cursor returned by a previous execution of the same statement handle. -- Charles Jardine - Computing Service, University of Cambridge c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes
On 15/01/2013 17:52, Charles Jardine wrote:
On 15/01/13 16:01, Martin J. Evans wrote:
On 15/01/13 15:04, Charles Jardine wrote:
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.
In this case the REF CURSOR variable in question is explicitly null.
I would expect the value returned via OCI to be accompanied by an
indicator variable with a value of -1,indicating a null value.
If this is the case, the value of the output variable is,
to quote the OCI manual, 'unchanged'. It should be ignored.
I am suggesting that the indicator variable should be tested before
the looking at the value. If indicator is -1, the value could be
the cursor returned by a previous execution of the same statement
handle.
Thanks Charles.
I think your explanation means DBD::Oracle is even more broken than I
thought wrt to output cursors.
Looks like I need to do a bit more reading. Thanks for the pointer.
Out of interest I looked at the code a little more and see the
descriptor in the D::O's phs is freed and reallocated pre-execute. The
parameter is also rebound. In pp_exec_rset it does (note my comment
labelled MJE):
if (pre_exec) {/* pre-execute - allocate a statement handle -
MJE it does not do this now */
sword status;
/* extproc deallocates everything for us */
if (is_extproc)
return 1;
/* MJE what is the following test supposed to be - always true? */
if (!phs-desc_h || 1) { /* XXX phs-desc_t != OCI_HTYPE_STMT) */
if (phs-desc_h) {
OCIHandleFree_log_stat(imp_sth, phs-desc_h,
phs-desc_t, status);
phs-desc_h = NULL;
}
phs-desc_t = OCI_HTYPE_STMT;
OCIHandleAlloc_ok(imp_sth, imp_sth-envhp, phs-desc_h,
phs-desc_t, status);
}
phs-progv = (char*)phs-desc_h;
phs-maxlen = 0;
OCIBindByName_log_stat(imp_sth, imp_sth-stmhp, phs-bndhp,
imp_sth-errhp,
(text*)phs-name,
(sb4)strlen(phs-name),
phs-progv,
0,
(ub2)phs-ftype,
NULL, /* using phs-indp triggers ORA-01001 errors! */
NULL,
0,
0,
NULL,
OCI_DEFAULT,
status);
However, as you said the phs-indp is -1 as you said. I will correct my
change and make it check indp first.
As you know there is a lot of OCI code in DBD::Oracle and I'm not
familiar with it all by a long way so I'm always grateful for any
pointers/help.
Martin
Re: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes
On 15/01/2013 22:20, Martin J. Evans wrote:
On 15/01/2013 17:52, Charles Jardine wrote:
On 15/01/13 16:01, Martin J. Evans wrote:
On 15/01/13 15:04, Charles Jardine wrote:
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.
In this case the REF CURSOR variable in question is explicitly null.
I would expect the value returned via OCI to be accompanied by an
indicator variable with a value of -1,indicating a null value.
If this is the case, the value of the output variable is,
to quote the OCI manual, 'unchanged'. It should be ignored.
I am suggesting that the indicator variable should be tested before
the looking at the value. If indicator is -1, the value could be
the cursor returned by a previous execution of the same statement
handle.
Thanks Charles.
I think your explanation means DBD::Oracle is even more broken than I
thought wrt to output cursors.
Looks like I need to do a bit more reading. Thanks for the pointer.
Out of interest I looked at the code a little more and see the
descriptor in the D::O's phs is freed and reallocated pre-execute. The
parameter is also rebound. In pp_exec_rset it does (note my comment
labelled MJE):
if (pre_exec) {/* pre-execute - allocate a statement handle -
MJE it does not do this now */
sword status;
/* extproc deallocates everything for us */
if (is_extproc)
return 1;
/* MJE what is the following test supposed to be - always
true? */
if (!phs-desc_h || 1) { /* XXX phs-desc_t != OCI_HTYPE_STMT) */
if (phs-desc_h) {
OCIHandleFree_log_stat(imp_sth, phs-desc_h,
phs-desc_t, status);
phs-desc_h = NULL;
}
phs-desc_t = OCI_HTYPE_STMT;
OCIHandleAlloc_ok(imp_sth, imp_sth-envhp, phs-desc_h,
phs-desc_t, status);
}
phs-progv = (char*)phs-desc_h;
phs-maxlen = 0;
OCIBindByName_log_stat(imp_sth, imp_sth-stmhp, phs-bndhp,
imp_sth-errhp,
(text*)phs-name,
(sb4)strlen(phs-name),
phs-progv,
0,
(ub2)phs-ftype,
NULL, /* using phs-indp triggers ORA-01001 errors! */
NULL,
0,
0,
NULL,
OCI_DEFAULT,
status);
However, as you said the phs-indp is -1 as you said. I will correct
my change and make it check indp first.
As you know there is a lot of OCI code in DBD::Oracle and I'm not
familiar with it all by a long way so I'm always grateful for any
pointers/help.
Martin
hmm, unfortunately, the indp always seems to be -1 (The selected value
is null, and the value of the output variable is unchanged) even when an
opened cursor is returned as indp is not passed to OCIBindByName because
(see above code) it is commented out in the OCIBindByName because it
apparently triggers ORA-01001 errors but we've no idea why - sigh. So
I assume somewhere else set indp to -1, it certainly wasn't OCIBindByName.
I see loads of code setting indp so I created an indp2 in the phs and
passed it to OCIBindByName above and it is always 0 (Oracle assigned an
intact value to the host variable) whether a null cursor is returned or
not. It also did not seem to trigger ORA-01001 (invalid cursor) errors.
Also the test suite works with the indp set in the OCIBindByName. What a
PITA. I really wish when people write code like this they comment why
better.
So it would seem resurrecting the phs-indp in the OCIBindByName does
not currently give me -1. Starting to wish I never started this. My
current change is better (in that it at least works whereas the previous
code did not at all) but you seem to suggest it is incomplete and that
concerns me. However, I've not been able to see what you suggested
should happen. I've already proved this speeds our application up a lot
compared with having to put a daft select 1 from dual in to just make
DBD::Oracle work so it would be a shame to fall at the last hurdle. Any
other ideas Charles?
Bear in mind I cannot be getting a value from a previous execute as my
test code only does one execute so perhaps when you bind a SYS_REFCURSOR
you get a valid stmt handle back even if it is not executed. It seems
this must be the case since I can call OCIAtrrGet(OCI_ATTR_STMT_STATE)
on it after only one execute and it returns OCI_STMT_STATE_INITIALIZED.
As always, most grateful for
Re: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes
On 11/01/2013 21:22, John Scoles wrote:
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.
No, this is production.
Sometimes there is a result-set to return and sometimes there is not.
Our situation here is perhaps a little unusual in that there is no read
or write access to any table in the database for anyone other than the
owner of the package - definer rights. As a result there is a procedure
to return any result-set the outside code needs. In this case the
outside knows some work needs to be done but not what it is and usually
does this:
alerted some work needs to be done
calls proc to get the work
depending on the work it might call another proc
once work is done calls a proc to say it is complete and audit it
I'm skipping the last 2 steps for some work by doing the dispatching in
the database thus cutting out to 2 trips to the database. The downside
is some of the work returns a cursor and some does not.
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.
Doing the describe before the execute is a pain and the current describe
code makes all the decisions on how columns will be bound which means a
type to bind_col is ignored. Hence rt's like:
https://rt.cpan.org/Ticket/Display.html?id=71810
ora_type = ORA_RAW doesn't work for select
where the reporter has a raw column which is bound as a string so Oracle
converts it to hex and the result is twice as long. Of course we could
just change the default binding of raw to bytes but I was reticent to do
that since the code had been that way a long time and people might be
relying on it. If the describe was afterwards the reporter could just
specify raw in the bind_col call thus not upsetting anyone else.
I got away with implementing strictly typed etc on bind_col because they
are applied afterwards.
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??
That was one of my thoughts also. In actual fact, I'd rather have undef
back for the cursor when there is no open cursor returned, it seems more
intuitive to me and avoids creating a new sth for no purpose. My only
concern was if it was at all possible to return an initialised cursor
for a valid result-set which was just not executed yet - not that I know
if you can do this or how. I will hopefully try and find the time to
look at that.
Been a while since I looked at this part of the code so bear with me if I sound a little rusty.
Cheere
John
Anyway, the change I arrived at does not seem to have any negative
affects on the test suite or our application so far. If I can find a
better way of doing it which is not a total overhaul of dbd_describe I
will but as I seem to be the only one with this issue I did not want to
risk breaking other things.
Martin
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
Re: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes
On 11/01/2013 19:28, Tim Bunce wrote:
On Fri, Jan 11, 2013 at 04:04:13PM +, Martin J. Evans wrote:
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.
Sounds good to me. Thanks for looking after this Martin.
Tim.
I've now got 2 ways to fix this issue. The first way is described above
and is a relatively small change. When pp_exec_rset is called post
execute it simply looks at the Oracle statement state and if it is only
initialised and not executed it leaves Active off and sets done_desc to
stop DBD::Oracle attempting to call dbd_describe. On the outside all
your Perl needs to do is test Active before attempting to use the cursor.
Advantages: small change unlikely to have any repercussions since we
still return a sth and if you attempt to use a non-executed sth it will
error with not executed. Fixes the problem I'm trying to fix.
Disadvantages: still creates a sth which is useless
Index: dbdimp.c
===
--- dbdimp.c(revision 15554)
+++ dbdimp.c(working copy)
@@ -2737,10 +2737,11 @@
DBIc_LOGPIO(imp_sth),
pp_exec_rset bind %s - allocated %s...\n,
phs-name, neatsvpv(phs-sv, 0));
-
}
else { /* post-execute - setup the statement handle */
dTHR;
+ub4 stmt_state = 99;
+sword status;
SV * sth_csr = phs-sv;
D_impdata(imp_sth_csr, imp_sth_t, sth_csr);
@@ -2771,7 +2772,23 @@
imp_sth_csr-stmt_type = OCI_STMT_SELECT;
DBIc_IMPSET_on(imp_sth_csr);
-/* set ACTIVE so dbd_describe doesn't do explicit OCI describe */
+OCIAttrGet_stmhp_stat(imp_sth_csr, stmt_state, 0,
OCI_ATTR_STMT_STATE,
+if (status != OCI_SUCCESS) {
+oci_error(sth, imp_sth-errhp, status, OCIAttrGet
OCI_ATTR_STMT_ST
+return 0;
+}
+if (DBIc_DBISTATE(imp_sth)-debug = 3 || dbd_verbose = 3 ) {
+/* initialized=1, executed=2, end of fetch=3 */
+PerlIO_printf(
+DBIc_LOGPIO(imp_sth),
+ statement state: %u\n, stmt_state);
+}
+if (stmt_state == OCI_STMT_STATE_INITIALIZED) {
+imp_sth_csr-done_desc = 1;
+return 1;
+}
+
+/* set ACTIVE so dbd_describe doesn't do explicit OCI describe */
DBIc_ACTIVE_on(imp_sth_csr);
if (!dbd_describe(sth_csr, imp_sth_csr)) {
return 0;
Second solution is a bit more involved but I think better since a
non-executed sth is not returned - instead undef is returned.
Advantages: fixes problem and does not create a useless sth
Disadvantages: touches the code which gets run if the returned cursor is
executed although I've mainly just moved it to the post execute path.
Index: dbdimp.c
===
--- dbdimp.c(revision 15554)
+++ dbdimp.c(working copy)
@@ -2666,10 +2666,6 @@
dTHX;
if (pre_exec) { /* pre-execute - allocate a statement handle */
- dSP;
- D_imp_dbh_from_sth;
- HV *init_attr = newHV();
- int count;
sword status;
if (DBIc_DBISTATE(imp_sth)-debug = 3 || dbd_verbose
= 3 )
@@ -2691,7 +2687,6 @@
OCIHandleAlloc_ok(imp_sth, imp_sth-envhp,
phs-desc_h, phs-desc_t, status);
}
-
phs-progv = (char*)phs-desc_h;
phs-maxlen = 0;
@@ -2714,6 +2709,38 @@
return 0;
}
+ }
+ else { /* post-execute - setup the statement handle */
+ dTHR;
+ dSP;
+ D_imp_dbh_from_sth;
+ HV *init_attr = newHV();
+ int count;
+ub4 stmt_state = 99;
+sword status;
+ SV * sth_csr;
+
+/* Before we go to the bother of attempting to allocate a new sth
+ for this cursor make sure the Oracle sth is executed i.e.,
+ the returned cursor may never have been opened */
+OCIAttrGet_stmhp_stat2(imp_sth, (OCIStmt*)phs-desc_h,
stmt_state, 0,
+ OCI_ATTR_STMT_STATE, status);
+if (status != OCI_SUCCESS) {
+oci_error(sth, imp_sth-errhp, status, OCIAttrGet
OCI_ATTR_STMT_STATE);
+return 0;
+}
+if (DBIc_DBISTATE(imp_sth)-debug = 3
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
