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 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/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 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: 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 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 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 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 +0000, Charles Jardine > 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: DBD::Oracle
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"' 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? 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. -- 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" 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 = ) { > 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: Clarification sought on execute_array
On 20/01/11 09:55, Tim Bunce wrote: > On Wed, Jan 19, 2011 at 10:25:05PM +, Martin J. Evans wrote: >> On 19/01/2011 11:47, Tim Bunce wrote: > Umm. I take that back. I suggest we change the spec from: > > When called in scalar context the execute_array() method returns the > number of tuples executed, or undef if an error occurred. > Like execute(), a successful execute_array() always returns true > regardless of the number of tuples executed, even if it's zero. > If there were any errors the ArrayTupleStatus array can be used to > discover which tuples failed and with what errors. I have always found the above wording confusing. I think I read 'tuple' as referring to a list of bind variables/values. Someone else might thing 'tuple' referred to a row in the database. > to either (new or changed text in capitals): > > a: When called in scalar context the execute_array() method returns the > number of tuples executed INCLUDING ANY TUPLES WHICH FAILED, > or undef if an error occurred THAT PREVENTED NORMAL OPERATION OF THE > METHOD. THE FAILURE OF SOME OR ALL TUPLES IS NOT REGARDED AS AN ERROR. > Like execute(), a successful execute_array() always returns true > regardless of the number of tuples executed, even if it's zero. > If there were any errors the ArrayTupleStatus array can be used to > discover which tuples failed and with what errors. > > or > > b: When called in scalar context the execute_array() method returns the > number of tuples executed IF ALL ARE SUCCESSFUL. IF ANY TUPLES > ENCOUNTER AN ERROR THEN execute_array() RETURNS UNDEF. > Like execute(), a successful execute_array() always returns true > regardless of the number of tuples executed, even if it's zero. > If there were any errors the ArrayTupleStatus array can be used to > discover which tuples failed and with what errors. > > I prefer (a) but (b) is how the DBI's default execute_array() and > execute_for_fetch() behave, which is obviously significant. I strongly prefer (b). I am sure that, if I am using RaiseError or HandleError, I want the exception route to be taken if *any* of the individual executes fail. >> The way I see it is that if some of the rows in the batch end up in >> the table and some don't I'd expect a warning. However, if 1 or more >> rows fail and no rows end up in the table I don't see any success so >> I'd expect an error. In my example code, 1 row fails but no rows end >> up successful so it is the latter. > > Shouldn't the number of rows "failing" and the number not ending up in > the database be the same? Anything else seems like a bug. Or are you > refering to a batch being aborted early? execute_array can be used for any non-SELECT DML statement. In general, a single successful execute can affect any number of rows, including zero. It is only in the case of a simple INSERT statement (with a VALUES clause) that there is a direct relationship between the number of successful executes and the number or rows affected. In some places in the current thread, the number of successful executes has been confused with the number of rows affected. -- 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 : >> [...] >>> 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 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 -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 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: 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: Bug in tracing in DBD::Oracle
On 28/01/10 13:15, Martin Evans wrote: Charles Jardine wrote: On 27/01/10 17:38, Martin Evans wrote: [snip] I prefer his version, with the cast to site_t left where it was, rather than imported into the macro. I'm not that comfortable with the cast to size_t in dbdimp.c because then it is later cast back to unsigned long and I'd guess on platforms where size_t is an unsigned long long the compiler might whine about that. The best fix would be if there was a reliable format for size_t but I don't know of one. Many modern printf implementations support 'z' as a width modifier meaning 'as wide as size_t'. This makes '%zu' the right answer fro such implementations. However, PerlIO_printf does not support it, so we can't use it. However, I don't think we need to get this out of proportion after all it is only two calls and in both cases the size_t is 0 anyway as the requested attributes are integers and not strings. Indeed! If the current SVN version works for Martin, I suggest that no more needs to be done. It does work. Excellent. [bug report snipped] I suggest we try very hard to get someone with a 64bit platform to try the next RC. I will be testing RC5 on as 64-bit Linux platform with Oracle 10.2.0.4.3. If you want me to test anything specific, let me know. -- 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=, 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 15/01/10 15:14, Charles Jardine wrote: 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. Things have move on - I am now at Oracle 10.2.0.4.3. I have a attached patch for the second problem. In will post a further patch providing regression tests for both problems. The table which demonstrates the second problem was set up as follows: CREATE OR REPLACE TYPE inner_type AS OBJECT ( v VARCHAR2(255) ); / CREATE OR REPLACE TYPE outer_type AS OBJECT ( id NUMBER, inner_obj inner_type); / CREATE TABLE nest(outer_obj outer_type); INSERT INTO nest VALUES (outer_type(1, inner_type('Hello world'))); In my environment, fetching the single row of this table causes a segmentation violation regardless of the value of $dbh->{ora_objects}. The violation occurs in the call of OCIObjectGetAttr which is attempting to get the property named V from the inner object. The cause of the violation is that the 4th argument passed to OCIObjectGetAttr has the value 0x1. This argument should be a pointer to an indicator structure. It is apparently an indicator value. This value has just been got from the inner object itself using OCIObjectGetInd. Is seems that OCIObjectGetInd does not always return a pointer to an indicator structure when it is applied to an object instance. In particular, it doesn't work in this case (when the object type is FINAL, and the object instance is a property of another object). When OCIObjectGetAttr is used to get the of the inner object from the instance of the outer object, an indicator structure for the inner object is returned via the attr_null_struct argument. The DBD::Oracle code never uses this result, instead calling OCIObjectGetInd later, to attempt to get the same structure again. Poking a little further, I have realised that, whenever get_object is called, an indicator structure suitable for passing to OCIObjectGetAttr is already to hand. There is no need for get_object ever to call OCIObjectGetInd. There are three cases: * Objects which are column values: The indicator structure was obtained at define time via the indpp argument to OCIDefineObject and stored in obj->obj_ind. * Objects which are properties of other objects: The indicator structure is returned by the existing call of OCIObjectGetAttr (arg attr_null_struct). * Objects in varrays or multisets: The indicator structure is returned by the existing call of OCIIterNext (arg elemind) The patch I attach adds an extra argument to get_object, which always points to an indicator structure obtained as above. It removes all the calls of OCIObjectGetInd. It also fixes a bug by testing the return codes of the two recursive calls of get_object. The elements obj->obj_ind are now used only to store the pointers obtained from OCIDefineObject. They are no longer modified at fetch time. -- 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.obj_ind_patch/: Makefile.old diff -ur DBD-Oracle-1.24-RC3/oci8.c DBD-Oracle-1.24-RC3.obj_ind_patch/oci8.c --- DBD-Oracle-1.24-RC3/oci8.c 2010-01-08 19:39:46.0 + +++ DBD-Oracle-1.24-RC3.obj_ind_patch/oci8.c2010-01-18 11:22:52.171812759 + @@ -2177,7 +2177,7 @@ /*gets the properties of an object from a fetch by using the attributes saved in the describe */ int -get_object (SV *sth, AV *list, imp_fbh_t *fbh,fbh_obj_t *base_obj,OCIComplexObject *value, OCIType *instance_tdo){ +get_object (SV *sth, AV *list, imp_fbh_t *fbh,fbh_obj_t *base_obj,OCIComplexObject *value, OCIType *instance_tdo, dvoid *obj_ind){ dTHX; sword status; @@ -2191,7 +2191,6 @@ OCIType *attr_tdo; OCIIter *itr; fbh_obj_t *fld; - OCIInd *obj_ind; fbh_obj_t *obj = base_obj; OCIType*tdo = instance_tdo ? instance_tdo : o
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,
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: 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",\ -
Re: ANNOUNCE: DBD::Oracle 1.24 Release Candidate 3
= 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",\ +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: Statement handle creation not seen by subclassed DBI
On 20/02/08 17:07, Martin Evans wrote: Hi, Just wondered if anyone else had seen this and if there are any opinions on getting around it. I use DBIx::Log4perl (which I wrote to help debug our DBI appication) and DBD::Oracle. New code added to our application calls an oracle package function which returns a reference cursor and it is bound in Perl like so: my $sth2; my $sth1 = $h->prepare(q{BEGIN ? := pkg.testfunc; END;}); $sth1->bind_param_inout(1, \$sth2, 0, { ora_type => ORA_RSET } ); as per DBD::Oracle pod. After $sth1->execute, $sth2 is a statement handle but DBIx::Log4perl does not know about it and hence when it is used it leads to errors in DBIx::Log4perl when it accesses private attributes it usually stores in the statement handle (e.g. a Log4perl log handle) which are not present. i.e., normally when the application calls the prepare method, DBIx::Log4perl sees the returned statement handle and does something like: my $sth = $dbh->SUPER::prepare(@args); $sth->{private_DBIx_Log4perl} = $log_handle if ($sth); but DBIx::Log4perl did not see this magically returned statement handle. These statement handles are presumably of the correct class. All that is wrong with them is that DBIx::Log4perl has not has a chance to initialise them. They have never been through prepare or execute, but are nevertheless ready to fetch from. One approach would be to modify all the fetch methods in DBIx::Log4perl to expect uninitialised statement handles and initialise on demand. You will also have to cope with the fact that these handles do not have useful Statement attributes. -- Charles Jardine - Computing Service, University of Cambridge [EMAIL PROTECTED]Tel: +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 DB<3> 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: On Thu, Jan 12, 2006 at 12:34:54PM +, Charles Jardine wrote: 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). I presume you had to use the -m option to specify the .../demo.mk since demo.mk isn't one of the .mk file names Makefile.PL search for. No. $ORACLE_HOME points to an Instant Client. demo.mk is the only makefile it has got. This worked with the first release candidate. The Makefile.pl -v ouput is below. 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. What does "ls -l $ORACLE_HOME" show? [EMAIL PROTECTED] echo $ORACLE_HOME /jackdaw/instantclient [EMAIL PROTECTED] ls -l $ORACLE_HOME lrwxrwxrwx 1 root other 18 Jan 3 10:55 /jackdaw/instantclient -> instantclient_10_2 [EMAIL PROTECTED] ls -l $ORACLE_HOME/. total 211850 -r--r--r-- 1 root other1590491 Aug 16 14:35 classes12.jar -r--r--r-- 1 root other 1525 Aug 16 14:38 glogin.sql lrwxrwxrwx 1 root other 17 Jan 4 14:58 libclntsh.so -> libclntsh.so.10.1 -rwxr-xr-x 1 root other23805656 Aug 16 14:35 libclntsh.so.10.1 -rwxr-xr-x 1 root other6858120 Aug 16 14:35 libnnz10.so -rwxr-xr-x 1 root other1492072 Aug 16 14:35 libocci.so.10.1 -rwxr-xr-x 1 root other70108288 Aug 16 14:35 libociei.so -rwxr-xr-x 1 root other 123864 Aug 16 14:35 libocijdbc10.so -rwxr-xr-x 1 root other1320016 Aug 16 14:38 libsqlplus.so -rwxr-xr-x 1 root other1437104 Aug 16 14:38 libsqlplusic.so -r--r--r-- 1 root other1536979 Aug 16 14:35 ojdbc14.jar drwxr-xr-x 4 root other512 Aug 16 14:38 sdk -rwxr-xr-x 1 root other 8784 Aug 16 14:38 sqlplus [EMAIL PROTECTED] find $ORACLE_HOME/. -name '*.mk' /jackdaw/instantclient/./sdk/demo/demo.mk [EMAIL PROTECTED] And the Makefile.PL outout from the first release candidate. [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 ... >>> Remember to actually *READ* the README file! Especially if you have any problems. os=solaris Using Oracle in /jackdaw/instantclient path=/jackdaw/perl/5.8.7-A/bin:/home/cj10/bin:/jackdaw/perl/bin:/jackdaw/perl/util:/jackdaw/bin:/jackdaw/instantclient:/usr/openwin/bin:/opt/SUNWspro/bin:/usr/sbin:/usr/bin:/usr/ccs/bin:/jackdaw/instantclient/bin:/jackdaw/instantclient DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR) Oracle version 10.2.0.1 (10.2) Found direct-link candidate: clntsh Oracle sysliblist: Checking for functioning wait.ph System: perl5.008007 sunos skylark.csx.cam.ac.uk 5.8 generic_117350-26 sun4u sparc sunw,sun-blade-100 Compiler: /opt/SUNWspro/bin/cc -O -xarch=v9 -I/net/jackdaw.cam.ac.uk/jackdaw/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 Linker: /usr/ccs/bin/ld Sysliblist: Linking with -lclntsh. Warning: If you have problems you may need to rebuild perl with threading enabled. MakeMaker (v6.17) Checking if your kit is complete... Looks good ABSTRACT_FROM => q[Oracle.pm] AUTHOR => q[Tim Bunce (dbi-users@perl.org)] DEFINE => q[ -DUTF8_SUPPORT -DNEW_OCI_INIT -DORA_OCI_VERSION=\"10.2.0.1\"] DIR => [] EXE_FILES => [q[ora_explain]] INC => q[-I/jackdaw/instantclient/sdk/include -I/net/jackdaw.cam.ac.uk/jackdaw/perl/5.8.7-A/lib/site_perl/5.8.7/sun4-solaris-64/auto/DBI/] LIBS => [q[-L/jackdaw/instantclient/ -lclntsh ]] NAME => q[DBD::Oracle] OBJECT => q[$(O_FILES)] PREREQ_PM => { DBI=>q[0] } VERSION_FROM => q[Oracle.pm] clean => { FILES=>q[xstmp.c Oracle.xsi dll.base dll.exp sqlnet.log libOracle.def ora_explain mk.pm] } dist => { DIST_DEFAULT=>q[clean distcheck disttest tardist], COMPRESS=>q[gzip -v9], PREOP=>q[$(MAKE) -f Makefile.old distdir], SUFFIX=>q[gz] } dynamic_lib => { OTHERLDFLAGS=>q[] } Using PERL=/jackdaw/perl/5.8.7-A/bin/perl Potential libraries are '-L/jackdaw/instantclient/ -lclntsh': '-lclntsh' found at /jackdaw/instantclient//libclntsh.so.10.1 LD_RUN_PATH=/jackdaw/instantclient Using DBD::Oracle 1.17. Using DBD::Oracle 1.17. 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/a
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"), 1<<8, '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"), 1<<8, 'system false should return 256'; - is system("true"), 0, 'system true should return 0'; + is system("exit 1;"), 1<<8, '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