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 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 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 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: Oracle ping
On 05/11/12 02:47, Greg Sabino Mullane wrote: Also by the time an application does execute some SQL, the the connection/node/server/network could have dropped and so the application 'will have to check recover from write failures at that point anyway. This is a bit of a red herring. You could say that about any database action, anytime ever. I personally think a simple select is better than relying on a low-level library call, as it does a more complete end-to-end check that is almost certainly closer to what the caller is going to use the connection for. I rather strongly disagree. It is possible to do useful work over a connection in situations when a 'simple select' is not possible. My production Oracle database is protected by a physical standby database. This database is, in Oracle terms, mounted but not open. A user with the SYSADMIN privilege can connect to the database. It is possible use virtual views to monitor the database and ALTER SYSTEM commands to change its state. However it is not possible to select from any table. SELECT 1 FROM DUAL will fail. I would like to see $dbh-ping to continue to return a true values in cases like this. -- Charles Jardine - Computing Service, University of Cambridge c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: Strange bind_param issue
On 16/08/12 14:00, Martin J. Evans wrote:
On 15/08/12 17:39, Tim Bunce wrote:
On Wed, Aug 15, 2012 at 04:14:52PM +0100, Martin J. Evans wrote:
I've just been given an rt
https://rt.cpan.org/Ticket/Display.html?id=78838 and am at a loss to
explain exactly what is happening. I wonder if anyone can help?
Some background:
DBI says for bind_param:
The bind_param method takes a copy of $bind_value and associates it
(binds it) with a placeholder
As far as I am aware DBD::ODBC does not copy the scalar given to it -
so perhaps DBI does this. The problem I'm seeing in the provided
example is the pointer passed to ODBC's SQLBindParameter at the time
bind_param is called no longer points to a valid string when execute
is called. However, changing the call to bind_param to pass $obj as
$obj appears to fix the problem.
The driver should take a copy of the value when bind_param is called.
Perhaps stored by the ParamValues attribute.
(bind_param_inout takes a reference instead and binds at execute.)
Can anyone say if DBD::ODBC should
work with either example and explain what might be happening here:
use DBI;
my $dbh =
DBI-connect(dbi:ODBC:DSN=xxx;UID=xx;PWD=yy;MARS_Connection=No;);
my $obj = new Object();
my $sql = q(SELECT ? AS result);
my $sth = $dbh-prepare($sql);
# interesting - is the following use of $obj really as a string?
# in the call to bind_param here, DBD::ODBC binds the pv ptr of the
scalar
# it is given in a SQLBindParameter call but by the time execute is
called
# the string at that address is no longer valid. I kind of expect
that as
# what to_s returns is only required in the bind_param statement and yet
# DBI says bind_param takes a copy.
# However if the following is changed to $obj it works
$sth-bind_param(1, $obj);
$sth-execute();
while (my $row = $sth-fetchrow_hashref()) {
print $row-{'result'}, \n;
}
package Object;
use overload '' = 'to_s';
sub new() { bless { }, shift };
sub to_s() { my $self = shift; ref($self); }
The driver needs to make a copy and ensure that 'get magic' is called.
SvPV will call get magic but won't make a copy. Since you need an SV for
the ParamValues attribute maybe newSVsv() followed by SvPOK_only_UTF8()
on the copy would be a suitable incantation.
Tim.
It appears dbd_bind_ph does this:
/* newvalue is the scalar param passed in to dbd_bind_ph */
/* phs is a structure to hold the param details */
if (!is_inout) {/* normal bind to take a (new) copy of current
value */
if (phs-sv == PL_sv_undef) /* (first time bind) */
phs-sv = newSV(0);
sv_setsv(phs-sv, newvalue);
} else if (newvalue != phs-sv) {
if (phs-sv)
SvREFCNT_dec(phs-sv);
phs-sv = SvREFCNT_inc(newvalue); /* point to live var */
}
so as far as I can see a copy is taken but I don't think sv_setsv does
any magic. Similar code exists in DBD::Oracle and a number of other DBDs
I looked at. DBD::ODBC does use SvPV later so I'm guessing that is where
the magic happens and the scalar is stringified.
This made me wonder what happens if you pass a reference without any
AMAGIC and DBD::ODBC allowed that but you got rubbish so I've now
disallowed references without AMAGIC.
So now I'm only not sure why in the example provided what passing $obj
(and not $obj) is doing. it appears when it gets the sv_setsv above
SvPV on phs-sv is 'Object' and it is address passed to SQLBindParameter
but later when execute is called the contents of that address have
changed. It seems every time you call SvPV on the copied scalar which is
the object you get a different address and because DBD::ODBC binds at
bind time and not execute time by execute time the original address has
changed.
I thought it might be interesting to see what DBD::Oracle does.
I ran
use strict;
use DBI;
my $dbh =
DBI-connect(dbi:Oracle:, '', '');
my $obj = new Object();
my $sql = q(SELECT ? AS result from dual);
my $sth = $dbh-prepare($sql);
$sth-bind_param(1, $obj);
$sth-execute();
while (my $row = $sth-fetchrow_hashref()) {
print $row-{'RESULT'}, \n;
}
package Object;
use overload '' = 'to_s';
sub new() { bless { }, shift };
sub to_s() { my $self = shift; ref($self); }
and it printed
Object
--
Charles Jardine - Computing Service, University of Cambridge
c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: Strange bind_param issue
On 16/08/12 16:37, Martin J. Evans wrote:
On 16/08/12 16:13, Charles Jardine wrote:
I thought it might be interesting to see what DBD::Oracle does.
I ran
use strict;
use DBI;
my $dbh =
DBI-connect(dbi:Oracle:, '', '');
my $obj = new Object();
my $sql = q(SELECT ? AS result from dual);
my $sth = $dbh-prepare($sql);
$sth-bind_param(1, $obj);
$sth-execute();
while (my $row = $sth-fetchrow_hashref()) {
print $row-{'RESULT'}, \n;
}
package Object;
use overload '' = 'to_s';
sub new() { bless { }, shift };
sub to_s() { my $self = shift; ref($self); }
and it printed
Object
Because DBD::Oracle does not bind the parameters until execute time
whereas DBD::ODBC binds them at bind_param time. If I change DBD::ODBC
to bind at execute time (there is an internal flag defer_bind which is
no longer used) I get the same result. I'm trying to understand why this
makes a difference here.
The following test re-blesses $obj between the bind and the execute.
It still prints 'Object' even though, at the time of the execute,
$obj is of class Subject. This demonstrates that DBD::Oracle does not
just copy the reference value it has been given. If must do the actual
stringifcation as bind time.
use strict;
use DBI;
my $obj = Object-new;
my $dbh = DBI-connect('dbi:Oracle:', '', '');
my $sth = $dbh-prepare(select ? from dual);
$sth-bind_param(1, $obj);
bless $obj, 'Subject';
$sth-execute;
print scalar $sth-fetchrow_array(), \n;
package Object;
use overload '' = 'to_s';
sub new() { bless { }, shift };
sub to_s() { my $self = shift; ref($self); }
package Subject;
use overload '' = 'to_s';
sub new() { bless { }, shift };
sub to_s() { my $self = shift; ref($self); }
--
Charles Jardine - Computing Service, University of Cambridge
c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: Strange bind_param issue
On 15/08/12 16:14, Martin J. Evans wrote:
I've just been given an rt
https://rt.cpan.org/Ticket/Display.html?id=78838 and am at a loss to
explain exactly what is happening. I wonder if anyone can help?
Some background:
DBI says for bind_param:
The bind_param method takes a copy of $bind_value and associates it
(binds it) with a placeholder
As far as I am aware DBD::ODBC does not copy the scalar given to it - so
perhaps DBI does this. The problem I'm seeing in the provided example is
the pointer passed to ODBC's SQLBindParameter at the time bind_param is
called no longer points to a valid string when execute is called.
However, changing the call to bind_param to pass $obj as $obj appears
to fix the problem. Can anyone say if DBD::ODBC should work with either
example and explain what might be happening here:
use DBI;
my $dbh =
DBI-connect(dbi:ODBC:DSN=xxx;UID=xx;PWD=yy;MARS_Connection=No;);
my $obj = new Object();
my $sql = q(SELECT ? AS result);
my $sth = $dbh-prepare($sql);
# interesting - is the following use of $obj really as a string?
# in the call to bind_param here, DBD::ODBC binds the pv ptr of the scalar
# it is given in a SQLBindParameter call but by the time execute is called
# the string at that address is no longer valid. I kind of expect that as
# what to_s returns is only required in the bind_param statement and yet
# DBI says bind_param takes a copy.
# However if the following is changed to $obj it works
$sth-bind_param(1, $obj);
$sth-execute();
while (my $row = $sth-fetchrow_hashref()) {
print $row-{'result'}, \n;
}
package Object;
use overload '' = 'to_s';
sub new() { bless { }, shift };
sub to_s() { my $self = shift; ref($self); }
Output when using $obj:
value passed to DBD::ODBC's bind_param = Object
pointer at execute time seems to point to rubbish
output of script: 8�k�8
When $obj passed to bind_param
value passed to DBD::ODBC's bind_param = Object
pointer at execute time points to Object
output of script: Object
As a quick test I did the following and it seems to work so I guess
there is something about the above Perl I don't get.
$obj is not a string. It is an object of a class which has a
stringify operator. $obj is a string, because ... stringifies.
It is not at all clear how the DBI should take a copy of an object.
I think this is a case of user error.
--
Charles Jardine - Computing Service, University of Cambridge
c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: DBD::Oracle
On 10/02/12 14:56, H.Merijn Brand wrote: On Fri, 10 Feb 2012 14:30:03 +, Charles Jardine c...@cam.ac.uk wrote: On 10/02/12 13:32, H.Merijn Brand wrote: Preparing a new database machine ... Do I need to worry? t/10general.t 1/30 # Failed test 'system exit 1 should return 256' # at t/10general.t line 41. # got: '-1' # expected: '256' # Failed test 'system exit 0 should return 0' # at t/10general.t line 42. # got: '-1' # expected: '0' t/10general.t 3/30 # Looks like you failed 2 tests of 30. t/10general.t Dubious, test returned 2 (wstat 512, 0x200) This symptom indicates that the system built-in function is not working. Try perl -e 'print system(exit 1;), \n' $ perl -e 'print system(exit 1;), \n' 256 If this reproduces the problem, you have something nothing to do with databases to investigate. If it doesn't reproduce the problem, it may be that Oracle is messing with the SIGCHLD signal. Are you connecting directly to the database using the bequeather? I've never heard of anything called a bequeather :) If you call $ORACLE_HOME/bin/adapters, the list will include 'BEQ'. This is the bequeather, which is the adapter used to connect to a local database when ORACLE_HOME and ORACLE_SID are specified and TWO_TASK is not. The BEQ adapter is inconsistent with the with perl built-ins which use fork or popen unless you put 'bequeath_detach = yes' in sqlnet.ora. It is a pity that this sqlnet.ora option is not the default. If so, try connecting via SQL*Net instead. If avoiding the bequeather fixes the problem, try putting 'bequeath_detach = yes' in your sqlnet.ora file. This should allow you to use the bequeather and system at the same time. above test was without TWO_TASK. Using the listener, I get All tests successful. Files=35, Tests=2008, 31 wallclock secs ( 0.40 usr 0.04 sys + 3.69 cusr 1.28 csys = 5.41 CPU) Result: PASS PERL_DL_NONLAZY=1 /pro/bin/perl -Iblib/lib -Iblib/arch test.pl -- Charles Jardine - Computing Service, University of Cambridge c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: Removal of Oraperl from DBD::Oracle?
On 04/01/12 19:09, Yanick Champoux wrote: Hi all, For a long time now, the documentation of DBD::Oracle had been warning that Oraperl is deprecated. A few releases back, I ratcheted up the warning and mentioned that Oraperl would be dropped from the DBD::Oracle distro in the new year, after v1.38. And now that the new year and v1.38 are upon us, I'm thinking on acting on the promise. But before I do, wisdom begs me to consult the dbi hivemind. Sooo... Can anybody know of anyone/anything who would be impacted (negatively) by the removal of said Oraperl? I started working with Oracle in 1993. At that time I wrote some simple utilities in oraperl. I read Yanick's recent warning, and reacted to it by wrapping my copies of Oraperl.pm with logging script. To my surprise, I found six or seven little oraperl scripts which were still in use, some in critical applications! I have now converted these scripts, and I am ready for the demise of Oraperl.pm. I am completely in favour of the abolition of oraperl, but I think it would be kind to start with a version which emits a warning message from the main line of Oraperl.pm, so any unfortunate end users of oraperl will have some time to convert, or seek advice. -- Charles Jardine - Computing Service, University of Cambridge c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: Problem with Oracle collections/objects
On 12/12/11 20:24, Martin J. Evans wrote:
On 12/12/2011 18:15, Charles Jardine wrote:
On 12/12/11 16:13, Martin J. Evans wrote:
On 09/12/11 18:02, Charles Jardine wrote:
On 09/12/11 14:01, Martin J. Evans wrote:
Hi,
If anyone is around who wrote or has worked on the object/collections
support in DBD::Oracle I'd greatly appreciate it if you could take a
quick look at this problem as the code in DBD::Oracle for this has
defeated me so far.
The problem is I have a query which uses types and collect and
although it works fine initially once it has been run a number of
times in the same connection it eventually goes from a 3s fetch time
to a number of minutes. I have reduced it to the example below.
I can run this code all day long in sqlplus without a problem so I
don't think it is an Oracle issue.
I have been re-writing parts of the of the object/collections
support with a view to correcting aspects of the storage management.
The existing code has some store drains, and some incorrect
freeing of Perl SVs, which can lead to crashes.
The changes I have made so far do not fix your problem, but
the problem might provide me with a useful test case.
I shall stare at the prepare/destroy code a bit more before
I give up.
I'm juggling a few problems right now but we are not going to let this one go.
I think I have found the resource drain which causes your slow-down
problem. Buffers implicitly allocated in the cache by OCIDefineObject()
need to be deallocated explicitly by OCIObjectFree() 'when they are no
longer needed'. This is counter-intuitive, but it is documented
in the reference description of OCIDefineObject(), and has been since
Oracle 8.
I have attached a demonstration patch against the trunk for you to test.
This patch is not fit to go into service. We need a logging macro for
OCIObjectFree().
If at any stage, you want a tester for any changes you've made to the
code please put me in the front of the queue.
Now I have found this resource drain, I feel happier about submitting
a patch intended to remove all resource drains from the object/collection
stuff. Expect me to be offering you something bigger to test after the
Christmas break.
--
Charles Jardine - Computing Service, University of Cambridge
c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Index: dbdimp.c
===
--- dbdimp.c(revision 15051)
+++ dbdimp.c(working copy)
@@ -3967,8 +3967,11 @@
sv_free(fbh-name_sv);
if (fbh-desc_h)
OCIDescriptorFree_log(fbh-desc_h, fbh-desc_t);
- if (fbh-obj)
+ if (fbh-obj) {
+ if (fbh-obj-obj_value)
+ OCIObjectFree(fbh-imp_sth-envhp, fbh-imp_sth-errhp,
fbh-obj-obj_value, (ub2)0);
Safefree(fbh-obj);
+ }
}
Re: Problem with Oracle collections/objects
On 12/12/11 16:13, Martin J. Evans wrote:
On 09/12/11 18:02, Charles Jardine wrote:
On 09/12/11 14:01, Martin J. Evans wrote:
Hi,
If anyone is around who wrote or has worked on the object/collections
support in DBD::Oracle I'd greatly appreciate it if you could take a
quick look at this problem as the code in DBD::Oracle for this has
defeated me so far.
The problem is I have a query which uses types and collect and
although it works fine initially once it has been run a number of
times in the same connection it eventually goes from a 3s fetch time
to a number of minutes. I have reduced it to the example below.
I can run this code all day long in sqlplus without a problem so I
don't think it is an Oracle issue.
I have been re-writing parts of the of the object/collections
support with a view to correcting aspects of the storage management.
The existing code has some store drains, and some incorrect
freeing of Perl SVs, which can lead to crashes.
The changes I have made so far do not fix your problem, but
the problem might provide me with a useful test case.
Do you find, as I do, that you can chase your problem away by
adding 'NOT FINAL' to the end of each of the specifications
of the two objects (as opposed to collections)? I find this
helps with my version of the code. This intrigues me.
I would expect non-final types to be handled worse, not better.
The problem does appear to go away when NOT FINAL is added.
However, it is slower when not final is used.
I'd still like to track this down.
I wasn't suggesting NOT FINAL as possible solution to your problem.
It is a waste of resources to declare an object type NON FINAL unless
you actually need to use sub-types. I asked you to test NON FINAL
because I had expected to make matters worse, and was very surprised
when it did not.
When I run your test script, the first 36 iterations are quick
(2 secs) and the 37th is slow (45 secs).
I have found I can chase the problem away in two different
ways. The slow-down does not occur if I change the script so it
uses the same statement handle for all iterations rather than
preparing a new one each time. This suggests that, if there is
a resource drain of any sort, the drain might be associated with
prepare/destroy rather than with fetch.
Another way removing the slow-down is to re-authenticate,
with $dbh-func('', '', 'reauthenticate'), after
each iteration. This causes Oracle to create a new session
within the existing connection. Re-authentication is much
faster than creating a new connection. This might provide
a workaround for you.
All that said, I still don't have any real clue as to what
is going on. I had suspected that the pin counts on TDOs
(Type Descriptor Objects) might be a problem, but adding
the OCIObjectUnpin calls I thought might be necessary made
no difference.
I shall stare at the prepare/destroy code a bit more before
I give up.
--
Charles Jardine - Computing Service, University of Cambridge
c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: Problem with Oracle collections/objects
On 09/12/11 14:01, Martin J. Evans wrote: Hi, If anyone is around who wrote or has worked on the object/collections support in DBD::Oracle I'd greatly appreciate it if you could take a quick look at this problem as the code in DBD::Oracle for this has defeated me so far. The problem is I have a query which uses types and collect and although it works fine initially once it has been run a number of times in the same connection it eventually goes from a 3s fetch time to a number of minutes. I have reduced it to the example below. I can run this code all day long in sqlplus without a problem so I don't think it is an Oracle issue. I have been re-writing parts of the of the object/collections support with a view to correcting aspects of the storage management. The existing code has some store drains, and some incorrect freeing of Perl SVs, which can lead to crashes. The changes I have made so far do not fix your problem, but the problem might provide me with a useful test case. Do you find, as I do, that you can chase your problem away by adding 'NOT FINAL' to the end of each of the specifications of the two objects (as opposed to collections)? I find this helps with my version of the code. This intrigues me. I would expect non-final types to be handled worse, not better. -- Charles Jardine - Computing Service, University of Cambridge c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: DBD::Oracle Release Candidate 1
On 17/02/11 12:34, H.Merijn Brand wrote:
On Wed, 16 Feb 2011 12:32:12 +0100, H.Merijn Brand
h.m.br...@xs4all.nl wrote:
http://www.pythian.com/news/wp-content/uploads/DBD-Oracle-1.28_RC_1.zip
You need to work on longdouble support I guess
I wrote the tests in question. I included object properties of type
DATE and NUMERIC to provide tests against regression of some changes
I had made to get_attr_val to fix type mismatches.
I realise that I made a mistake by using literal Perl floating point
numbers in the insert statements. Doing it this way means that, on
the route into Oracle, the number is converted first into Perl's binary
floating point format and then into Oracle's base-100 format.
(I was only interested in testing the route out of Oracle.)
I think the best fix is simply to use strings in the insert statements.
Change 12345.6789 to '12345.6789' and 777.666 to '777.666'. This
way, the conversions both ways will be done by Oracle. Conversion of
a decimal string representation to a base-100 representation of adequate
precision will be exact. Nothing will be hidden under the carpet.
I should have written it like this originally. I am sorry.
Here's a patch to make the test PASS on all systems, but I'm not sure
if I'm using a carpet to shuv the problems under ...
--- t/58object.t.org2011-02-17 13:33:48.0 +0100
+++ t/58object.t2011-02-17 13:33:25.0 +0100
@@ -82,9 +82,9 @@ $dbh-do(qq{ INSERT INTO $table VALUES (
or die $dbh-errstr;
$dbh-do(qq{ INSERT INTO $table VALUES (2, $sub_type(NULL, 'obj2',
TO_DATE('2004-11-30 14:27:18', '-MM-DD HH24:MI:SS'),
-12345.6789)) }
+12345.9375)) }
) or die $dbh-errstr;
-$dbh-do(qq{ INSERT INTO $table VALUES (3, $sub_type(5, 'obj3', NULL,
777.666)) }
+$dbh-do(qq{ INSERT INTO $table VALUES (3, $sub_type(5, 'obj3', NULL,
777.875)) }
) or die $dbh-errstr;
$dbh-do(qq{ CREATE OR REPLACE TYPE $inner_type AS OBJECT (
@@ -159,14 +159,14 @@ ok (scalar @row2, 'new: Fetch second row
cmp_ok(ref $row2[1], 'eq', 'DBD::Oracle::Object', 'new: Row 2 column 2 is an
DBD::Oracle::Object');
cmp_ok(uc $row2[1]-type_name, eq, uc $schema.$sub_type, new: Row 2 column 2
object type);
is_deeply([$row2[1]-attributes], ['NUM', undef, 'NAME', 'obj2',
-'DATETIME', '2004-11-30T14:27:18', 'AMOUNT', '12345.6789'], new: Row 1
column 2 object attributes);
+'DATETIME', '2004-11-30T14:27:18', 'AMOUNT', '12345.9375'], new: Row 1
column 2 object attributes);
@row3 = $sth-fetchrow();
ok (scalar @row3, 'new: Fetch third row');
cmp_ok(ref $row3[1], 'eq', 'DBD::Oracle::Object', 'new: Row 3 column 2 is an
DBD::Oracle::Object');
cmp_ok(uc $row3[1]-type_name, eq, uc $schema.$sub_type, new: Row 3 column 2
object type);
is_deeply([$row3[1]-attributes], ['NUM', 5, 'NAME', 'obj3',
-'DATETIME', undef, 'AMOUNT', '777.666'], new: Row 1 column 2 object
attributes);
+'DATETIME', undef, 'AMOUNT', '777.875'], new: Row 1 column 2 object
attributes);
ok (!$sth-fetchrow(), 'new: No more rows expected');
@@ -178,7 +178,7 @@ my $expected_hash = {
NUM = 5,
NAME= 'obj3',
DATETIME= undef,
-AMOUNT = 777.666,
+AMOUNT = 777.875,
};
is_deeply($obj-attr_hash, $expected_hash, 'DBD::Oracle::Object-attr_hash');
is_deeply($obj-attr, $expected_hash, 'DBD::Oracle::Object-attr');
--
Charles Jardine - Computing Service, University of Cambridge
c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: Question about passing a stored procedure file to DBD-Oracle
On 11/02/11 17:33, tran...@commerceinsurance.com wrote:
I'm sorry in advance if this is not the correct forum/mechanism to ask
this question - I have been googling this for a while, as well as
re-reading all of the CPAN man page for DBD-Oracle.
I need to pass a physical file( or the contents of that file), that
contains code to create an Oracle stored procedure into DBD-Oracle. So
like SQL PLus, where I'd say @package.spb, I have been trying to do the
same in every way imaginable with $DBH-prepare / do. As of right now,
this is not working:
# load file contents
open (FILEDATA, $sql);
while ($record = FILEDATA) {
print $record;
}
close(FILEDATA);
You have opened the file named $sql, read it line-by line,
printing each line as yo go.
$record is now undefined. Passing it to prepare won't work.
# prepare the stored procedure
if ($sth = $DBH-prepare( $record )){
print $LOG prepared: $HoH{$filename}{'full_filename'} \n;
--
Charles Jardine - Computing Service, University of Cambridge
c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: $SIG{INT} tampering during DBI-connect on Oracle
On 07/11/10 20:14, Tim Bunce wrote: On Wed, Nov 03, 2010 at 10:29:18PM +0100, Jens Rehsack wrote: 2010/11/3 Martin J. Evans martin.ev...@easysoft.com: [...] From time to time something similar to Brian's post turns up on this list although it is usually something to do with pipes not working as you expect. I have to say I would be strongly against returning all signals to their original state after a connect call. I know for a fact that some oracle clients (depending on how you connect to Oracle) install a SIGCHLD handler for instance. Who is to say what operations in the Oracle client library might be rendered useless if we destroy any signal handlers it has set up. There is an entry in Oracle Knowledge Base about this. I had trouble with this in a project around a year ago - and under specific circumstances it's safe to restore the handlers and in others you should chain them (when you need them, too). Search for SIGCHLD in the knowledge base (I didn't took the URI with me when I left the contractor). The topic has also come up on the dbi mailing lists several times over the years. Both for SIGCHLD and SIGINT. I am aware of the SIGCHLD problem, which affects only connections using the BEQ connection method, being a fre1quently asked questions. The solution is to add BEQUEATH_DETACH=YES to the client side sqlnet.ora. I have found several threads about SIGINT, but all seem to peter out without reaching a conclusion. Is it really worth changing DBD::Oracle for what effectively is a one liner? No, because it's not safe for each Oracle configuration. It must keep up to the developer to figure out when it's safe. I think we should try to fix/workaround at least the SIGINT issue. The default behaviour should be to act in the most reasonable/useful way. An option could be provided to restore the current unreasonable way. I can say whether a workaround would be a good idea without understanding exactly what the problem is. I can't reproduce the original poster's problem, and I don't know why. -- Charles Jardine - Computing Service, University of Cambridge c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: $SIG{INT} tampering during DBI-connect on Oracle
On 03/11/10 19:16, Brian Phillips wrote:
On Wed, Nov 3, 2010 at 9:55 AM, Charles Jardine c...@cam.ac.uk wrote:
I simply do not see this effect. The following program:
#!/usr/local/bin/perl -w
use DBI;
my $dbh = DBI-connect(dbi:Oracle:, oralogin) or die Failed\n;
$| = 1;
sleep 1, print('.') while 1;
prints dots every second until I press ^C, at which point it terminates.
There must be something different about your environment. For reference, I
have Oracle 10.2.0.4.6, Perl 5.10.1, DBI 1.609, DBD::Oracle 1.24. The
OS is SLES 10 SP 3. It makes no difference whether I use the bequeather
or a TNS connection.
I can use your exact script and it doesn't respond to a CTRL-C. The
only difference is that I'm specifying a username and password in the
DBI-connect call but I wouldn't think that would make any difference.
Our versions are similar:
perl 5.10.1
DBI 1.611
DBD::Oracle 1.24
Oracle Client 11.2.0.1.0
It might be a difference between the Oracle 10 and Oracle 11 clients.
Can someone who has easy access to both confirm to deny this?
--
Charles Jardine - Computing Service, University of Cambridge
c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: $SIG{INT} tampering during DBI-connect on Oracle
On 02/11/10 18:49, Brian Phillips wrote:
Hello - I recently discovered that when we do a DBI-connect to an Oracle
database, the process no longer responds to SIGINT signals.
I simply do not see this effect. The following program:
#!/usr/local/bin/perl -w
use DBI;
my $dbh = DBI-connect(dbi:Oracle:, oralogin) or die Failed\n;
$| = 1;
sleep 1, print('.') while 1;
prints dots every second until I press ^C, at which point it terminates.
This variant:
#!/usr/local/bin/perl -w
use DBI;
$SIG{INT} = sub { die Interrupted\n };
my $dbh = DBI-connect(dbi:Oracle:, oralogin) or die Failed\n;
$| = 1;
sleep 1, print('.') while 1;
writes Interrupted\n when I press ^C.
There must be something different about your environment. For reference, I
have Oracle 10.2.0.4.6, Perl 5.10.1, DBI 1.609, DBD::Oracle 1.24. The
OS is SLES 10 SP 3. It makes no difference whether I use the bequeather
or a TNS connection.
--
Charles Jardine - Computing Service, University of Cambridge
c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: DBD::Oracle::db prepare failed: ORA-00932: inconsistent datatypes
On 05/08/10 12:49, H.Merijn Brand wrote: Snip $dbh-do (qq; create global temporary table t_foo ( h_key blob, h_value blob ); ); my $sth = $dbh-prepare (select h_value from t_foo where h_key = ?); $ perl test.pl DBD::Oracle::db prepare failed: ORA-00932: inconsistent datatypes: expected - got BLOB (DBD ERROR: error possibly near * indicator at char 32 in 'select h_value from t_foo where *h_key = :p1') [for Statement select h_value from t_foo where h_key = ?] at test.pl line 23. This is nothing to do with perl. You get this message from Oracle if you attempt to compare a blob with anything, including itself. Here is a simple demonstration using SQLPlus. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production SQL create table t_foo (h_key blob); Table created. SQL select h_key from t_foo where h_key = h_key; select h_key from t_foo where h_key = h_key * ERROR at line 1: ORA-00932: inconsistent datatypes: expected - got BLOB It is a bad error message, but the rule is clear. This is from the section on Comparison Conditions in the Oracle 10.2 SQL Reference Manual Large objects (LOBs) are not supported in comparison conditions. However, you can use PL/SQL programs for comparisons on CLOB data. -- Charles Jardine - Computing Service, University of Cambridge c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: ANNOUNCE: DBD::Oracle 1.24 Release Candidate 5
On 28/01/10 15:59, John Scoles wrote: Well here comes the big #5 It can be found at the usual place http://svn.perl.org/modules/dbd-oracle/trunk/DBD-Oracle-1.24-RC5.tar My environment is Linux x86-64, Perl 5.10.1 (64 bit), DBI 1.609, Oracle 10.2.0.4.2 (64 bit). Database charset UTF8, national charset AL16UTF16. RC5 compiles without warnings and passes all its tests, including the regression tests for my object patches. I have run some sample work at trace level 15 - there are no segfaults. In short, I can't find anything wrong with it. -- Charles Jardine - Computing Service, University of Cambridge c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: ANNOUNCE: DBD::Oracle 1.24 Release Candidate 5
On 29/01/10 13:18, John Scoles wrote: Horray. Just one more thing has come up in like the last 15min can you redo your the make test with the following file http://svn.perl.org/modules/dbd-oracle/trunk/ocitrace.h It is a trivial change but just want to make sure it does not break anything It still compiles without warnings and passes all its tests. if is a change to precision for OCIDateTimeToText to 6 instead of 0 for varrays of timestamps needed to conduct a large scale experiment of some sort cheers John Scoles Charles Jardine wrote: On 28/01/10 15:59, John Scoles wrote: Well here comes the big #5 It can be found at the usual place http://svn.perl.org/modules/dbd-oracle/trunk/DBD-Oracle-1.24-RC5.tar My environment is Linux x86-64, Perl 5.10.1 (64 bit), DBI 1.609, Oracle 10.2.0.4.2 (64 bit). Database charset UTF8, national charset AL16UTF16. RC5 compiles without warnings and passes all its tests, including the regression tests for my object patches. I have run some sample work at trace level 15 - there are no segfaults. In short, I can't find anything wrong with it. -- Charles Jardine - Computing Service, University of Cambridge c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: Bug in tracing in DBD::Oracle
On 27/01/10 17:38, Martin Evans wrote: Charles Jardine wrote: On 27/01/10 15:52, Martin Evans wrote: Hi, I was asked to enable ora_verbose and send a trace a few days ago. I'm getting a segfault with DBD::Oracle when ora_verbose or dbd_verbose is set to 15 in the connect method call. The stack trace is: [snip] and that refers to the following line in dbdimp.c: OCINlsEnvironmentVariableGet_log_stat( ncharsetid,(size_t) 0, OCI_NLS_NCHARSET_ID, 0, rsize ,status ); Oracle defines the second argument as size_t so I guess that cast of 0 to size_t is ok but ocitrace.h then goes on to cast it again to (unsigned long long) and the format argument has been changed to %llu. Although these match it segfaults. I am responsible for this change. It was part of a campaign to avoid warnings when compiling on 64-bit gcc platforms. All that is necessary to avoid the compiler warnings is that the format arguments match the casts (subject to integral promotion). I used (unsigned long long) in this case for maximum portability. I couldn't find any standard that said that (size_t) might not be wider than (unsigned long). If my change breaks PerlIO_vprintf, we must back off. Using (unsigned long) and %lu would work on all platforms I use. Using (unsigned int) and %u, would work in this case, but not for all uses of size_t. This is the only place where I used a %llu or %lld, so there is only one place to change. Martin, can you try changing the casts to (unsigned long) and the formats to %lu, and see if this fixes your problem. That is what I did in effect (nearly). I took the casts of 0 to size_t out of the 2 calls in dbdimp.c and added a cast to size_t on the real call to oracle in the macro. Then I change the format in the PerlIO_printf to %lu and change the cast to (unsigned long). This works for me and I guess it will work without warning for you too. This isn't exactly what John has in subversion at the moment. John seems to have corrected my over-zealous cast, and produced a version which complies without warning and works on both 32- and 64-bit platforms. Thank you John. I prefer his version, with the cast to site_t left where it was, rather than imported into the macro. If the current SVN version works for Martin, I suggest that no more needs to be done. I an sorry to have caused this bother. -- Charles Jardine - Computing Service, University of Cambridge c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: Bug in tracing in DBD::Oracle
On 27/01/10 15:52, Martin Evans wrote: Hi, I was asked to enable ora_verbose and send a trace a few days ago. I'm getting a segfault with DBD::Oracle when ora_verbose or dbd_verbose is set to 15 in the connect method call. The stack trace is: (gdb) bt #0 0x080be45c in Perl_sv_vcatpvfn () #1 0x080ccd6d in Perl_vnewSVpvf () #2 0x0811cb54 in PerlIO_vprintf () #3 0x0811cbdf in PerlIO_printf () #4 0x007e961c in ora_db_login6 (dbh=0x830f6a0, imp_dbh=0x834b0b0, dbname=value optimised out, uid=0x81aedf8 bet, pwd=0x81aee08 b3t, attr=0x830ee20) at dbdimp.c:546 #5 0x007dd0e0 in XS_DBD__Oracle__db__login (my_perl=0x8188008, cv=0x8344b88) at ./Oracle.xsi:100 #6 0x080b12c0 in Perl_pp_entersub () #7 0x080af688 in Perl_runops_standard () #8 0x080acf4b in Perl_call_sv () #9 0x00575f0a in XS_DBI_dispatch (my_perl=0x8188008, cv=0x82bfa88) at DBI.xs:3442 #10 0x080b12c0 in Perl_pp_entersub () #11 0x080af688 in Perl_runops_standard () #12 0x080adbb2 in perl_run () #13 0x08063ffd in main () and that refers to the following line in dbdimp.c: OCINlsEnvironmentVariableGet_log_stat( ncharsetid,(size_t) 0, OCI_NLS_NCHARSET_ID, 0, rsize ,status ); Oracle defines the second argument as size_t so I guess that cast of 0 to size_t is ok but ocitrace.h then goes on to cast it again to (unsigned long long) and the format argument has been changed to %llu. Although these match it segfaults. I am responsible for this change. It was part of a campaign to avoid warnings when compiling on 64-bit gcc platforms. All that is necessary to avoid the compiler warnings is that the format arguments match the casts (subject to integral promotion). I used (unsigned long long) in this case for maximum portability. I couldn't find any standard that said that (size_t) might not be wider than (unsigned long). If my change breaks PerlIO_vprintf, we must back off. Using (unsigned long) and %lu would work on all platforms I use. Using (unsigned int) and %u, would work in this case, but not for all uses of size_t. This is the only place where I used a %llu or %lld, so there is only one place to change. Martin, can you try changing the casts to (unsigned long) and the formats to %lu, and see if this fixes your problem. This segfaults on my Linux machine described with the Perl -V output below. I cannot believe the size of the first argument passed to OCINlsEnvironmentVariableGet is every going to need a size_t and in any case it has a max size of OCI_NLS_MAXBUFSZ (100 in Instant Client 11.1 for Linux X86). -- Charles Jardine - Computing Service, University of Cambridge c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: ANNOUNCE: DBD::Oracle 1.24 Release Candidate 3
On 14/01/10 12:19, Charles Jardine wrote:
My environment is Linux x86-64, Perl 5.10.1 (64 bit), DBI 1.609,
Oracle 10.2.0.4.2 (64 bit). Database charset UTF8, national
charset AL16UTF16
Three things:
[ snip ]
2. Back in early October, I reported a problem with the support for
objects. Specifically, fetching a column value which was a nested
table of objects was failing if $dbh-{ora_objects} was true.
I have found the cause of the problem and have developed a patch.
Unfortunately, while investigating this I have found another serious
problem with the object support. Fetching a column value which
is an object, one of whose properties is an object, segfaults,
regardless of the value of $dbh-{ora_objects}. The cause of this
looks like a simple type error.
I hope to be able to post a patch which fixes both these problems
within the next few days.
I have attached a patch which fixes the first problem. I will
post another message about the second problem, which I no longer
see as easy to fix.
I should explain what the patch does, and why.
There are two calls of OCITypeByRef in oci8.c. The second one,
which is in get_object, is the one which was problematic. The purpose
of the call is to obtain the true type of a fetched object. This is
to deal with the possibility that the fetched object's type is
a subtype of the type obtained at describe time.
In the example case I posted back in October this call fails badly;
see http://www.mail-archive.com/dbi-us...@perl.org/msg32378.html.
With 1.23 the failure was a segfault, with 1.24-RC3 the message is
OCI-21500: internal error code, arguments: [kocgpn129].
I now know that it is significant that the type of my example
object was a FINAL type. FINAL types can have no subtypes.
If I change my example by adding NOT FINAL to the to the CREATE TYPE
statement for the object, the problem goes away - the OCITypeByRef
call succeeds.
If the type found at describe time is FINAL, all the fetched
instances must be of the described type, since subtypes are
not possible. So, in this case, it is a correct optimisation
to skip the block of code containing the call of OCITypeByRef.
The patch implements this optimisation, and so circumvents the
problem.
The patch also adds a call of OCIObjectFree, to match the
call of OCIObjectNew in the same code sequence. I think this
removes a small per-row store drain.
--
Charles Jardine - Computing Service, University of Cambridge
c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Only in DBD-Oracle-1.24-RC3.object_patches: Makefile.old
diff -ur DBD-Oracle-1.24-RC3/dbdimp.h
DBD-Oracle-1.24-RC3.object_patches/dbdimp.h
--- DBD-Oracle-1.24-RC3/dbdimp.h2010-01-08 19:39:46.0 +
+++ DBD-Oracle-1.24-RC3.object_patches/dbdimp.h 2010-01-15 13:00:44.567065736
+
@@ -141,7 +141,7 @@
};
-typedef struct fbh_obj_st fbh_obj_t; /*Ebbedded Object Descriptor */
+typedef struct fbh_obj_st fbh_obj_t; /*Embedded Object Descriptor */
struct fbh_obj_st { /* embedded object or table will work recursively*/
text*type_name; /*object's name
(TDO)*/
@@ -152,6 +152,7 @@
OCITypeCode typecode; /*object's
OOCI_ATTR_TYPECODE */
OCITypeCode col_typecode; /*if collection this is its
OCI_ATTR_COLLECTION_TYPECODE */
OCITypeCode element_typecode; /*if collection this is its
element's OCI_ATTR_TYPECODE*/
+ ub1 is_final_type; /*object's
OCI_ATTR_IS_FINAL_TYPE*/
OCIRef *obj_ref; /*if an embeded
object this is ref handle to its TDO*/
OCIInd *obj_ind; /*Null
indictator for object */
OCIComplexObject *obj_value;/*the actual value from the DB*/
diff -ur DBD-Oracle-1.24-RC3/oci8.c DBD-Oracle-1.24-RC3.object_patches/oci8.c
--- DBD-Oracle-1.24-RC3/oci8.c 2010-01-08 19:39:46.0 +
+++ DBD-Oracle-1.24-RC3.object_patches/oci8.c 2010-01-15 13:04:43.444036727
+
@@ -2205,9 +2205,9 @@
case OCI_TYPECODE_OPAQUE: /*doesn't do anything though*/
if (ora_objects){
- OCIRef *type_ref=0;
sword status;
- if (!instance_tdo) {
+ if (!instance_tdo !obj-is_final_type) {
+ OCIRef *type_ref=0;
status =
OCIObjectNew(fbh-imp_sth-envhp, fbh-imp_sth-errhp, fbh-imp_sth-svchp,
OCI_TYPECODE_REF, (OCIType *)0,
(dvoid *)0, OCI_DURATION_DEFAULT, TRUE,
@@ -2229,6 +2229,15 @@
oci_error(sth,
fbh
Re: ANNOUNCE: DBD::Oracle 1.24 Release Candidate 3
-1.24-RC3.warning_patches/ocitrace.h --- DBD-Oracle-1.24-RC3/ocitrace.h 2010-01-11 22:44:49.0 + +++ DBD-Oracle-1.24-RC3.warning_patches/ocitrace.h 2010-01-14 10:38:09.520304633 + @@ -222,8 +222,8 @@ stat = OCINlsEnvironmentVariableGet(valp, size, item, charset, rsizep ); \ (DBD_OCI_TRACEON) \ ? PerlIO_printf(DBD_OCI_TRACEFP,\ -%sNlsEnvironmentVariableGet(%d,%d,%d,%d,%d)=%s\n,\ -OciTp, *valp, size, item, charset, *rsizep, oci_status_name(stat)),stat \ +%sNlsEnvironmentVariableGet(%d,%llu,%d,%d,%llu)=%s\n,\ +OciTp, *valp, (unsigned long long)size, item, charset, (unsigned long long)*rsizep, oci_status_name(stat)),stat \ : stat /* added by lab */ -- Charles Jardine - Computing Service, University of Cambridge c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: ANNOUNCE: DBD::Oracle 1.24 Release Candidate 3
On 14/01/10 12:19, Charles Jardine wrote:
On 12/01/10 12:07, John Scoles wrote:
Ok third time is a Charm
The Third RC of the beer edition of DBD::Oracle 1.24 can be found at
http://svn.perl.org/modules/dbd-oracle/trunk/DBD-Oracle-1.24-RC3.tar
This round has a few little patches from Martin Evans on it.
Please test and enjoy
My environment is Linux x86-64, Perl 5.10.1 (64 bit), DBI 1.609,
Oracle 10.2.0.4.2 (64 bit). Database charset UTF8, national
charset AL16UTF16
Three things:
[snip]
3. Here is a patch which removes the remaining warnings detected by
gcc in 64-bit mode.
[snip]
I realise that something has wrapped the very long lines in the patch,
so I am trying again, sending the patch as an attachment.
--
Charles Jardine - Computing Service, University of Cambridge
c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
diff -ur DBD-Oracle-1.24-RC3/dbdimp.c
DBD-Oracle-1.24-RC3.warning_patches/dbdimp.c
--- DBD-Oracle-1.24-RC3/dbdimp.c2010-01-08 19:39:46.0 +
+++ DBD-Oracle-1.24-RC3.warning_patches/dbdimp.c2010-01-14
10:36:01.839787037 +
@@ -543,14 +543,14 @@
#ifdef NEW_OCI_INIT/* XXX needs merging into use_proc_connection branch */
/* Get CLIENT char and nchar charset id values */
- OCINlsEnvironmentVariableGet_log_stat( charsetid, 0,
OCI_NLS_CHARSET_ID, 0, rsize ,status );
+ OCINlsEnvironmentVariableGet_log_stat( charsetid,
(size_t)0, OCI_NLS_CHARSET_ID, 0, rsize ,status );
if (status != OCI_SUCCESS) {
oci_error(dbh, NULL, status,
OCINlsEnvironmentVariableGet(OCI_NLS_CHARSET_ID) Check NLS settings etc.);
return 0;
}
- OCINlsEnvironmentVariableGet_log_stat( ncharsetid, 0,
OCI_NLS_NCHARSET_ID, 0, rsize ,status );
+ OCINlsEnvironmentVariableGet_log_stat( ncharsetid,
(size_t)0, OCI_NLS_NCHARSET_ID, 0, rsize ,status );
if (status != OCI_SUCCESS) {
oci_error(dbh, NULL, status,
OCINlsEnvironmentVariableGet(OCI_NLS_NCHARSET_ID) Check NLS settings etc.);
diff -ur DBD-Oracle-1.24-RC3/oci8.c DBD-Oracle-1.24-RC3.warning_patches/oci8.c
--- DBD-Oracle-1.24-RC3/oci8.c 2010-01-08 19:39:46.0 +
+++ DBD-Oracle-1.24-RC3.warning_patches/oci8.c 2010-01-14 10:43:16.341547757
+
@@ -1872,7 +1872,7 @@
if (status == OCI_NEED_DATA ){
char buf[300];
sprintf(buf,fetching %s. LOB and the read bufer is
only %lubytes, and the ora_ncs_buff_mtpl is %d, which is too small. Try
setting ora_ncs_buff_mtpl to %d,
- name, buflen,
ora_ncs_buff_mtpl,ora_ncs_buff_mtpl+1);
+ name, (unsigned long)buflen,
ora_ncs_buff_mtpl,ora_ncs_buff_mtpl+1);
oci_error_err(sth, NULL, OCI_ERROR, buf,
OCI_NEED_DATA); /* appropriate ORA error number */
croak(DBD::Oracle has returned a %s status when doing
a LobRead!! \n,oci_status_name(status));
@@ -2240,7 +2240,7 @@
if (tdo != obj-tdo) {
/* new subtyped - get obj
description */
if (DBIS-debug = 5 ||
dbd_verbose = 5 ) {
- PerlIO_printf(DBILOGFP,
describe subtype (tdo=%x) of object type %s
(tdo=%x)\n,(int)tdo,base_obj-type_name,(int)base_obj-tdo);
+ PerlIO_printf(DBILOGFP,
describe subtype (tdo=%p) of object type %s
(tdo=%p)\n,(void*)tdo,base_obj-type_name,(void*)base_obj-tdo);
}
Newz(1, obj-next_subtype, 1,
fbh_obj_t);
@@ -3079,7 +3079,6 @@
ub4 num_fields;
int num_errors = 0;
int has_longs = 0;
- int has_lobs= 0;
int est_width = 0;/* estimated avg row width (for cache)
*/
int nested_cursors = 0;
ub4 i = 0;
diff -ur DBD-Oracle-1.24-RC3/ocitrace.h
DBD-Oracle-1.24-RC3.warning_patches/ocitrace.h
--- DBD-Oracle-1.24-RC3/ocitrace.h 2010-01-11 22:44:49.0 +
+++ DBD-Oracle-1.24-RC3.warning_patches/ocitrace.h 2010-01-14
10:38:09.520304633 +
@@ -222,8 +222,8 @@
stat = OCINlsEnvironmentVariableGet(valp, size, item, charset,
rsizep ); \
(DBD_OCI_TRACEON) \
? PerlIO_printf(DBD_OCI_TRACEFP,\
-%sNlsEnvironmentVariableGet(%d,%d,%d,%d,%d)=%s\n,\
-OciTp, *valp, size, item, charset, *rsizep,
oci_status_name(stat)),stat \
+%sNlsEnvironmentVariableGet(%d,%llu,%d,%d,%llu)=%s\n
Re: ANNOUNCE: DBD::Oracle 1.24 Release Candidate 3
On 14/01/10 13:10, John Scoles wrote: Thank Charles that is really good stuff I have not investigated the NLS_LANG=.WE8ISO8859P1, but the tests 30long.t and 31lob_extended.t still fail badly if NLS_LANG=.AL32UTF8. bug yet as my local test box is just US7ASCII. BTW can you tell me what the ' NLS_CHARACTERSET and UTF8 NLS_NCHAR_CHARACTERSET AL16UTF16 setting of your Oralcle DB you are testing on -- Charles Jardine - Computing Service, University of Cambridge c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: [patch] (final) DBD::Oracle - Support for standard array binds.
Tim Bunce wrote: On Tue, Sep 11, 2007 at 02:15:27AM +0400, Alexander V Alekseev wrote: On Mon, 10 Sep 2007, Tim Bunce wrote: There's no need for all that. You could just do this: $sth-bind_param_inout(:mytable, [EMAIL PROTECTED] , 10 , ... ) Don't you think, it's better to use the first format ? It's not really a question of one backslash vs two. It's more a question of the principle behind the design of the API. Consistency trumps beauty in the long run. Annoyingly for me I can argue it both ways in this case. Here's a question for you: what happens when the returnd value is NULL? How can you distinguish that from an empty array? Hmmm... In this particular case, Oracle object TABLE OF ... INDEX BY BINARY INTEGER can't be NULL. As far, as I know, it's impossible in Oracle. Be careful here. Nested table and varray types in PL/SQL can by null. Googling on 'Until you initialize it, a nested table or varray is atomically null' leads to clear documentation of this. With the current API you could do this: $sth-bind_param_inout(:mytable, \my $ary = [EMAIL PROTECTED] , 10 , ... ) die got null if !$ary; (I'll admit I've made that rather terse, but that's not the point here.) Ok. I'll change docs in my patch. Hey, don't give in so easily! You can still persuade me. I'm teetering on the end. Anyone else have any opinions? Do any other databases support array types? Can they be NULL? Oracle supports array types which can by NULL, both in PL/SQL and in SQL. I strong agree with Tim's position before he teetered. -- Charles Jardine - Computing Service, University of Cambridge [EMAIL PROTECTED]Tel: +44 1223 334506, Fax: +44 1223 334679
Re: Having problems with DBI, Larry Wall told me to ask you guys
Joe McTigue wrote:
Hi Any Kind Person,
Larry was almost right. Actually, dbi-users@perl.org would have
been a more appropriate mail list.
I am currently getting the following error message and was wondering if
you could help?
[snip]
But for the past several days now
[snip]
DBI connect('nameofdb','username',...) failed: ORA-24315: illegal
attribute type (DBD ERROR: OCIAttrGet OCI_ATTR_ENV_CHARSET_ID) at
perlscript.pl line 463
Can't connect to database:ORA-24315: illegal attribute type (DBD ERROR:
OCIAttrGet OCI_ATTR_ENV_CHARSET_ID) at perlscript.pl line 463.
[snip]
My guess is that the installation of Oracle on the computer on
which you are running this perl script was upgraded several days
ago (perhaps by the application of a patch), an that, in consequence,
the bits of DBD::Oracle which are implemented on C need re-compiling.
You should ask the person who installed DBD::Oracle to do this
for you.
--
Charles Jardine - Computing Service, University of Cambridge
[EMAIL PROTECTED]Tel: +44 1223 334506, Fax: +44 1223 334679
Re: Adding utf8 support to DBD::mysql
Tim Bunce wrote:
[I'm at the mysql conference and Patrick asked me about adding utf8
support to DBD::mysql.]
[snip]
*** Detecting Inconsistencies
If the connection character set is _not_ utf8 but the application calls
the driver with data (or SQL statement) that has the UTF8 flag set, then
it could issue a warning. In practice that may be to be too noisy for
people that done their own workarounds for utf8 support. If so then
they could be changes to level 1 trace messages.
If the connection character set _is_ utf8, and the application calls
the driver with data (or SQL statement) that does _not_ have the UTF8
flag set but _does_ have bytes with the high bit set, then the driver
should issue a warning. The checking for high bit set is an extra cost
so this should only be enabled if tracing and/or an attribute is set
(perhaps called $dbh-{mysql_charset_checks} = 1)
Tim,
You don't explicitly say what you are proposing should be done with
the anomalous data. I guess, by analogy with the behaviour of
DBD::Oracle's handling of SQL statements, that the implicit proposal
is to pass the octets of the anomalous string unchanged across the
connection. This will result in octet strings which perl has flagged
as being utf8-encoded being passed over connections which expect
byte encoding, and vice versa.
I think that this is wrong as the default behaviour for a DBD, and
I would be sorry to see another DBD converted to behave in this way.
The default behaviour I would like to see is as follows:
If a utf8-flagged string is presented for transmission over a
byte-encoded connection, an attempt should be made to downgrade
the string to byte encoding. This will fail if the string contains
characters with codepoints 255. Such failure should be treated
as an error.
If a string without the utf8 flag is presented for transmission
across a utf8-encoded connection, it should simply be upgraded
to utf8 encoding. This cannot fail.
I am aware that a DBD which does not automatically upgrade and
downgrade may provide a useful compatibility bridge for programs
originally written to cope with DBDs without Unicode support.
However, such DBDs are not compatible with the spirit of
perldoc perluniintro and perldoc perlunicode. To quote from the
former:
o How Do I Know Whether My String Is In Unicode?
You shouldn't care. No, you really shouldn't. No,
really. If you have to care--beyond the cases described
above--it means that we didn't get the transparency of
Unicode quite right.
Okay, if you insist: [...]
A DBD which does not handle upgrading and downgrading itself
doesn't get the transparency quite right. The writer of a program
using such a driver has to care about the utf8 flag, since
strings which compare equal in perl, but differ in the setting
of the flag, will produce different results when processed by
the DBD. This ought not to be the way of the future.
--
Charles Jardine - Computing Service, University of Cambridge
[EMAIL PROTECTED]Tel: +44 1223 334506, Fax: +44 1223 334679
Re: Binding undefs in DBD::Oracle
Paul G. Weiss wrote:
[snip]
but
DB3 x $dbh-selectall_arrayref('select x,b from tbl where a=? and
b=?', undef, 4, undef)
0 ARRAY(0x9ba0530)
empty array
i.e. the 'b=?' in the statement where I bind the value to undef does
_not_ act like
'b is null'.
No - it doesn't. It acts like 'b=null', as is should. 'b=null' can
never be true.
This is not Oracle specific. It is covered under 'NULL values' in
the DBI pod.
--
Charles Jardine - Computing Service, University of Cambridge
[EMAIL PROTECTED]Tel: +44 1223 334506, Fax: +44 1223 334679
Re: ANNOUNCE: DBD::Oracle 1.17 release candidate 3
Andy Hassall wrote: What seems to be happening with RC3 is that Makefile.PL is behaving as if I had a full Oracle install rather than an Instant Client. Yeap. I'll take a closer look at the log and see if I can spot why. Thanks for the details. I think it just needs the Oracle XE and Oracle Instant Client branches swapping around. [snip] Swap the XE and IC tests over (as in attached patch) and it builds successfully again for me (on Linux 10201instant, 10201xe, 10201full, 10104instant, 9207full). This patch makes my build on Solaris 8 10201instant work again. -- Charles Jardine - Computing Service, University of Cambridge [EMAIL PROTECTED]Tel: +44 1223 334506, Fax: +44 1223 334679
Re: ANNOUNCE: DBD::Oracle 1.17 release candidate 3
Tim Bunce wrote: http://www.data-plan.com/public/DBD-Oracle-1.17-RC3.tar.gz We're getting there. This one fixes the two issues raised against RC2 (thanks Andy and Steffen). I'd be grateful for more, and wider, testing. Thanks! Neither this release, nor RC2, work against the Instant Client on Solaris. Makefile.PL fails with the output shown below. The original release candidate's Makefile.PL succeeds with the exact same environment settings (using the makefile /jackdaw/instantclient/sdk/demo/demo.mk). [EMAIL PROTECTED] perl Makefile.PL -v Using DBI 1.50 (for perl 5.008007 on sun4-solaris-64) installed in /net/jackdaw.cam.ac.uk/jackdaw/perl/5.8.7-A/lib/site_perl/5.8.7/sun4-solaris-64/auto/DBI/ Configuring DBD::Oracle for perl 5.008007 on solaris (sun4-solaris-64) Remember to actually *READ* the README file! Especially if you have any problems. Using Oracle in /jackdaw/instantclient PATH=/jackdaw/instantclient/bin:/jackdaw/instantclient:/jackdaw/perl/5.8.7-A/bin:/home/cj10/bin:/jackdaw/perl/bin:/jackdaw/perl/util:/jackdaw/bin:/usr/openwin/bin:/opt/SUNWspro/bin:/usr/sbin:/usr/bin:/usr/ccs/bin DEFINE _SQLPLUS_RELEASE = 1002000100 (CHAR) Oracle version 10.2.0.1 (10.2) Unable to locate an oracle.mk, proc.mk or other suitable *.mk file in your Oracle installation. (I looked in /jackdaw/instantclient/rdbms/demo/demo_xe.mk /jackdaw/instantclient/rdbms/lib/oracle.mk /jackdaw/instantclient/rdbms/demo/oracle.mk /jackdaw/instantclient/rdbms/demo/demo_rdbms.mk /jackdaw/instantclient/precomp/demo/proc/proc.mk /jackdaw/instantclient/precomp/demo/proc/demo_proc.mk /jackdaw/instantclient/proc/lib/proc.mk /jackdaw/instantclient/proc16/lib/proc16.mk under /jackdaw/instantclient) The oracle.mk (or demo_rdbms.mk) file is part of the Oracle RDBMS product. The proc.mk (or demo_proc.mk) file is part of the Oracle Pro*C product. You need to build DBD::Oracle on a system which has one of these Oracle components installed. (Other *.mk files such as the env_*.mk files will not work.) Alternatively you can use Oracle Instant Client. In the unlikely event that a suitable *.mk file is installed somewhere non-standard you can specify where it is using the -m option: perl Makefile.PL -m /path/to/your.mk See README.clients.txt for more information and some alternatives. at Makefile.PL line 1039. [EMAIL PROTECTED] -- Charles Jardine - Computing Service, University of Cambridge [EMAIL PROTECTED]Tel: +44 1223 334506, Fax: +44 1223 334679
Re: ANNOUNCE: DBI 1.50 release candidate (bug in 10General.t)
Tim Bunce wrote:
http://dbi.demonweb.co.uk/public/DBI-1.50.tar.gz
I have successfully built the above release candidate under
Solaris 8 against an Oracle 10.2 Instant Client. The full
versions of things are:
SunOS 5.8 Generic_117350-26 sun4u sparc SUNW,Sun-Blade-100
This is perl, v5.8.7 built for sun4-solaris-64
SQL*Plus: Release 10.2.0.1.0
The resulting build fails one test. The error messages are:
t/10general.NOK 1
# Failed test (t/10general.t at line 25)
# got: '65280'
# expected: '256'
t/10general.ok 13/33# Looks like you failed 1 test of 33.
t/10general.dubious
The line in question is:
is system(false), 18, 'system false should return 256';
The cause of the problem is that the Solaris version of /usr/bin/false
gives an exit code of 255, not 1.
Here is a patch which fixes this:
Index: t/10general.t
===
--- t/10general.t (revision 2334)
+++ t/10general.t (working copy)
@@ -22,8 +22,8 @@
SKIP: {
skip not unix-like, 2 unless $Config{d_semctl};
# basic check that we can fork subprocesses and wait for the status
- is system(false), 18, 'system false should return 256';
- is system(true), 0, 'system true should return 0';
+ is system(exit 1;), 18, 'system exit 1; should return 256';
+ is system(exit 0;),0, 'system exit 0; should return 0';
}
--
Charles Jardine - Computing Service, University of Cambridge
[EMAIL PROTECTED]Tel: +44 1223 334506, Fax: +44 1223 334679
Re: ANNOUNCE: DBD::Oracle 1.17 release candidate 3
Tim Bunce wrote: http://www.data-plan.com/public/DBD-Oracle-1.17-RC3.tar.gz We're getting there. This one fixes the two issues raised against RC2 (thanks Andy and Steffen). I'd be grateful for more, and wider, testing. Thanks! Neither this release, nor RC2, work against the Instant Client on Solaris. Makefile.PL fails with the output shown below. The original release candidate's Makefile.PL succeeds with the exact same environment settings (using the makefile /jackdaw/instantclient/./sdk/demo/demo.mk). [EMAIL PROTECTED] perl Makefile.PL -v Using DBI 1.50 (for perl 5.008007 on sun4-solaris-64) installed in /net/jackdaw.cam.ac.uk/jackdaw/perl/5.8.7-A/lib/site_perl/5.8.7/sun4-solaris-64/auto/DBI/ Configuring DBD::Oracle for perl 5.008007 on solaris (sun4-solaris-64) Remember to actually *READ* the README file! Especially if you have any problems. Using Oracle in /jackdaw/instantclient PATH=/jackdaw/instantclient/bin:/jackdaw/instantclient:/jackdaw/perl/5.8.7-A/bin:/home/cj10/bin:/jackdaw/perl/bin:/jackdaw/perl/util:/jackdaw/bin:/usr/openwin/bin:/opt/SUNWspro/bin:/usr/sbin:/usr/bin:/usr/ccs/bin DEFINE _SQLPLUS_RELEASE = 1002000100 (CHAR) Oracle version 10.2.0.1 (10.2) Unable to locate an oracle.mk, proc.mk or other suitable *.mk file in your Oracle installation. (I looked in /jackdaw/instantclient/rdbms/demo/demo_xe.mk /jackdaw/instantclient/rdbms/lib/oracle.mk /jackdaw/instantclient/rdbms/demo/oracle.mk /jackdaw/instantclient/rdbms/demo/demo_rdbms.mk /jackdaw/instantclient/precomp/demo/proc/proc.mk /jackdaw/instantclient/precomp/demo/proc/demo_proc.mk /jackdaw/instantclient/proc/lib/proc.mk /jackdaw/instantclient/proc16/lib/proc16.mk under /jackdaw/instantclient) The oracle.mk (or demo_rdbms.mk) file is part of the Oracle RDBMS product. The proc.mk (or demo_proc.mk) file is part of the Oracle Pro*C product. You need to build DBD::Oracle on a system which has one of these Oracle components installed. (Other *.mk files such as the env_*.mk files will not work.) Alternatively you can use Oracle Instant Client. In the unlikely event that a suitable *.mk file is installed somewhere non-standard you can specify where it is using the -m option: perl Makefile.PL -m /path/to/your.mk See README.clients.txt for more information and some alternatives. at Makefile.PL line 1039. [EMAIL PROTECTED] -- Charles Jardine - Computing Service, University of Cambridge [EMAIL PROTECTED]Tel: +44 1223 334506, Fax: +44 1223 334679
