Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from puck1183.startdedicated.com (localhost [127.0.0.1]) by puck1183.startdedicated.com (Postfix) with ESMTP id B5E5E19602AD for ; Fri, 11 Nov 2016 17:39:24 +0100 (CET) Received: from turing.freelists.org (turing.freelists.org [206.53.239.180]) by puck1183.startdedicated.com (Postfix) with ESMTPS for ; Fri, 11 Nov 2016 17:39:24 +0100 (CET) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5A0D461EDE; Fri, 11 Nov 2016 11:39:23 -0500 (EST) X-Virus-Scanned: Debian amavisd-new at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id QE5YFcxt33Om; Fri, 11 Nov 2016 11:39:23 -0500 (EST) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9DD8B61EB7; Fri, 11 Nov 2016 11:39:04 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 11 Nov 2016 11:37:42 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9431D61E5D for ; Fri, 11 Nov 2016 11:37:42 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id ZN1uJk-Ak66G for ; Fri, 11 Nov 2016 11:37:42 -0500 (EST) Received: from icecream.delink-ssl.net (icecream.delink-ssl.net [148.251.178.35]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 51C3061E54 for ; Fri, 11 Nov 2016 11:37:40 -0500 (EST) Received: from KBI7PROF ([78.42.143.114]) by icecream.delink-ssl.net (deLink mail system) with ASMTP (SSL) id 201611111737397495 for ; Fri, 11 Nov 2016 17:37:39 +0100 From: "Willy Klotz" To: References: In-Reply-To: Subject: AW: Testing Oracle fitness for purpose/internal test suite Date: Fri, 11 Nov 2016 17:37:37 +0100 Message-ID: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0109_01D23C42.4BF12A50" Content-Language: de X-archive-position: 66815 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: willyk@kbi-gmbh.de Precedence: normal Reply-To: willyk@kbi-gmbh.de List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l ------=_NextPart_000_0109_01D23C42.4BF12A50 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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@freelists.org [mailto:oracle-l-bounce@freelists.org] Im Auftrag von Michael D O'Shea/Woodward Informatics Ltd Gesendet: Donnerstag, 10. November 2016 09:52 An: ORACLE-L 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> ------=_NextPart_000_0109_01D23C42.4BF12A50 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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@freelists.org = [mailto:oracle-l-bounce@freelists.org] Im Auftrag von Michael D = O'Shea/Woodward Informatics Ltd
Gesendet: Donnerstag, 10. = November 2016 09:52
An: ORACLE-L = <oracle-l@freelists.org>
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=3D3999. 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) :=3D '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 =3D 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) :=3D '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) :=3D '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

 

 

 

 

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>

 

 

 

 

------=_NextPart_000_0109_01D23C42.4BF12A50-- -- http://www.freelists.org/webpage/oracle-l