Info:

No data needed occurs too, when you query a pipelined function that should return n elements. But you have added the condition nb_row< m with m<n.

exemple:

select *
    from table ( my_pieplined_function())  -- this function should returns 20 elements
   where rownum < 10;

It should occurs with fetch next n rows only. But I've tested that yet.

I have learned that I don't need to handle the exception "no_data_needed" that occurs in a pipelined function. When I'm doing the query, logically oracle catch this exception , take the lines that have been piped and do nothing with the rest.

let oracle catch the exception is equivalent to that.

create my_pieplined_function return sys.odcinumberlist is
    i integer:=0;
begin
    for loop
        pipe row (i); 
        i:=i 1;
        if i=20 then return i; end;
    end loop;
exception
      when no_data_needed then   null;
end;

more info there https://asktom.oracle.com/Misc/nodataneeded-something-i-learned.html

question

I would like to know if there are other exception that are caught by oracle without having to do something. If yes, where can I find the list of these exceptions.

other question :

why don't I find no_data_needed in [summary of predefined exception] (https://docs.oracle.com/cd/B14117_01/appdev.101/b10807/07_errs.htm)

CodePudding user response：

why don't I find no_data_needed in [summary of predefined exception] (https://docs.oracle.com/cd/B14117_01/appdev.101/b10807/07_errs.htm)

Because it was not documented in that version.

It is in the Oracle 11g Database PL/SQL Language Reference.

I have learned that I don't need to handle the exception no_data_needed that occurs in a pipelined function.

You only do not need to handle that exception if you have nothing that needs doing when cleaning up after the pipelined function.

If you do:

CREATE FUNCTION my_pipelined_function
RETURN sys.odcinumberlist PIPELINED
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE( 'Initialise' );
  FOR i IN 1 .. 2 LOOP
    DBMS_OUTPUT.PUT_LINE( 'Loop ' || i );
    pipe row (i);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE( 'Cleanup' );
  RETURN;
END;
/

If you call the function then using:

SELECT * FROM TABLE(my_pipelined_function());

Then the output is:

COLUMN_VALUE
1
2

Initialise
Loop 1
Loop 2
Cleanup

But if you limit the number of rows:

SELECT * FROM TABLE(my_pipelined_function()) WHERE rownum = 1;

Then the output will be:

COLUMN_VALUE
1

Initialise
Loop 1

and the Cleanup section is never reached as you stop calling the function in the middle of the loop.

If you define the function as:

CREATE FUNCTION my_pipelined_function
RETURN sys.odcinumberlist PIPELINED
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE( 'Initialise' );
  FOR i IN 1 .. 2 LOOP
    DBMS_OUTPUT.PUT_LINE( 'Loop ' || i );
    pipe row (i);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE( 'Cleanup 1' );
  RETURN;
EXCEPTION
  WHEN NO_DATA_NEEDED THEN
    DBMS_OUTPUT.PUT_LINE( 'Cleanup 2' );
    RETURN;
END;
/

and call it using:

SELECT * FROM TABLE(my_pipelined_function()) WHERE rownum = 1;

Then the output will be:

COLUMN_VALUE
1

Initialise
Loop 1
Cleanup 2

So you don't need to handle that exception if your pipelined function has nothing to do when the function finishes; however, if you initialise something and need to clean it up at the end of the function then you should handle the NO_DATA_NEEDED exception as it will allow you to perform the clean up when the loop is terminated early.

db<>fiddle here

I would like to know if there are other exception that are caught by oracle without having to do something.

In the SQL scope (but not the PL/SQL scope), Oracle will silently catch the NO_DATA_FOUND exception and replace it with a NULL value:

For example:

SELECT (SELECT 1 FROM DUAL WHERE 1 = 0) AS value
FROM   DUAL;

The outer query expects a value:

SELECT <something> AS value
FROM   DUAL;

The inner query:

SELECT 1 FROM DUAL WHERE 1 = 0

Generates zero rows and so no value; this raises a NO_DATA_FOUND exception which is silently caught and replaced by a NULL value.

This can be seen more clearly with:

WITH FUNCTION ndf RETURN NUMBER
  IS
  BEGIN
    RAISE NO_DATA_FOUND;
  END;
SELECT ndf() AS value FROM DUAL;

Which outputs:

VALUE
null

The behaviour is different in PL/SQL:

DECLARE
  v_value NUMBER;
BEGIN
  SELECT 1 INTO v_value FROM DUAL WHERE 1 = 0;
END;
/

Will not catch the NO_DATA_FOUND exception and it will cause the execution of the block to terminate early.