Re: no_data_found oracle vs pg

Hi

so 16. 9. 2023 v 13:27 odesílatel Jean-Marc Voillequin (MA) <
Jean-Marc(dot)Voillequin(at)moodys(dot)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=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.
>
>
>