Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qhVwb-003By0-9t for pgsql-sql@arkaria.postgresql.org; Sat, 16 Sep 2023 14:06:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qhVwY-00DwnP-2j for pgsql-sql@arkaria.postgresql.org; Sat, 16 Sep 2023 14:06:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qhVwX-00DwnE-NV for pgsql-sql@lists.postgresql.org; Sat, 16 Sep 2023 14:06:41 +0000 Received: from mail-yw1-x112d.google.com ([2607:f8b0:4864:20::112d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qhVwU-005Se8-UW for pgsql-sql@postgresql.org; Sat, 16 Sep 2023 14:06:41 +0000 Received: by mail-yw1-x112d.google.com with SMTP id 00721157ae682-59bcd927b45so33396457b3.1 for ; Sat, 16 Sep 2023 07:06:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1694873197; x=1695477997; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=1whqq+npYA6qkB5sI/37GSqwbinVmx3fxWbxrKnlC2s=; b=YBQQaaS6ez96LIc3Fe2KTpCSFiZjy2hB4QUJ+twRq8NsdJMQ0RZjkMExQHcnw3pEAO T4ipoO51N9HXixlXxfGfSuyXCTWh9fiRal9w42UFA0A/43bBe4I+DHR0r8RvcGOX8QyW 3bA2hEtauVUjy8inoL9dBv1cq7pT8Fj5a+ChjQVxpILAx3ppsixtB7AW+EklGju7jL0H bkfgP7BLVQ2m/VQTJDVfOzyK+Xhy70K8h1lmt3WBYK6mmnEJ53/008ehVZWeKIWVInV5 //Z7ghVJv5uCLV/HATXoGo/jURVSfKcuG1ait5Bptxg+tP5OCuui6YE63+Z89fg3sYD1 zmPw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1694873197; x=1695477997; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=1whqq+npYA6qkB5sI/37GSqwbinVmx3fxWbxrKnlC2s=; b=uAkAfZTs56XCGk3moaOBXhf4C4Qe8pN4yfm/HNO+j2EYqoM7l5p8bGfbJac8buD2cn RkbvwWDde1Wew6Kay1+gNKxeu/EkUxLqGamuZi94lsCwVyvSaMQenwFt3vCqoxUC9aPf t7JgvPu/ChhRiESoqMDhNzO7Gq0CTDTJ7R6oOKAxBeX9gh9/58W3na6dfX7UCNkLU/Fc n/qEjFMROno/y54mpASD8LAhDAaVvBwjpsShxZLsz45N+kWZkULS5OOTT/3zbwkCa1XT flcUWCqt2gX/puKvCFQz2OoLc6P2ks7V8hd9mG0wTBoXdD5KkwunU2ZmENdQW3rPGd0U Xr2Q== X-Gm-Message-State: AOJu0Yxsq0ROTCZe3+7otveXx4Tj5LZ7lrTb2gOH2UHaMeEEpI8jj5GK XVo5WlV/1HXNfrftX4QrTi3gUpgzTjvryNrSYZI= X-Google-Smtp-Source: AGHT+IEu2TdA4tMsisjwv0FhhypnJHfUgZiwtQxSZhtKUHkZrD7bjFryoHkGlJv8ckfJtguT5OAmPNOmDkfj0Q1Pto0= X-Received: by 2002:a25:40cc:0:b0:d72:8661:ee26 with SMTP id n195-20020a2540cc000000b00d728661ee26mr4660847yba.4.1694873197043; Sat, 16 Sep 2023 07:06:37 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Pavel Stehule Date: Sat, 16 Sep 2023 16:06:00 +0200 Message-ID: Subject: Re: no_data_found oracle vs pg To: "Jean-Marc Voillequin (MA)" Cc: "pgsql-sql@postgresql.org" Content-Type: multipart/alternative; boundary="000000000000d9647906057a6ab9" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d9647906057a6ab9 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi so 16. 9. 2023 v 13:27 odes=C3=ADlatel Jean-Marc Voillequin (MA) < Jean-Marc.Voillequin@moodys.com> napsal: > Hello everyone, > > On Oracle, a no_data_found exception is raised from pl/sql but not from > sql (it returns null). It's well known. > > Connected to: > Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit > Production > > SQL> create or replace function hello return char is > 2 c char; > 3 begin > 4 select 'a' into c from dual where 1=3D2; > 5 return c; > 6 end; > 7 / > > Function created. > > SQL> select coalesce(hello(),'') from dual; > > COALESCE(HELLO(),'') > > -------------------------------------------------------------------------= ------- > > > SQL> declare > 2 res char; > 3 begin > 4 res:=3Dhello(); > 5 end; > 6 / > > declare > * > ERROR at line 1: > ORA-01403: no data found > ORA-06512: at "JM.HELLO", line 4 > ORA-06512: at line 4 > > > On PG, with the strict keyword, we get: > > psql (15.2) > Type "help" for help. > > JM=3D> create or replace function hello_strict() returns char language > plpgsql as $function$ > JM$> declare > JM$> c char; > JM$> begin > JM$> select 'a' into strict c where 1=3D2; > JM$> return c; > JM$> end;$function$; > CREATE FUNCTION > > JM=3D> select coalesce(hello_strict(),''); > > ERROR: query returned no rows > CONTEXT: PL/pgSQL function hello_strict() line 5 at SQL statement > > JM=3D> do $$declare > JM$> res char; > JM$> begin > JM$> res:=3Dhello_strict(); > JM$> end$$; > > ERROR: query returned no rows > CONTEXT: PL/pgSQL function hello_strict() line 5 at SQL statement > PL/pgSQL function inline_code_block line 4 at assignment > > > And without the strict keyword: > > JM=3D> create or replace function hello_not_strict() returns char languag= e > plpgsql as $function$ > JM$> declare > JM$> c char; > JM$> begin > JM$> select 'a' into c where 1=3D2; > JM$> return c; > JM$> end;$function$; > CREATE FUNCTION > > JM=3D> select coalesce(hello_not_strict(),''); > coalesce > ---------- > > (1 row) > > JM=3D> do $$declare > JM$> res char; > JM$> begin > JM$> res:=3Dhello_not_strict(); > JM$> end$$; > DO > JM=3D> > > I have tons of functions to migrate from Oracle to PG. They are both > called from SQL or PL/SQL. > I would like to avoid to create two functions (_strict and _not_strict). > > A kind of proxy function that is lazy to evaluate its argument would be > helpful: > select do_not_raise_no_data_found(hello_strict()); > The STRICT keyword can be replaced by test of number of returned rows so you can do some like DECLARE rows int; target record; BEGIN SELECT * FROM foo INTO target; GET DIAGNOSTICS rows =3D ROW_COUNT; IF rows <> 1 THEN /* do what you want */ END IF; END; Regards Pavel > > Or maybe a parameter to set just prior to exec sql. > set do_not_raise_no_data_found_in_sql=3Dtrue; > select hello_strict(); > > Or something else. > Any good idea is welcome! > > I've been able to transpose to PG all Oracle specific features ((+) left > join operator, connect by, packages, etc). > It was a big challenge almost successful. > But I cannot figure out how to solve this strict/not strict difference in > a smart way. This is my last blocking point. It makes me crazy! > > Thanks & Regards > > ---------------------------------------------------------------------- > Moody's monitors email communications through its networks for regulatory > compliance purposes and to protect its customers, employees and business > and where allowed to do so by applicable law. The information contained i= n > this e-mail message, and any attachment thereto, is confidential and may > not be disclosed without our express permission. If you are not the > intended recipient or an employee or agent responsible for delivering thi= s > message to the intended recipient, you are hereby notified that you have > received this message in error and that any review, dissemination, > distribution or copying of this message, or any attachment thereto, in > whole or in part, is strictly prohibited. If you have received this messa= ge > in error, please immediately notify us by telephone, fax or e-mail and > delete the message and all of its attachments. Every effort is made to ke= ep > our network free from viruses. You should, however, review this e-mail > message, as well as any attachment thereto, for viruses. We take no > responsibility and have no liability for any computer virus which may be > transferred via this e-mail message. > > > --000000000000d9647906057a6ab9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi

so 16. 9. 2023 v=C2=A013:27 odes=C3=ADlatel J= ean-Marc Voillequin (MA) <Jean-Marc.Voillequin@moodys.com> napsal:
Hello everyone,

On Oracle, a no_data_found exception is raised from pl/sql but not from sql= (it returns null). It's well known.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Productio= n

SQL> create or replace function hello return char is
=C2=A0 2=C2=A0 c char;
=C2=A0 3=C2=A0 begin
=C2=A0 4=C2=A0 =C2=A0 =C2=A0select 'a' into c from dual where 1=3D2= ;
=C2=A0 5=C2=A0 =C2=A0 =C2=A0return c;
=C2=A0 6=C2=A0 end;
=C2=A0 7=C2=A0 /

Function created.

SQL> select coalesce(hello(),'<NULL>') from dual;

COALESCE(HELLO(),'<NULL>')
---------------------------------------------------------------------------= -----
<NULL>

SQL> declare
=C2=A0 2=C2=A0 res char;
=C2=A0 3=C2=A0 begin
=C2=A0 4=C2=A0 =C2=A0 =C2=A0res:=3Dhello();
=C2=A0 5=C2=A0 end;
=C2=A0 6=C2=A0 /

declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "JM.HELLO", line 4
ORA-06512: at line 4


On PG, with the strict keyword, we get:

psql (15.2)
Type "help" for help.

JM=3D> create or replace function hello_strict() returns char language p= lpgsql as $function$
JM$> declare
JM$> c char;
JM$> begin
JM$>=C2=A0 =C2=A0select 'a' into strict c where 1=3D2;
JM$>=C2=A0 =C2=A0return c;
JM$> end;$function$;
CREATE FUNCTION

JM=3D> select coalesce(hello_strict(),'<NULL>');

ERROR:=C2=A0 query returned no rows
CONTEXT:=C2=A0 PL/pgSQL function hello_strict() line 5 at SQL statement

JM=3D> do $$declare
JM$> res char;
JM$> begin
JM$>=C2=A0 =C2=A0res:=3Dhello_strict();
JM$> end$$;

ERROR:=C2=A0 query returned no rows
CONTEXT:=C2=A0 PL/pgSQL function hello_strict() line 5 at SQL statement
PL/pgSQL function inline_code_block line 4 at assignment


And without the strict keyword:

JM=3D> create or replace function hello_not_strict() returns char langua= ge plpgsql as $function$
JM$> declare
JM$> c char;
JM$> begin
JM$>=C2=A0 =C2=A0select 'a' into c where 1=3D2;
JM$>=C2=A0 =C2=A0return c;
JM$> end;$function$;
CREATE FUNCTION

JM=3D> select coalesce(hello_not_strict(),'<NULL>');
=C2=A0coalesce
----------
=C2=A0<NULL>
(1 row)

JM=3D> do $$declare
JM$> res char;
JM$> begin
JM$>=C2=A0 =C2=A0res:=3Dhello_not_strict();
JM$> end$$;
DO
JM=3D>

I have tons of functions to migrate from Oracle to PG. They are both called= from SQL or PL/SQL.
I would like to avoid to create two functions (_strict and _not_strict).
A kind of proxy function that is lazy to evaluate its argument would be hel= pful:
select do_not_raise_no_data_found(hello_strict());
The STRICT keyword can be replaced by test of number of returne= d rows

so you can do some like

DECLARE rows int; target record;
BEGIN
=C2=A0 SEL= ECT * FROM foo INTO target;
=C2=A0 GET DIAGNOSTICS rows =3D ROW_C= OUNT;
=C2=A0 IF rows <> 1 THEN
=C2=A0=C2=A0=C2=A0= /* do what you want */
=C2=A0 END IF;
END;
<= br>
Regards

Pavel

=C2=A0

Or maybe a parameter to set just prior to exec sql.
set do_not_raise_no_data_found_in_sql=3Dtrue;
select hello_strict();

Or something else.
Any good idea is welcome!

I've been able to transpose to PG all Oracle specific features ((+) lef= t join operator, connect by, packages, etc).
It was a big challenge almost successful.
But I cannot figure out how to solve this strict/not strict difference in a= smart way. This is my last blocking point. It makes me crazy!

Thanks & Regards

----------------------------------------------------------------------
Moody's monitors email communications through its networks for regulato= ry compliance purposes and to protect its customers, employees and business= and where allowed to do so by applicable law. The information contained in= this e-mail message, and any attachment thereto, is confidential and may n= ot be disclosed without our express permission. If you are not the intended= recipient or an employee or agent responsible for delivering this message = to the intended recipient, you are hereby notified that you have received t= his message in error and that any review, dissemination, distribution or co= pying of this message, or any attachment thereto, in whole or in part, is s= trictly prohibited. If you have received this message in error, please imme= diately notify us by telephone, fax or e-mail and delete the message and al= l of its attachments. Every effort is made to keep our network free from vi= ruses. You should, however, review this e-mail message, as well as any atta= chment thereto, for viruses. We take no responsibility and have no liabilit= y for any computer virus which may be transferred via this e-mail message.<= br>

--000000000000d9647906057a6ab9--