A large customer refuses to apply security PSUs because of the (neagtive)
side-effects they had seen in the past.
I would love to have a set of "basic functional tests", so I can see that a
patch only corrects the problem it is supposed to correct .
Regards
Willy Klotz
Von: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] Im
Auftrag von Michael D O'Shea/Woodward Informatics Ltd
Gesendet: Donnerstag, 10. November 2016 09:52
An: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
Betreff: Testing Oracle fitness for purpose/internal test suite
Recently I encountered a very large organisation that had a comprehensive
suite of unit tests that tested stock "Oracle" SQL & PL/SQL behaviour. Yes I
really did write that. The client likely had a suite of unit tests that
tested their in-house developed code too but we were discussing testing of
stock Oracle functionality. In my book, I test my own code, and I expect the
vendor to test and certify their software is OK and "does what it says on
the tin". I found the client behaviour atypical to say the least. Their
issue was that they had commited to Oracle, but there was a quality
confidence issue.
Their tests appeared to be done for two reasons.
1. To confirm Oracle patch/patch sets or version upgrades did not introduce
unexpected behaviour, or changes to existing RDBMS behaviour, that would
manifest itself in their critical business systems.
2. To lay the law down in terms of their internal coding standards, "though
shall not use LISTAGG" for example.
The following contrived and silly code snippet returns the length of a
VARCHAR2 aggregated using LISTAGG. The LENGTH(y) is 4*999+3=3999. All good
(again this is contrived code, but from memory it is the type of scripted
code the organisation had in their test suite).
SQL>
SQL>
SQL> set serveroutput on size 100000
SQL> set timing on
SQL>
SQL> declare
2 xx char(999) := 'char not varchar2 so padded';
3 y varchar2(32767); --PL/SQL limit 32767, not 4000
4 begin
5 select distinct listagg(x4, ',')
6 within group (order by 1)
7 over (partition by 1)
8 into y
9 from (
10 select xx x4 from dual
11 union all select xx from dual
12 union all select xx from dual
13 union all select xx from dual
14 );
15 dbms_output.put_line(to_char(length(y)));
16 end;
17 /
3999
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
SQL>
SQL>
SQL>
This is the expected behaviour, and 3999 the expected VARCHAR2 length (4 *
999 = 3996 + 3 for the 3 commas).
Something like the next test they performed is shown below. Although
surprising to me, it was the expected behaviour - the limit on LISTAGG is
4000 bytes not 32k, but it was a test "pass".
SQL>
SQL>
SQL>
SQL>
SQL> declare
2 xx char(1000) := 'char not varchar2 so padded';
3 y varchar2(32767); --PL/SQL limit 32767, not 4000
4 begin
5 select distinct listagg(x4, ',')
6 within group (order by 1)
7 over (partition by 1)
8 into y
9 from (
10 select xx x4 from dual
11 union all select xx from dual
12 union all select xx from dual
13 union all select xx from dual
14 );
15 dbms_output.put_line(to_char(length(y)));
16 end;
17 /
declare
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long
ORA-06512: at line 5
Elapsed: 00:00:00.03
SQL>
The next test was a failure for the client and showcased their concerns.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> declare
2 xx char(2001) := 'char not varchar2 so padded';
3 y varchar2(32767); --PL/SQL limit 32767, not 4000
4 begin
5 select distinct listagg(x4, ',')
6 within group (order by 1)
7 over (partition by 1)
8 into y
9 from (
10 select xx x4 from dual
11 union all select xx from dual
12 union all select xx from dual
13 union all select xx from dual
14 );
15 dbms_output.put_line(to_char(length(y)));
16 end;
17 /
declare
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [15851], [3], [2], [1], [1], [],
[], [], [], [], [], []
Elapsed: 00:00:00.08
SQL>
SQL>
While clearly this is just a plain old Oracle bug (an edge case - 2000 vs.
2001 bytes, half the 4000 byte limit), the test result confirmed the client
confidence issue in internal Oracle testing and fitness for purpose, with a
knock-on effect that their internal coding standards documentation
prohibited the use of LISTAGG (I do not know whether they raised the issue
with Oracle but this isn't my point), and other built-in functionality.
My question to you all is "Has anyone else encountered the behaviour where
clients run a suite of automated tests to test out-of-the-box Oracle stock
functionality, in otherwords running what are in effect unit tests that
should be performed by the Oracle prior to shipping, not the client?".
Despite my views of quality and testing at Oracle, I still cannot get my
head around a client testing out-of-the-box functionality with their own
test suite like this.
I am not sure what version of Oracle the client was using, but I have just
retested using the following version (below) and can reproduce the
behaviour.
Regards
Mike
http://www.strychnine.co.uk
SQL>
SQL>
SQL>
SQL> select * from v$version;
BANNER
CON_ID
----------------------------------------------------------------------------
---- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
0
PL/SQL Release 12.1.0.2.0 - Production
0
CORE 12.1.0.2.0 Production
0
TNS for Solaris: Version 12.1.0.2.0 - Production
0
NLSRTL Version 12.1.0.2.0 - Production
0
Elapsed: 00:00:00.02
SQL>
SQL>
SQL>
