Main Tutorials

Oracle Stored Procedure Cursor example

Here’s a stored procedure example in Oracle database, using Cursor to manipulate or navigate the records.

1. Table SQL Script

DBUSER table creation script.


CREATE TABLE DBUSER ( 
  USER_ID       NUMBER (5)    NOT NULL, 
  USERNAME      VARCHAR2 (20)  NOT NULL, 
  CREATED_BY    VARCHAR2 (20)  NOT NULL, 
  CREATED_DATE  DATE          NOT NULL, 
  PRIMARY KEY ( USER_ID ) 
 )

2. Stored Procedure

A stored procedure, return the record as cursor type (SYS_REFCURSOR) base on the matched username.


CREATE OR REPLACE PROCEDURE getDBUSERCursor(
	   p_username IN DBUSER.USERNAME%TYPE,
	   c_dbuser OUT SYS_REFCURSOR)
IS
BEGIN

  OPEN c_dbuser FOR
  SELECT * FROM DBUSER WHERE USERNAME LIKE p_username || '%';
 
END;
/

3. Calls from PL/SQL

Get the returned ref cursor and navigate the records like this :


DECLARE 
  c_dbuser SYS_REFCURSOR;
  temp_dbuser DBUSER%ROWTYPE;
BEGIN
 
  --records are assign to cursor 'c_dbuser'
  getDBUSERCursor('mkyong',c_dbuser);
 
  LOOP
 
        --fetch cursor 'c_dbuser' into dbuser table type 'temp_dbuser'
	FETCH c_dbuser INTO temp_dbuser;
 
        --exit if no more records
        EXIT WHEN c_dbuser%NOTFOUND;

        --print the matched username
        dbms_output.put_line(temp_dbuser.username);
 
  END LOOP;
 
  CLOSE c_dbuser;
 
END;
/

Result
The records of username like ‘mkyong%’ are returned as cursor via getDBUSERCursor store procedure.

About Author

author image
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

Subscribe
Notify of
9 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Chandu
6 years ago

Hi,
I have to create the procedure with 2 input paramaters
one is table name
second one is sys_ref_curosr (table data).
here
i have to insert the ref cursor data into input table name(input paramater) but for doing the insertion operation, we have to create the collection type to process the refcursor data
type type_name is table of table_name%rowtype —-> Here table name comes from input paramater (dynamically).
so how we can create the above type with dynamic table
or else
give me a alternate solution

Thanks
Chandu

likitha
1 year ago
Reply to  Chandu

please send that

hasto
6 years ago

hai mkyong, i have a question about opening and closing a cursor. what different if i running PL/SQL with close cursor in the end of query and i running PL/SQL without close cursor in the end of query?

vikash kumar
9 years ago

this example is not working. It’s showing anonymous block completed even my table has the data.

Pizza inn coupons
10 years ago

At this time I am ready to do my breakfast, afterward having my
breakfast coming yet again to read other news.

Steven Andler
11 years ago

Hello Mkyong

Thank you for this example. I am faily new to Oracle. I am a c# programmer ( sorry I know your a Java guy). I was wondering if you also have sample code showing how to access and return the Cursor info from the getDBUSERCursor written in C#.

Thank you.

Pavan
11 years ago

Thanks Dude..Good Example…

Aasim
11 years ago

Could you please let me know what is the diffrence between in, out parameter.

praveen john
4 years ago
Reply to  Aasim

in is what you give as input and out is what you get as ouput.
For ex: 1+2=3 1 and 2 are input whereas 3 is an output.