CODEWITHSUNDEEP
oracle-databaseproceduresys-refcursor
shiva tatikonda
shiva tatikonda

Reputation: 101

SYS_REFCURSOR as OUT parameter

I have a table contains (username-primarykey,password,age,gender);

have to create procedure like procedure(username in varchar,s_cursor out sys_refcursor);

procedure has to accept username and returns row (where username=in parameter )as cursor.

Rule:Cursor must and should be having unique sequence no along with the record it gives. example:(unique no(sequence),username ,password,age,gender)

Every time procedure should return single record along with uniqueno(sequence)

Upvotes: 3

Views: 46695

Answers (1)

Jacob
Jacob

Reputation: 14731

You can try something like this, if you need more information you have to provide more details.

Create a sequence for unique no.

CREATE SEQUENCE emp_seq
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

Create a procedure which returns sys_refcursor as OUT parameter and emp_id as IN parameter

    CREATE OR REPLACE PROCEDURE get_employee_details (user_id 
                                                      YOURTABLE.USERNAME%TYPE,
                                               emp_cursor   OUT SYS_REFCURSOR)
    AS
    BEGIN
       OPEN emp_cursor FOR
          SELECT emp_seq.NEXTVAL,
                 USERNAME,
                 PASSWORD,
                 AGE,
                 GENDER
            FROM YOURTABLE
           WHERE USERNAME = user_id;
   EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line ('<your message>' || SQLERRM);
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('<your message>' || SQLERRM);
    END get_employee_details;
    /

And to execute the procedure from sqlplus

variable usercur refcursor;

DECLARE

user_id  YOURTABLE.USERNAME%TYPE;

BEGIN
user_id := 'JON';
get_employees(user_id,:usercur);

END;
/

print usercur

Update 1

I assume that you are calling your procedure from sqlplus or from Toad, then you could execute your procedure as 

variable dcursor refcursor;

DECLARE


p_arrival  DEFAULT_DETAILS.ARRIVALCOUNTRY%TYPE;

BEGIN
p_arrival := '123';
PROCEDURE_SAMPLE(p_arrival,:dcursor);

END;
/

print dcursor

Update 2

To execute procedure from SQL Developer, do as

var usercur refcursor
exec procedure_sample('AU',:usercur)
print usercur

Upvotes: 8

Related Questions