-3

I have written a stored procedure and i want to execute it.

The procedure is as follows:

create or replace procedure details( p_cursor OUT SYS_REFCURSOR)
is
begin
    OPEN p_cursor FOR
       select name,address,phone,email
       from client
       join ccondition on ccondition.clientno=client.clientno
       where condition!='Acrophobia' and condition!='Heart Condition';

END details;

I have tried different methods to execute but i got different sort of errors when I tried every one of them.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Talha Jaleel Chatha
  • 1
  • 1
  • 3
  • 1
    How you call a procedure will depend on the tool you are using (Oracle SQL Developer? SQL\*Plus? PL/SQL Developer? Java?) especially as your test procedure passes back a ref cursor which will need to be handled by the calling environment. – William Robertson May 31 '20 at 08:11
  • 1
    What different sort of errors? – Scratte May 31 '20 at 16:48

1 Answers1

2

As the procedure returns the result via OUT parameter, you have to declare something which will accept it. As I don't have your tables, I'll use Scott's EMP, just to show how it works in SQL*Plus.

SQL> create or replace procedure details (p_cursor out sys_refcursor)
  2  is
  3  begin
  4    open p_cursor for
  5      select empno, ename, job, sal
  6      from emp
  7      where deptno = 10;
  8  end;
  9  /

Procedure created.

SQL> var rc refcursor;
SQL>
SQL> begin
  2    details (:rc);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> print rc

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7782 CLARK      MANAGER         2450
      7839 KING       PRESIDENT       5000
      7934 MILLER     CLERK           1300

SQL>

It would probably help if you posted what you tried so that we could help you fix those errors (if possible), because - maybe you wanted some other option.

If you use Oracle Live SQL, something like this could do the job:

create table emp as
  select 7782 empno, 'CLARK' ename from dual union all
  select 7839      , 'KING'        from dual union all
  select 7934      , 'MILLER'      from dual;

create or replace procedure details (p_cursor out sys_refcursor)
is
begin
  open p_cursor for
    select empno, ename
    from emp;
end;
/

declare
  rc sys_refcursor;
  l_empno emp.empno%type;
  l_ename emp.ename%type;
begin
  details (rc);
  loop
    fetch rc into l_empno, l_ename;
    exit when rc%notfound;
    dbms_output.put_line(l_empno||' - '|| l_ename);
  end loop;
end;
/
Littlefoot
  • 131,892
  • 15
  • 35
  • 57