Oracle CURSOR with parameter

      1 Comment on Oracle CURSOR with parameter
0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Buffer 0 Email -- 0 Flares ×

Execution of a cursor puts the results of the query into a set of rows called the result set, which can be fetched sequentially or non sequentially.
You can create Cursors which accepts parameters at runtime.

Read:Cursor | Oracle PL/SQL Cursors and example.
Read:ORA-01000: maximum open cursors exceeded.
Read:Oracle Cursors | OPEN ,FETCH and CLOSE Cursor statements.
Read:ORA-01001: invalid cursor


Step 1:

create table PERSON( PERSON_ID NUMBER(19) not null, AGE       NUMBER(10), FIRSTNAME VARCHAR2(255), LASTNAME  VARCHAR2(255));

SQL> insert into Person values(1,10,'Geek','Greek');

1 row inserted

SQL> insert into Person values(2,12,'Seek','Bells');

1 row inserted

SQL> insert into Person values(3,13,'Creek','Dells');

1 row inserted

SQL>SQL> insert into Person values(4,13,'Sreek','Sells');

1 row inserted


Step 2:

Now write a small procedure to illustrate the cursor which accepts a parameter at run time.

create or replace procedure Param_Cursor as

CURSOR PERSON_CUR(pAge NUMBER) isSelect * from Person p where p.age = pAge;

BEGIN    for i in PERSON_CUR(13) loop       dbms_output.put_line(i.firstname);    end loop;

END Param_Cursor;


Now run the procedure Param_Cursor.

SQL> set serveroutput on;SQL> exec Param_Cursor;CreekSreek

PL/SQL procedure successfully completed


Technorati Tags:
, , , ,

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Buffer 0 Email -- 0 Flares ×

1 thought on “Oracle CURSOR with parameter

  1. Million einsacken

    Just want to say your article is impressive. The clarity in your post is simply striking and i can assume you are an expert on this subject. Well with your permission allow me to grab your rss feed to keep up to date with future post. Thanks a million and please keep up the good work.


Leave a Reply

Your email address will not be published. Required fields are marked *

Paged comment generated by AJAX Comment Page