Ref Cursor Type oracle

Question

I would like to check if cursor have only one row. and return this,

--Please see my question inside the procedure?

    CREATE OR REPLACE PROCEDURE GetInterestRate( p_id    IN NUMBER DEFAULT NULL,
       RC1             IN OUT SYS_REFCURSOR
    )
    ....


    begin
      open rc1 for select * from interestRatesTable i  join parametersInterest p on 
i.interestName = p.Name
 where i.idinterest  = p_id
and p.Active ='A';
     -- I would like to check if cursor have only one row.
    --- if it has zero row, no result I got to raise an error
    --- if it has more than one row, I got to raise to many interest rates!
    -- how can I do this?
    end;

I thought java developers could be familiar with this case. I just remove to be polite ... — Bilgin Kılıç
– Bilgin Kılıç, Commented Dec 11, 2012 at 7:46

Kevin Burton · Accepted Answer · 2012-12-11 09:52:45Z

3

Oracle will raise the errors for you, simply select a value into a row I guess you should really catch the exception in an exception block too.

 CREATE OR REPLACE PROCEDURE GetInterestRate( p_id    IN NUMBER DEFAULT NULL,
   RC1             IN OUT SYS_REFCURSOR
)
....

 begin
   select 1
   into   a_value
   from   interestRatesTable i  
   join parametersInterest p ...    

   open rc1 for ......
 EXCEPTION 
   WHEN NO_DATA_FOUND THEN
     .....
   WHEN TOO_MANY_ROWS THEN
     .....
   WHEN OTHERS THEN
     .....
 end;

edited Dec 11, 2012 at 9:52

answered Dec 11, 2012 at 9:32

Kevin Burton

12k2 gold badges26 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

David Aldridge · Accepted Answer · 2012-12-11 08:17:52Z

2

I think you'll have to run the query first before opening the cursor.

 CREATE OR REPLACE PROCEDURE ...
 begin
   select count(*)
   into   row_found
   from   interestRatesTable i  join parametersInterest p ...
   and    rownum = 1;

   if row_found = 0 then raise ...
   end if
   open ...

 end;

answered Dec 11, 2012 at 8:17

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

Collectives™ on Stack Overflow

Ref Cursor Type oracle

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related