public inbox for [email protected]
help / color / mirror / Atom feedFrom: Pavel Stehule <[email protected]>
To: Jean-Marc Voillequin (MA) <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: no_data_found oracle vs pg
Date: Sat, 16 Sep 2023 16:06:00 +0200
Message-ID: <CAFj8pRAfJy7du1O5fkCbNUPJticxrQpomu7mcPYs2vJNfEKkJw@mail.gmail.com> (raw)
In-Reply-To: <MN2PR20MB273509335B549DEB90AB5724BEF5A@MN2PR20MB2735.namprd20.prod.outlook.com>
References: <MN2PR20MB273509335B549DEB90AB5724BEF5A@MN2PR20MB2735.namprd20.prod.outlook.com>
Hi
so 16. 9. 2023 v 13:27 odesÃlatel Jean-Marc Voillequin (MA) <
[email protected]> 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=2;
> 5 return c;
> 6 end;
> 7 /
>
> Function created.
>
> SQL> select coalesce(hello(),'<NULL>') from dual;
>
> COALESCE(HELLO(),'<NULL>')
>
> --------------------------------------------------------------------------------
> <NULL>
>
> SQL> declare
> 2 res char;
> 3 begin
> 4 res:=hello();
> 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=> 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=2;
> JM$> return c;
> JM$> end;$function$;
> CREATE FUNCTION
>
> JM=> select coalesce(hello_strict(),'<NULL>');
>
> ERROR: query returned no rows
> CONTEXT: PL/pgSQL function hello_strict() line 5 at SQL statement
>
> JM=> do $$declare
> JM$> res char;
> JM$> begin
> JM$> res:=hello_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=> create or replace function hello_not_strict() returns char language
> plpgsql as $function$
> JM$> declare
> JM$> c char;
> JM$> begin
> JM$> select 'a' into c where 1=2;
> JM$> return c;
> JM$> end;$function$;
> CREATE FUNCTION
>
> JM=> select coalesce(hello_not_strict(),'<NULL>');
> coalesce
> ----------
> <NULL>
> (1 row)
>
> JM=> do $$declare
> JM$> res char;
> JM$> begin
> JM$> res:=hello_not_strict();
> JM$> end$$;
> DO
> JM=>
>
> 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 = 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=true;
> 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 in
> 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 this
> 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 message
> 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 keep
> 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.
>
>
>
view thread (6+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: no_data_found oracle vs pg
In-Reply-To: <CAFj8pRAfJy7du1O5fkCbNUPJticxrQpomu7mcPYs2vJNfEKkJw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox