CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Madalina
Madalina

Reputation: 85

how to call oracle procedure that has in out cursor

I have this procedure and I want to know how to execute it in PL/SQL. Can someone please help me?

create or replace PROCEDURE "P_TEST" 
 (
    c$inchidere  IN OUT  softys.pk_utils.GenericCursor,
    p_OLD IN   empl.code%type,
    p_NEW IN   empl.code%type,
    p_NXT IN   empl.code%type,
    p_ORGID IN   empl.ORGID%type
  )
is
begin
    open c$inchidere  for
        select
     ...

end

Upvotes: 0

Views: 3273

Answers (1)

APC
APC

Reputation: 146339

Calling a procedure in PL/SQL is easy. The tricky part is reading the REF CURSOR. We need to fetch it into variables or a record type which match the projection of the executed query. That your code uses something called softys.pk_utils.GenericCursor makes me think you might have problems with that bit.

Anyway, this example presumes the result set returns a single numeric column. You will need to adjust the target variables to match the actual projection. 

declare
    rc sys_refcursor;
    l_id number;
begin

    P_TEST (
       c$inchidere  => rc,
      p_OLD => 'whatever',
      p_NEW => 'meh',
      p_NXT => 'ABC',
      p_ORGID => 123456  );
    loop
        fetch rc into l_id;
        exit when rc%notfound;
    end loop;
end;

Not sure why the ref cursor parameter is defined as IN OUT. As you open it immediately it only needs to be OUT.

Upvotes: 1

Related Questions