Cursor for loop in procedure with called function in Oracle

Question

The table is:

F TIME1                                                                       END_TIME                          
- --------------------------------------------------------------------------- ----------------------
C 16-NOV-16 09.45.32.000000 AM                                                17-NOV-16 09.45.32.000000 AM      
A 16-NOV-16 10.14.54.000000 AM                                                16-NOV-16 11.14.54.000000 AM      
A 16-NOV-16 10.14.56.000000 AM                                                16-NOV-16 11.14.56.000000 AM

I have created a function..

CREATE OR REPLACE FUNCTION datediff
(
time1 TIMESTAMP
, time2 TIMESTAMP
)
RETURN number
as
tot number;
BEGIN
SELECT(extract(DAY FROM time2-time1)*24*60*60)+
(extract(HOUR FROM time2-time1)*60*60)
 into tot from tt ;

RETURN tot;
END;

I am then calling the function in procedure...

CREATE OR REPLACE PROCEDURE P1
IS 
  CURSOR c1
   IS    
   select count(*) as cnt,time1,end_time
   from tt group by time1,end_time ;

  a number;
BEGIN
  FOR i IN c1
  LOOP
    declare
      a number;
    BEGIN
      insert into y1 values(i.cnt,datediff(i.time1,i.end_time)) ;

      --display(i.cnt||'        '||a);

    /* EXCEPTION
         WHEN OTHERS THEN
            dbms_output.put_line('Error updating record ' || SUBSTR (SQLERRM, 1, 250));*/
    END;

  END LOOP;

END P1;

The error I am getting is ...

SQL> exec p1
BEGIN p1; END;
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "ANU.DATEDIFF", line 11
ORA-06512: at "ANU.P1", line 14
ORA-06512: at line 1

This is working for single record in the table ,but not for multiple records..? please guide..

score 0 · Answer 1 · edited May 23 '17 at 12:13

0

The problem is in your datediff function.

Whenever you have a SELECT ... INTO ... statement in PL/SQL, the SELECT query must return a single row. If it returns no rows at all, you get an ORA-01403 no data found error, and if it returns more than one row, you get the ORA-01422 exact fetch returns more than requested number of rows error you see above.

So, how can you get your query to return one row? Well, you can start by noticing that you aren't selecting any values from of the table tt. All your query is returning is the same value for each row in tt. If tt has one row, your query returns the value once. If tt has three rows, your query returns the same value three times. All this is unnecessary as you only want the one value, regardless of how many rows there are in tt.

So instead of SELECTing from tt, use the built-in table dual instead. The dual table only ever has one row in it:

SELECT(extract(DAY FROM time2-time1)*24*60*60)+
(extract(HOUR FROM time2-time1)*60*60)
 into tot from dual;

However, in your case you don't even need a query: your datediff function could be rewritten to just perform the calculation and return the value:

CREATE OR REPLACE FUNCTION datediff
(
time1 TIMESTAMP
, time2 TIMESTAMP
)
RETURN number
as
BEGIN
 RETURN (extract(DAY FROM time2-time1)*24*60*60)+
        (extract(HOUR FROM time2-time1)*60*60);
END;
/

edited May 23 '17 at 12:13

Community

1
1

answered Nov 16 '16 at 21:34

Luke Woodward

63,336
16
89
104

Wonderful.. thank you so much for the detailed explanation.can i get your email id pls – swathi Nov 18 '16 at 15:22
@swathi: no, sorry, you can't have my email address. I don't give that out on Stack Overflow. – Luke Woodward Nov 18 '16 at 19:29
no prob Luke, i tried to implement as u suggested... as follows, – swathi Nov 19 '16 at 08:44
the function... CREATE OR REPLACE FUNCTION datediff ( time1 TIMESTAMP , time2 TIMESTAMP, t2 out number ) -- RETURN NUMBER RETURN number as t1 number; BEGIN SELECT (extract(DAY FROM time2-time1)*24), (extract(HOUR FROM time2-time1)) into t1,t2 from dual; RETURN t1; END; – swathi Nov 19 '16 at 08:52
------------- the procedure is.....-------------- CREATE OR REPLACE PROCEDURE P1 IS CURSOR c1 IS select count(*) as cnt,time1,end_time from tt group by time1,end_time ; a number; mi number; BEGIN execute immediate 'truncate table y1'; FOR i IN c1 LOOP declare a number; b varchar2(23); BEGIN a:= datediff(i.time1,i.end_time,mi); b:=a||':'||mi; insert into y1 values(i.cnt,b); – swathi Nov 19 '16 at 08:53
/* EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Error updating record ' || SUBSTR (SQLERRM, 1, 250));*/ END; END LOOP; END P1; ------------- exec p1; ---------- select * from y1; CNT TIME ------ ---------------------- 1 24:0 1 0:1 1 0:1 – swathi Nov 19 '16 at 08:53
-------------------it working ... but me requirement is ... select * from y1; CNT TIME ------ ---------------------- 1 24:0 2 0:1 there cnt should be grouped , when time is same.... am trying but facing multiple errors in procedure... can u guide me ...plz? – swathi Nov 19 '16 at 08:53
i modifies as.... CREATE OR REPLACE FUNCTION datediff ( diff timestamp, --time2 TIMESTAMP, --time1 TIMESTAMP, t2 out number ) -- RETURN NUMBER RETURN number as t1 number; BEGIN SELECT (extract(DAY FROM diff)*24), (extract(HOUR FROM diff)) into t1,t2 from dual; RETURN t1; END; – swathi Nov 19 '16 at 09:03
CREATE OR REPLACE PROCEDURE P1 IS CURSOR c1 IS select count(*) as cnt, (end_time-time1) as diff from tt group by (end_time-time1); a number; mi number; BEGIN execute immediate 'truncate table y1'; FOR i IN c1 – swathi Nov 19 '16 at 09:05
LOOP declare a number; b varchar2(23); BEGIN a:= datediff(i.diff,mi); b:=a||':'||mi; insert into y1 values(i.cnt,b); /* EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Error updating record ' || SUBSTR (SQLERRM, 1, 250))*/ END; END LOOP; END P1; – swathi Nov 19 '16 at 09:05
the error is............ Warning: Procedure created with compilation errors. SQL> show err Errors for PROCEDURE P1: LINE/COL ERROR -------- ---------------------------------------------------------------- 17/4 PL/SQL: Statement ignored 17/8 PLS-00306: wrong number or types of arguments in call to 'DATEDIFF' – swathi Nov 19 '16 at 09:05
also the following error.....SQL> exec p1; BEGIN p1; END; * ERROR at line 1: ORA-01847: day of month must be between 1 and last day of month ORA-06512: at "ANU.DATEDIFF", line 13 ORA-06512: at "ANU.P1", line 17 ORA-06512: at line 1 – swathi Nov 19 '16 at 09:21
@swathi: *never*, **ever**, ***EVER*** post that much code in comments. If you've updated your code and changed things and it isn't working, ask a new question instead. – Luke Woodward Nov 19 '16 at 14:16

Cursor for loop in procedure with called function in Oracle

1 Answers1