Hi Tom,
Is this behaviour because in case of SQL, for ANSI standard, the NO DATA FOUND is not an error condition (rather it is an indicator of so to say, END-OF-FILE or EOF). This just tells the system that what you were trying to read is no more to be found.

In this regard, than it appears that Oracle PL/SQL and SQL behave differently?

SQL, to be ANSI complient, does not treat it as an ERROR whereas PL/SQL (a copyrighted language), treats it as an Error and jumps to the exception block.

Is this assertion correct? Is this the reason why we get an error with PL/SQL block but not with SQL SELECT?
Thanks,

Hi Tom,
This is something misleading us. I never faced this. Because i always use proper exceptions in functions also. In the same function, if i add no_data_found exception, it throws error. How this is coming ? Also 1 more doubt, as in your reply,
select func_foo from dual will return no rows selected. But if i executed it shows 1 row selected. How is this differs
?

also if i call the function in pl/sql block as below, it throws exception.

declare
l_x varchar2(1) ;
begin
l_x := func_foo ;
end ;

tx,
Balasubramanian.R

Hi Balasubramanian,

I think TOM's statement clearly states that when you run stored function from SQL you will never get EXCEPTION. He didnt say that you wont get EXCEPTION when you call the function in the PL/SQL block. Am I right TOM?

>> no data found in a function called from sql just says
>> "ok, no more data, please stop"

Regards,
Sajid Anwar
London

Ho Tom,
I think one of the reason for my earlier statement was because this no data found condition is represented by a different SQLCODE within PL/SQL. The SQLCODE is NOT -1403 but is rather +100 when this condition is encountered within PL/SQL. This difference in the value of SQLCODE is what caused me to think that, yes, there is some difference somewhere, may not be as big, but there seems to be a difference?
Is this thought process correct?

OPS$KKISHORE @ ORA9203.WORLD> set serveroutput on
OPS$KKISHORE @ ORA9203.WORLD> begin
2 raise no_data_found ;
3 exception
4 when others
5 then
6 dbms_output.put_line('Error Code:'||SQLCODE||chr(10)||'Error Text:'||SQLERRM) ;
7 end ;
8 /
Error Code:100
Error Text:ORA-01403: no data found

PL/SQL procedure successfully completed.

OPS$KKISHORE @ ORA9203.WORLD>

Tom,

I swear I´d never noticed that.

Thanks.

Amazing, in 8 years of Oracle, I'd never come across this until a few days ago - always learning ;-)

2 Questions:
- Where is this documented in the manuals (8.1.7)? I've been looking for this but can't find it.
- As you mentioned above, it depends on how the client handles the exception. When I execute "select func_foo from dual;", is the SQL engine acting as the client of PL/SQL?

Thx.

I am getting No data found error For the following SQL statement
declare
age_no number(5);
begin
for cursor_fields in (select * from People where name like '%R%')
loop
select age into age_no from PeopleDetails where city = cursor_fields.city
dbms_output.put_line('The city name and age are');
dbms_output.put_line(cursor_fields.city);
dbms_output.put_line(age_no);
end loop;
exception
when NO_DATA_FOUND then
dbms_output.put_line('No data available for this record');
null;
end


My problem is if the Query 'select age .... 'did not find any records then it is throwing the error. 'ORA-01403: no data found'. I am catching that in exception but I want to go back into the loop and continue for the next cursor element in the for loop. And also do I need to modify the 'Select age into age_no.....' Query . Give some suggestions on this . It is very urgent.

Tom,
I did not ask proper question but it was some helpful to me.
I have written code like what you have said before only. But my intention is to verify my view. I caught the exception in the inner block only. I have another problem here.

select name into l_name from people where city ='xxx';

If the value 'xxx' does not exist in the database then it is throwing the exception NO_DATA_FOUND. Should we change the query in order to avoid this exception. BEcause in general sql query like
select name from people where city = 'xxx'; if the record is not found it does not throw exception . How to solve this problem.

Tom

The problem that i have is i have used an insert select * from to collect and process records. Within the insert i have a function that does some processing.
I have come across this behaviour that if the sql query within the function doesn't return any data it just returns a null to the select statement the function is used in, ie:

insert into ..
select ..
..
..
f_my_function(...),
..
from my_table;

So obviously i can force the function to return something by catching any known exceptions within the function code but what about unknown errors?? How will i know if there is a bug in the function?

Just like if you used a WHEN OTHERS any unexpected error is ignored, and this will exhibit the same behaviour, i wont be able to tell when my function has returned any unexpected error. Is there any way around this or should i just be coding it differently??

Tom

Below illustrates the problem i'm getting:

16:36:00 OPT1@OP1>drop table t1;

Table dropped.

Elapsed: 00:00:00.00
16:36:00 OPT1@OP1>drop table t2;

Table dropped.

Elapsed: 00:00:00.00
16:36:01 OPT1@OP1>create table t1 (c1 number(10));

Table created.

Elapsed: 00:00:00.00
16:36:01 OPT1@OP1>create table t2 (c2 number(10));

Table created.

Elapsed: 00:00:00.00
16:36:01 OPT1@OP1>
16:36:01 OPT1@OP1>insert into t1 values (1);

1 row created.

Elapsed: 00:00:00.00
16:36:01 OPT1@OP1>insert into t1 values (2);

1 row created.

Elapsed: 00:00:00.00
16:36:01 OPT1@OP1>
16:36:01 OPT1@OP1>create or replace function f1(p1 in number)
16:36:01 2 return number
16:36:01 3
16:36:01 4 as
16:36:01 5
16:36:01 6 v1 number(10);
16:36:01 7
16:36:01 8 begin
16:36:01 9
16:36:01 10 select c1
16:36:01 11 into v1
16:36:01 12 from t1
16:36:01 13 where c1 = p1;
16:36:01 14
16:36:01 15 return v1;
16:36:01 16
16:36:01 17
16:36:01 18
16:36:01 19 end f1;
16:36:01 20 /

Function created.

Elapsed: 00:00:00.00
16:36:01 OPT1@OP1>
16:36:01 OPT1@OP1>
16:36:01 OPT1@OP1>insert into t2
16:36:01 2 select f1(1)
16:36:01 3 from dual;

1 row created.

Elapsed: 00:00:00.00
16:36:01 OPT1@OP1>
16:36:01 OPT1@OP1>select * from t2;

C2
----------
1

1 row selected.

Elapsed: 00:00:00.00
16:36:02 OPT1@OP1>
16:36:02 OPT1@OP1>
16:36:02 OPT1@OP1>insert into t2
16:36:02 2 select f1(3)
16:36:02 3 from dual;

1 row created.

Elapsed: 00:00:00.00
16:36:02 OPT1@OP1>
16:36:02 OPT1@OP1>select * from t2;

C2
----------
1


2 rows selected.

Elapsed: 00:00:00.00
16:36:02 OPT1@OP1>
16:36:02 OPT1@OP1>set autoprint on
16:36:02 OPT1@OP1>variable b1 number
16:36:02 OPT1@OP1>exec :b1 := f1(1);

PL/SQL procedure successfully completed.


B1
----------
1

Elapsed: 00:00:00.00
16:36:02 OPT1@OP1>variable b2 number
16:36:02 OPT1@OP1>exec :b2 := f1(3);
BEGIN :b2 := f1(3); END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "OPT1.F1", line 10
ORA-06512: at line 1



B2
----------


Elapsed: 00:00:00.01
16:36:03 OPT1@OP1>

You can see that the function doesn't fall over when used in a sql statement but will if executed from sqlplus into a bind variable.

So you are right the function will just return null to the calling sql, but i would also like to know if the function is falling over and thus returning the null, so that the insert does not insert nulls into my table.
i want the fact that it has fallen over be known to the sql statment using the function so that the whole insert fails.

Hope this is clearer.

Thanks

ok then, so how can i tell if it has fallen over?! The only way i can think of is to catch the exception in the function and log the error?

the client is sql. I am calling the function within a sql statement and inserting into a table.

procedure myproc
..

execute immediate
'INSERT INTO mytable
select col1, myFunction(col2), col3, CASE col4 etc..
from myOtherTable';
..

end myproc;

This is executed from a stored procedure (using execute immediate since I also am using a CASE expression).

At the moment if the function falls over and returns no_Data_found to the sql, then the sql just says fine no more data for that row and carries on with the rest of the insert -- that is bad, i don't want it to do that.

If the function falls over and returns no_data_found i want the whole insert to fail and an error to be returned to whichever client is calling the procedure. This is what i can't work out how to do. The sql just carries on as if nothing has happened. The sql acts like it gets a null returned from the function and thats what is inserted into myTable.

Please tell me how i can stop the insert from completing.

Hey dxl ... <quote>ok then, so how can i tell if it has fallen over?!</quote> ... take it out of the SQL and code for it.

An exception (any) won't propagate out when executing the function from SQL ... there is no way around it. So rather than insisting on it just think of something else.

If you just want not to insert when your function returns a not null value then something like this would do:

SQL> insert into t2
  2  select t.c from
  3  (
  4  select f1(1) c from dual union all
  5  select f1(2) c from dual union all
  6  select f1(3) c from dual union all
  7  select f1(5) c from dual
  8  ) t
  9  where t.c is not null
 10  ;

2 rows created.

<quote>how i can stop the insert from completing?</quote>
Do not perform the insert at all when detecting the _problem_ ... hence you may need to split the operation into multiple steps.

It is a bit theoretical right now ... why don't you post your specific, complete problem ... you may find you don't need the pl/sql function at all (and still do the whole thing in one step) ... you may also find you don't need the dynamic sql either (the fact that you use a CASE in there is not, in itself, a justification for abandoning the static SQL).

I mean for the T1, T2 test case you've shown, one could:
create table t1 (c1 number(10) not null primary key);
create table t2 (c2 number(10) not null primary key, constraint fk foreign key (c2) references t1(c1));
and hence ...

SQL> insert into t2 select f1(3) from dual;
insert into t2 select f1(3) from dual
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."T2"."C2")

The point is, one cannot really extrapolate a solution for your real problem based on this test case.

As for ...
execute immediate
'INSERT INTO mytable
select col1, myFunction(col2), col3, CASE col4 etc..
from myOtherTable';

maybe you could have a view over mytable with an instead of trigger to compute the result of myFunction(:new.col2) and raise_application_error whenever whatever error ... again, one would need a real problem to suggest a _real_ solution.

Cheers.
 

create table t1 (c1 number(10));

create or replace function f1 return number
as
v1 number(10);
begin
select 10
into v1
from dual
where 1=2;
return v1;
exception
when no_data_found then
raise_application_error(-20001,
'f1 failed with no data found');
end f1;

select f1 from dual;

Without the exception handler, this returns null. With the exception handler, you get the error.
You only need to check for the NO_DATA_FOUND, as this is an exceptional exception. All other exceptions will cause the select invoking the function to fail (and also cause the failure of an INSERT/CREATE TABLE AS... using the select)

Gary,

no_data_found or raise_application_error or any other exception in f1 will not propagate out when f1 is called from a sql statement ... your 'select f1 from dual' will return one row back with null in it ... no exception.

ops$tkyte@ORA9IR2> create or replace function f  return number
  2  as
  3  begin
  4          raise program_error;
  5  end;
  6  /
 
Function created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select f from dual;
select f from dual
       *
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at "OPS$TKYTE.F", line 4

ops$tkyte@ORA9IR2> create or replace function f  return number
  2  as
  3  begin
  4          raise_application_error( -20001, 'Not in my experience' );
  5  end;
  6  /
 
Function created.
 
ops$tkyte@ORA9IR2> select f from dual;
select f from dual
       *
ERROR at line 1:
ORA-20001: Not in my experience
ORA-06512: at "OPS$TKYTE.F", line 4
 

<b>and using the way back machine:</b>

tkyte@DEV716> create or replace function f  return number
  2  as
  3  begin
  4          raise program_error;
  5  end;
  6  /
 
Function created.
 
tkyte@DEV716>
tkyte@DEV716> select f from dual;
ERROR:
ORA-06501: PL/SQL: program error
ORA-06512: at "TKYTE.F", line 4
ORA-06512: at line 1
 
 
 
no rows selected
 
tkyte@DEV716>
tkyte@DEV716> create or replace function f  return number
  2  as
  3  begin
  4          raise_application_error( -20001, 'Not in my experience' );
  5  end;
  6  /
 
Function created.
 
tkyte@DEV716> select f from dual;
select f from dual
       *
ERROR at line 1:
ORA-06571: Function F does not guarantee not to update database
 


ops$tkyte@ORA734> create or replace function f  return number
  2  as
  3  begin
  4          raise program_error;
  5  end;
  6  /
 
Function created.
 
ops$tkyte@ORA734>
ops$tkyte@ORA734> select f from dual;
ERROR:
ORA-06501: PL/SQL: program error
ORA-06512: at "OPS$TKYTE.F", line 4
ORA-06512: at line 1
 
 
 
no rows selected
 
ops$tkyte@ORA734>
ops$tkyte@ORA734> create or replace function f  return number
  2  as
  3  begin
  4          raise_application_error( -20001, 'Not in my experience' );
  5  end;
  6  /
 
Function created.
 
ops$tkyte@ORA734> select f from dual;
ERROR:
ORA-20001: Not in my experience
ORA-06512: at "OPS$TKYTE.F", line 4
ORA-06512: at line 1
 
 
 
no rows selected


ops$tkyte@ORA806> @test
 
Function created.
 
select f from dual
       *
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at "OPS$TKYTE.F", line 4
ORA-06512: at line 1
 
 
 
Function created.
 
select f from dual
       *
ERROR at line 1:
ORA-20001: Not in my experience
ORA-06512: at "OPS$TKYTE.F", line 4
ORA-06512: at line 1
 

<b>It has always worked that way....</b>
 

Tom

Thanks, re raising the exception worked fine. For some reason i thought i'd already tried that but obviously i didn't! I guess i kinda assumed that if it wasn't propagating the no data found 1403 error then it wouldn't propagate any other when called from a sql statement. This is of course wrong! No wonder you couldn't see what my problem was!

-- to reviewer Gabe : Thanks for your input and time taken to look at this however i'm afraid you're tests aren't quite correct:

" ...An exception (any) won't propagate out when executing the function from SQL ... there is no way around it..."

This is not true see below:

09:04:06 OPT1@OP1>create table t1 (c1 number(10));

Table created.

Elapsed: 00:00:00.00
09:04:06 OPT1@OP1>create table t2 (c2 number(10));

Table created.

Elapsed: 00:00:00.00
09:04:06 OPT1@OP1>
09:04:06 OPT1@OP1>insert into t1 values (1);

1 row created.

Elapsed: 00:00:00.00
09:04:06 OPT1@OP1>
09:04:06 OPT1@OP1>insert into t1 values (2);

1 row created.

Elapsed: 00:00:00.00
09:04:06 OPT1@OP1>
09:04:06 OPT1@OP1>create or replace function f1(p1 in number)
09:04:06 2 return number
09:04:06 3
09:04:06 4 as
09:04:06 5
09:04:06 6 v1 number(10);
09:04:06 7
09:04:06 8 begin
09:04:06 9
09:04:06 10 select c1
09:04:06 11 into v1
09:04:06 12 from t1
09:04:06 13 where c1 = p1;
09:04:06 14
09:04:06 15 return v1;
09:04:06 16
09:04:06 17 exception
09:04:06 18 when no_data_found then
09:04:06 19 raise program_error;
09:04:06 20
09:04:06 21 end f1;
09:04:06 22 /

Function created.

Elapsed: 00:00:00.01
09:04:07 OPT1@OP1>
09:04:07 OPT1@OP1>insert into t2
09:04:07 2 select f1(1)
09:04:07 3 from dual;

1 row created.

Elapsed: 00:00:00.00
09:04:07 OPT1@OP1>
09:04:07 OPT1@OP1>select * from t2;

C2
----------
1

1 row selected.

Elapsed: 00:00:00.00
09:04:07 OPT1@OP1>insert into t2
09:04:07 2 select f1(3)
09:04:07 3 from dual;
select f1(3)
*
ERROR at line 2:
ORA-06501: PL/SQL: program error
ORA-06512: at "OPT1.F1", line 19
ORA-01403: no data found
ORA-06512: at line 1


The program error exception will be re raised it was only the no data found that sql doesn't see as an error.

Also the reason i only posted a test case and not the complete real problem is that my real problem is horrendously complicated (I really did need to use CASE, couldn't see any other way around it except for some hideous DECODES) therefore having used this site for some time i knew TOM would not appreciate seeing my tables with no data and horrible sql, so i posted a very simple runnable problem. -- thanks anyway though!

(Gary from sydney -- cheers you were spot on!)

Didn't have access to a database when I sent that reply ... seriously thought I tried that before. Guess there is something to learn from embarrassing blunders as well.

Thanks Tom.

Tom,

I'm getting this error while tracing through a function in a package via a procedure editor. I've been looking up this message and I'm confused by my findings. This thread leads me to believe it is a self-explanitory message, meaning "there's no data for the conditions you specified".
The oracle error messages and metalink seem to give me the impression however that it is the result of the end of records being fetched. Could you clarify this? In my case, I know there's data because I can run the select that loads the cursor that gets results. The no data found occurs when attempting to open the cursor. The code looks like this:

FUNCTION GET_INFO (pParameter IN NUMBER) RETURN VARCHAR2

IS
vPATS VARCHAR2(48);

CURSOR cPATS IS
SELECT
col1
FROM
tab1
WHERE
id = pParameter;

BEGIN
OPEN cPATS;<-----NO_DATA FOUND Occurs here
FETCH
cPATS
INTO
vPATS;
CLOSE cPATS;

RETURN vPATS;
END;
Metalink article is this :</code> http://metalink.oracle.com/metalink/plsql/ml2_gui.startup <code>
Thanks a ton.

I thought so too, that's what I needed to hear.
I've since realized the code is fine and TOAD's procedure editor is the reason for this bizarre occurrence I ran the select with the function call (without tracing) and it worked fine.
I'm starting to see why a lot of people detest GUIs when I hit stupid misleading errors like this. Thanks for the clarification.

Tom,

Would you mind giving an example of using implicit cursors for a singleton select, something simple like:

select col1
into var1
from t
where col1 = 5;

Ok here's what I don't understand. I asked because of what I found in 'Beginning Oracle Programming', that has an example like the one I posted, in a pl/sql block that says:

"Due to the errors that may be encountered, people are using singleton SELECTs less and less these days, preferring to use implicit cursors instead. There is no need to declare local variables to hold values selected in an implicit cursor and there is no need to do exception handling for the cases where the query returns more or less than one row."

I like the sound of this, so I wanted to see an example so I knew for sure how to do it. The statement in book example is this:

set serverout on
declare
l_empno emp.empno%type;
l_ename emp.ename%type;
begin
select empno, ename
into l_empno, l_ename
from emp
where rownum = 1;
dbms_output.putline(l_empno||';'||l_name);
end;
Thanks for taking the time for us newbies too :)

The title of the section is "Singleton selects". I'd never heard that before I don't know what that means. As far as the example in the book goes, it looks like a bad one for either an implicit or explicit cursor. If implicit, then they didn't need to declare the variables l_empno and l_ename. If explicit, then it should look like your example.

In any event I understand the difference. This was on page 384-385 if you can track down a copy and make any sense out of it.

create or replace function f return integer
as
z integer;
begin
select rownum into z from all_tables where rownum < 3;
return z;
end;

select f from dual;

This returns no data at all. No exception, no data, unlike the NO_DATA_FOUND case where a null row is returned. We've encountered both of these in obscure cases where the code otherwise looked sound. Hard to debug when no exception is propagated. It's job security to know all these caveats, though.

BTW, Change the constant "3" above to the value "1" and you'll see the NO_DATA_FOUND case returns a NULL row.

Oops. Disregard that. This behavior is not reproduceable in Sql*Plus: in this case it is definitely the client I'm using, PL/SQL Developer.

Sorry.

I'm crazy I wasn't thinking went I thought you didn't have to declare variables. I got mixed up because Oracle takes care of all the cursor related stuff.

Also just FYI, this was in Beginning Oracle Programming, not Expert One on One (maybe your face was on BOP too at one point but not on my copy :)). I already incorrectly assumed you had one of those kicking around on a different post. After all, why would you, it's a beginner's book.

Hallo Tom,

I am confused.

please have a look at your very first answer in this thread.
Has something changed in 9.2.0.5 ?

<QUOTE>
tkyte@ORA716> select func_foo from dual;

no rows selected
</QUOTE>

I try to reproduce this behaviour, however, I GET A NULL ROW
INSTEAD OF NO ROW (!?)


C:\>sqlplus

SQL*Plus: Release 9.2.0.1.0 - Production on Mo Feb 7 14:59:10 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Benutzernamen eingeben: ....
Kennwort eingeben:

Verbunden mit:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for Linux: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

SQL> create or replace function func_foo return varchar2 is
  2  begin
  3     raise no_data_found;
  4  end;
  5  /

Funktion wurde erstellt.

SQL> set null nada
SQL> select func_foo from dual;

FUNC_FOO
--------------------------------------------------------------------------------
nada

SQL> set feedb on
SQL> /

FUNC_FOO
--------------------------------------------------------------------------------
nada

1 Zeile wurde ausgewõhlt.

SQL> exit
Verbindung zu Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production beendet




What is wrong here ?
What do I miss ?

Thank you 

sorry,

you got

tkyte@ORA716> select func_foo from dual;

no rows selected

(7.1.6.2.0)


,
I get
SQL> select func_foo from dual;

FUNC_FOO
--------------------------------------------------------------------------------
nada

1 Zeile wurde ausgewõhlt.

REM "1 row selected " in English

I didn't expect do get an exception, I expect to get
no row instead of one row consistincg of nulls. 

Hi I have a package which contains one function and one procedure, the function is called by procedure similar to this:

create or replace package body
is
function f1(p_tab varchar2(30))
return date
as
l_date date;
begin
select fec
into l_date
from tmov
where tabname = p_tab;
return l_date;
exception
when NO_DATA_FOUND then
dbms_output.put_line ('SQLERRM: ' || sqlerrm);
when others then
dbms_output.put_line ('SQLERRM: ' || sqlerrm);
end;

procedure pr_tmm_contrato_contrato
as
l_fecha_trata date;
begin
l_fecha_trata := f1('T1');
end;
begin
dbms_output.put_line('HIHIHI');
end;
/


here if NO_DATA_FOUND occurs the process should stop but it doesnt. Should I add raise; after dbms_output.put_line ('SQLERRM: ' || sqlerrm); ? Or shall I convert the function into a procedure with an out parameter (l_date)

Just curiousity, i added dbms_output.put_line('HIHIHI'); inthe body but that doesnt do anything right? Nonsense?

thanks

hi your code should be smth like this

create or replace package body
is
function f1(p_tab varchar2(30))
return date
as
l_date date;
begin
select fec
into l_date
from tmov
where tabname = p_tab;
return l_date;
exception
when NO_DATA_FOUND then
dbms_output.put_line ('(f1) SQLERRM: ' || sqlerrm);
raise;
when others then
dbms_output.put_line ('(f1) SQLERRM: ' || sqlerrm);
raise;
end;

procedure pr_tmm_contrato_contrato
as
l_fecha_trata date;
begin
l_fecha_trata := f1('T1');
end;
begin
dbms_output.put_line('HIHIHI');
end;
/

add the raise statement, this way with dbms_output you know where in your package failed and with raise you stop the processing, the caller should not handle your function exceptions in this case

or there is another approach:

create or replace package body
is
function f1(p_tab varchar2(30))
return date
as
l_date date;
begin
select fec
into l_date
from tmov
where tabname = p_tab;
return l_date;
exception
when NO_DATA_FOUND then
dbms_output.put_line ('(f1) SQLERRM: ' || sqlerrm);
return NULL;
when others then
dbms_output.put_line ('(f1) SQLERRM: ' || sqlerrm);
raise;
end;

procedure pr_tmm_contrato_contrato
as
l_fecha_trata date;
begin
l_fecha_trata := f1('T1');
if ( l_fecha_trata is null ) then
raise NO_DATA_FOUND;
end if;
exception
when NO_DATA_FOUND etc etc etc.
end;
begin
dbms_output.put_line('HIHIHI');
end;
/

although I personally prefer the first approach since you know where fails, in this small piece of code it´s easy to see the point where the failure occurs if the second approach is used, but if you have a huge package with recursive functions calls then it´s hard to handle no_data_found exception if there are several functions which can cause the exception

I think the best is handle the exception at lower level possible, at least write to a log table then reraise to outer block if you wanna stop processing

Hi

You are right, there is no point to catch those errors. The point I try to make is if you catch the error at lower level you can know exactly where in the program failed. In this example the code is simple, if the code is huge and complex if we dont catch the exceptions at low level it´s will be hard to debug.

Say we have this code


select 1
into x
from t1
where rownum < 2;

select 1
into y
from t2
where rownum < 2;

select 1
into z
from t3
where rownum < 2;

exception
when no_data_found ......

if we use a generic exception handling at end of the code then we wouldnt be able to know which query failed

Hi

Say we have this code

declare
procedure p1
as
x number;
begin
select 1
into x
from emp
where empno = 7369;
exception
when no_data_found then
log_error('p1');
raise;
end;
procedure p2
as
x number;
begin
select 1
into x
from emp
where empno = 1;
exception
when no_data_found then
log_error('p2');
raise;
end;
procedure p3
as
x number;
begin
select 1
into x
from emp
where empno = 7521;
exception
when no_data_found then
log_error('p3');
raise;
end;
procedure p4
as
x number;
begin
select 1
into x
from emp
where empno = 3;
exception
when no_data_found then
log_error('p4');
raise;
end;
begin
p1;
p2;
p3;
p4;
end;
/


We want to log the errors in a table so in this case it makes sense to catch the exception at lower level no? Otherwise how can we know where the procedure failed (in 10g we can use back trace but not before...). Although these queries arent expected to fail noone can ever ensure that it wont fail for any reason.

If we didnt use exceptions we see messages like this

declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 15
ORA-06512: at line 40

We then have to check the whole procedure and see which procedure/function caused this error.

We do this in PRO*C, always log the error to a table with the function name which caused the error.

This is no good... ?

Here is a test case where even a raised error is being
ignored.

Happened in a real-life situation. Here is a dummy function
to simulate the case. The function raises an exception when
passed in '1' and not when passed in '2'.

create or replace function myfunc(i number) return number
is
vii number;
begin

if i = 1 then -- force error: many values into one var
select customerid into vii from customer where customerid like '1%';
end if;

return i;

end;


Test the error is raised:

SQL> select myfunc(1) from dual;
select myfunc(1) from dual
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows


Great but now look:

SQL> select myfunc(x) from (
2 select 2 x from dual union all select 1 x from dual);

MYFUNC(X)
----------
2

The error raised in the function is ignored (!?!)

Just replace UNION ALL with UNION and see the result. You will be surprised :)

Here's a more generic test case.
(I could repeat on 9.2.0.2.1 and 10.2.0.3.0)


SQL> create or replace function myfunc(i number) return number
2 is
3 vii number;
4 begin
5
6 if i = 1 then -- force error: many values into one var
7 select object_name into vii from user_objects;
8 end if;
9
10 return i;
11
12 end;
13 /

Function created.

SQL>
SQL>
SQL> select myfunc(1) from dual;
select myfunc(1) from dual
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "ADMIN.MYFUNC", line 7



SQL> select myfunc(x) from (
2 select 2 x from dual union all select 1 x from dual);

MYFUNC(X)
----------
2

What happened to that error ????

ops$tkyte%ORA10GR2> create or replace function myfunc(i number) return number
  2  is
  3  vii number;
  4  begin
  5  if i = 1 then -- force error: many values into one var
  6  select object_name into vii from user_objects;
  7  end if;
  8   return i;
  9  end;
 10  /

Function created.

ops$tkyte%ORA10GR2> select myfunc(1) from dual;
select myfunc(1) from dual
       *
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "OPS$TKYTE.MYFUNC", line 6


ops$tkyte%ORA10GR2> select myfunc(x) from (select 2 x from dual union all select 1 x from dual);
ERROR:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "OPS$TKYTE.MYFUNC", line 6



no rows selected



ops$tkyte%ORA10GR2> select * from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for Linux: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production




ops$tkyte%ORA9IR2> select myfunc(1) from dual;
select myfunc(1) from dual
       *
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "OPS$TKYTE.MYFUNC", line 6


ops$tkyte%ORA9IR2> select myfunc(x) from (select 2 x from dual union all select 1 x from dual);
ERROR:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "OPS$TKYTE.MYFUNC", line 6



ops$tkyte%ORA9IR2> select * from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> create or replace function myfunc(i number) return number
2 is
3 vii number;
4 begin
5
6 if i = 1 then -- force error: many values into one var
7 select object_name into vii from user_objects;
8 end if;
9
10 return i;
11
12 end;
13 /

Function created.

SQL> select myfunc(x) from (select 2 x from dual union all select 1 x from dual);

MYFUNC(X)
----------
2

SQL>

*************

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.2.1 - Production
PL/SQL Release 9.2.0.2.1 - Production
CORE 9.2.0.2.0 Production
TNS for 32-bit Windows: Version 9.2.0.2.0 - Production
NLSRTL Version 9.2.0.2.0 - Production

SQL> create or replace function myfunc(i number) return number
2 is
3 vii number;
4 begin
5
6 if i = 1 then -- force error: many values into one var
7 select object_name into vii from user_objects;
8 end if;
9
10 return i;
11
12 end;
13 /

Function created.

SQL> select myfunc(x) from (select 2 x from dual union all select 1 x from dual);

MYFUNC(X)
----------
2

SQL>


Would it be reasonable to refer to the behaviour I'm seeing in 10.2.0.3.0 and 9.2.0.2.1 as Oracle bugs ? Any other possible explanation ?

Ah!, I get different results with different versions of SQLPlus.

The results posted earlier were with SQLPlus Release 9.2.0.1.0.

When I repeat the test with SQLPlus Release 10.1.0.5.0 I get the same behaviour as you.

We'll be upgrading away from the older version of SQLPlus shortly but as far as lessons learned go, is this a SQLPlus bug ?

Hi Tom,

I've tried searching, but can't find anything that answers my query. I know that if you're calling a function from within SQL and the function returns the no_data_found exception, then SQL doesn't consider that to be an exceptional situation, and no error is raised.

However, I need to know whether any other exceptions that SQL will handle in the same way, or if it's just no_data_found that is handled like that.

Can you help?

Thanks in advance!

Yes, I get that SQL is the client in question here, and I get that it handles some of the exceptions that may be passed to it - so far, I've identified no_data_found and too_many_rows (which surprised me!) as errors that are handled by the SQL engine/client.

But I need to know if these are the only two errors that get handled by SQL, or whether there are others. (I'm trying to implement error handling within a function that gets called from an "insert as select... from function" statement. Unfortunately, no_data_found is one of those errors, but hey ho!)

ops$tkyte%ORA10GR2> create or replace function f return numTabType
  2  pipelined
  3  as
  4          l_x   varchar2(1);
  5  begin
  6          select * into l_x
  7            from (select * from dual union all select * from dual);
  8
  9          pipe row(1);
 10          return;
 11  exception
 12          when too_many_rows
 13          then
 14                  dbms_output.put_line( 'too many rows...' );
 15                  RAISE;
 16  end;
 17  /

Function created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop table t;

Table dropped.

ops$tkyte%ORA10GR2> create table t ( x number );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t
  2  select * from table(f);<b>
too many rows...</b>
select * from table(f)
                    *
ERROR at line 2:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "OPS$TKYTE.F", line 15

After further tests, it was a TOADism that caused the too_many_rows to be handled (but only in the select function from dual format - select * from table(cast(function as whatever)) worked - well, errored - as expected!)

That'll teach me not to test first in SQLPlus!

Thanks very much for your help with this.

We have recently migrated to Oracle 10g (v. 10.2.0.3.0)

after the migration, One of the Pro*C executables started giving No Data found errors.

Condition used to check the fatal errors is sqlca.sqlcode < 0. if this condition is satisfied, exe prints the error message and exits gracefully.
"No data found" error can be ignored and process can be continued. so, this error checking was working just fine.

But now, in some cases, we are getting "negative" 1403 error for no data found; thus forcing the exe to exit.

Could you tell us in which case SQL raises negative error for no data found? we are not able to find the exact business scenario. All the oracle documents i have read, say "No data found" is raised with a positive error code (either +100 or +1403).

Thanks in advance.

static void process()
{
EXEC SQL BEGIN DECLARE SECTION;
    varchar username[35];
EXEC SQL END DECLARE SECTION;

    exec sql declare c cursor for
    select username from all_users where 1=0;
    exec sql open c;
    sqlca.sqlcode = 0;
    EXEC SQL FETCH c INTO :username;
    printf( "sqlca.sqlcode = %d\n", sqlca.sqlcode);
    EXEC SQL CLOSE c;

    sqlca.sqlcode = 0;
    exec sql select 'hello world' into :username from all_users where 1=0;
    printf( "sqlca.sqlcode = %d\n", sqlca.sqlcode);
}

[tkyte@dellpe test]$ ./test

Connected to ORACLE as user: /

sqlca.sqlcode = 1403
sqlca.sqlcode = 1403

Hi Tom,

I've been programming in pl/sql since 1994 and never noticed this feature before (unless my memory is going). However, I've managed to get my query to re-raise the exception when the function it calls raises no_data_found. Here's how:

CREATE OR REPLACE FUNCTION fred
RETURN VARCHAR2
IS
lv_fred_val VARCHAR2(1);
le_ansi_no_data_found EXCEPTION;
PRAGMA EXCEPTION_INIT (le_ansi_no_data_found,-100);
BEGIN
SELECT d.dummy
INTO lv_fred_val
FROM dual d
WHERE 2 = 1;
RETURN lv_fred_val;
EXCEPTION
WHEN no_data_found THEN
RAISE le_ansi_no_data_found;
END fred;
/

Function created


SELECT fred FROM dual;

ORA-00100: no data found
ORA-06512: at "MDI.FRED", line 15
ORA-01403: no data found


Is this a reliable solution, or could it be foiled by some system setting? Even if it isn't, I could just replace the RAISE with a RAISE_APPLICATION_ERROR to give a -20000 exception: that's got to be fool-proof.

Regards
Phil

The 10gR2 note on NO_DATA_FOUND says

"Because this exception is used internally by some SQL functions to signal completion, you should not rely on this exception being propagated if you raise it within a function that is called as part of a query." (my bolding added)

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:317260900346187160#1351196600346787943

It is the one exception that rues them all.

Hi Tom,
The below behaviour is confusing me, was it always like this. 
Even if there is no data foubd by the query why is the value 0 getting set in the variable?
And why when count is removed it raises a no_data_found?


SQL> set serveroutput on;
SQL> declare
  2  v_no integer:=3;
  3  begin 
  4  select count(1) into v_no from dual
  5  where 1=2;
  6  dbms_output.put_line('Test '||v_no);
  7  end;
  8  /
Test 0

PL/SQL procedure successfully completed.

SQL> declare
  2  v_no integer:=3;
  3  begin 
  4  select 1 into v_no from dual
  5  where 1=2;
  6  dbms_output.put_line('Test '||v_no);
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for Linux: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

SQL>  

rajesh@ORA10GR2> create or replace function f(x number)
  2  return varchar2
  3  as
  4     l_ename varchar2(30);
  5  begin
  6     select ename
  7     into l_ename
  8     from emp
  9     where deptno = x;
 10
 11  exception
 12             when no_data_found then
 13                     return null;
 14             when too_many_rows then
 15                     return l_ename;
 16             when others then
 17                     raise_application_error (-20474,sqlerrm);
 18  end;
 19  /

Function created.

Elapsed: 00:00:00.51
rajesh@ORA10GR2>
rajesh@ORA10GR2> variable y varchar2(30);
rajesh@ORA10GR2> exec :y := f(10);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
rajesh@ORA10GR2> print y;

Y
--------------------------------
CLARK

rajesh@ORA10GR2>
rajesh@ORA10GR2>

Tom:

When Too_Many_Rows Exception raises, Oracle assigns the first value into that variable. I thought in this case l_ename should be null but it hold the value CLARK. Is this a right behaviour? Is that mentioned in doc's that i behaves like this? I dont find any details about it.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/errors.htm#sthref1962

Hi Tom,


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> set serveroutput on
SQL> 
SQL> create or replace function f1 return number 
  2  is
  3   x number;
  4  begin
  5  select 1 into x from dual where 1=2;
  6  return 5;
  7  end;
  8  /

Function created.

SQL> select f1 from dual;

        F1
----------


1 row selected.
############################# 

here it has to show zero record and i tried in 9i too same,didnt 
reproduce as "0 Row SELECTED".(I understood why it is not throwing error :) but have doubt
why it is showing one row selected with null should given as no rows selected).
Is it like we should not rely whether it will give 0 rows or 1 row??

#######################
I was just doing somemore analysis on that with exception_init and have some doubt with 
exception_init fro no_dat_found,Please see below testcase.

SQL> 
SQL> declare
  2  hello exception;
  3  pragma exception_init(hello ,100);
  4  begin
  5  null;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> 
SQL> declare
  2  hello exception;
  3  pragma exception_init(hello ,-1403);  --------they should include -1403 
  4  begin
  5  null;
  6  end;
  7  /
hello exception;
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00701: illegal ORACLE error number -1403 for PRAGMA EXCEPTION_INIT


SQL> 
SQL> begin
  2   dbms_output.put_line(sqlerrm(-1403));
  3   dbms_output.put_line(sqlerrm(100));
  4  end;
  5  /
ORA-01403: no data found
ORA-01403: no data found

PL/SQL procedure successfully completed.

SQL> 

#######################

I think they should keep -1403 in error list for "exception_init pragma"
like they have maintained for sqlerrm.
I am not saying it is a bug but I just want to know what is the reason for not including "-1403" for that,
or they have missed it.

Could you please say few lines for this.

thanks & Regards,
Biswaranjan.

Hi Tom,

That I have understood what you have explained just now ,long back from your sites from you only.

But my question here is.
we know we can associate a named exception to a particular oracle error using 
"pragma exception_init".

we also know the positive error "100" is the error code for nodatafound(I mean -1403 or 100) .

SQL> declare
  2  hello exception;
  3  pragma exception_init(hello ,100);
  4  begin
  5  null;
  6  end;
  7  /

PL/SQL procedure successfully completed.

----it is ok as we are associating "hello" named exception to nodatafound(100).

SQL> declare
  2  hello exception;
  3  pragma exception_init(hello ,-1403);  --------they should include -1403 
  4  begin
  5  null;
  6  end;
  7  /
hello exception;
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00701: illegal ORACLE error number -1403 for PRAGMA EXCEPTION_INIT

---But here it seems odd ,it(exception_init pragma) should accept "hello" 
named exception to nodatafound(-1403).

thanks & Regards,
Biswaranjan.

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/exceptioninit_pragma.htm#i33787

Hi Tom,
thanks for the reply.

tomorrow you have something schedule "National Capitol Area Users Group".

Is that like you are sharing some new and special knowledge at some conference!!

Hi Tom,

In my pl/sql code, 'no data found' error is thrown from line 5 but no error is thrown for lines 1 to 4

1)m_acct_tab_first := g_g_tab(m_bill_no).service_tab(m_service).date_tab(m_period).account_tab.first;

2)m_acct_tab_last := g_g_tab(m_bill_no).service_tab(m_service).date_tab(m_period).account_tab.last;

3)m_account_no := g_g_tab(m_bill_no).service_tab(m_service).date_tab(m_period).account_tab(m_acct_tab_first).acc_no;

4)m_acct_no_tab_cnt := g_g_tab(m_bill_no).service_tab(m_service).date_tab(m_period).account_tab.count;

5)FOR m_account IN 1..g_g_tab(m_bill_no).service_tab(m_service).date_tab(m_period).account_tab.COUNT LOOP

The oracle version is 10g

Any ideas as to how we can explain this

Thank you.

Hi,

Just to add that m_acct_tab_first,m_acct_tab_last,m_acct_no_tab_cnt have all the same value,1.

hello Tom,
in your first reply on 10 jun 2003 you wrote,

> tkyte@ORA716> select func_foo from dual;
>
> <b>no rows selected</b>

>
> no data found in a function called from sql just says "ok, > no more data, please stop"

this was very reasonable..but, as noted also from Balasubramanian, on 9.2 and 10.2.0.5.0 (and conceivably other versions)

> select func_foo from dual will return no rows selected.
> But if i executed it shows 1 row selected.

SQL> create or replace function func_foo return varchar2 is
  2    l_value varchar2(1);
  3  begin
  4    select dummy
  5    into l_value
  6    from dual
  7    where dummy = '-';
  8
  9    return l_value;
 10  end;
 11  /

Function created.

SQL> select func_foo from dual;


<b>1 row selected.</b>

SQL> select * from v$version;

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Productio
NLSRTL Version 10.2.0.5.0 - Production

5 rows selected.

moreover, the function returns 1 row even if there's no "return":

SQL> create or replace function func_foo return varchar2 is
  2    l_value varchar2(1);
  3  begin
  4    select dummy
  5    into l_value
  6    from dual
  7    where dummy = '-';
  8
  9    -- return l_value;
 10  end;
 11  /

Function created.

SQL> select func_foo from dual;


1 row selected.

10:32:26 SQL> select nvl(func_foo,'X') from dual;
X

1 row selected.

that last beahaviour seems to me quite buggy: I do expect an ORA-06503.. "who" is returning the null value?

hello Tom,
in your first reply on 10 jun 2003 you wrote,

> tkyte@ORA716> select func_foo from dual;
>
> <b>no rows selected</b>

>
> no data found in a function called from sql just says "ok, > no more data, please stop"

this was very reasonable..but, as noted also from Balasubramanian, on 9.2 and 10.2.0.5.0 (and conceivably other versions)

> select func_foo from dual will return no rows selected.
> But if i executed it shows 1 row selected.

SQL> create or replace function func_foo return varchar2 is
  2    l_value varchar2(1);
  3  begin
  4    select dummy
  5    into l_value
  6    from dual
  7    where dummy = '-';
  8
  9    return l_value;
 10  end;
 11  /

Function created.

SQL> select func_foo from dual;


<b>1 row selected.</b>

SQL> select * from v$version;

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Productio
NLSRTL Version 10.2.0.5.0 - Production

5 rows selected.

moreover, the function returns 1 row even if there's no "return":

SQL> create or replace function func_foo return varchar2 is
  2    l_value varchar2(1);
  3  begin
  4    select dummy
  5    into l_value
  6    from dual
  7    where dummy = '-';
  8
  9    -- return l_value;
 10  end;
 11  /

Function created.

SQL> select func_foo from dual;


1 row selected.

10:32:26 SQL> select nvl(func_foo,'X') from dual;
X

1 row selected.

that last beahaviour seems to me quite buggy: I do expect an ORA-06503.. "who" is returning the null value?

Hello Thomas,

What do you think about scheduled jobs which can hide no_data_found exception?

create or replace procedure test1 is
begin
  raise no_data_found;
end;
/
begin test1; end;
/
begin
      dbms_scheduler.create_job(job_name        => 'T1',
                                job_type        => 'STORED_PROCEDURE',
                                job_action      => 'TEST1',
                                enabled         => TRUE,
                                start_date      => systimestamp,
                                auto_drop       => true);
END;
/
select ACTUAL_START_DATE, LOG_DATE,STATUS,ERRORS from user_scheduler_job_run_details where job_name='T1';

The final query shows STATUS='SUCCEEDED' even though call of test1() raises a no_data_found exception.

Btw, if job is defined slightly differently

  job_type        => 'PLSQL_BLOCK',
  job_action      => 'BEGIN raise no_data_found; end;',

all works as expected, i.e. STATUS='FAILED'.