CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
Bilgin Kılıç
Bilgin Kılıç

Reputation: 9129

Ref Cursor Type oracle

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;

Upvotes: 2

Views: 793

Answers (2)

Kevin Burton
Kevin Burton

Reputation: 11924

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;

Upvotes: 3

David Aldridge
David Aldridge

Reputation: 52376

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;

Upvotes: 2

Related Questions