Using OPEN Statement in Oracle

This Oracle tutorial explains how to use the Oracle/PLSQL OPEN statement with syntax and examples.

Description

Once you’ve got declared your cursor, the next step is to use the OPEN announcement to open the cursor.

Syntax

The syntax to open a cursor the use of the OPEN announcement in Oracle/PLSQL is:

OPEN cursor_name;

Parameters or Arguments

cursor_name The identify of the cursor that you wish to open.

Example

For example, you could open a cursor called c1 with the following command:

OPEN c1;

Below is a feature that demonstrates how to use the OPEN statement:

CREATE OR REPLACE Function FindCourse
  ( name_in IN varchar2 )
  RETURN number
IS
   cnumber number;

   CURSOR c1
   IS
     SELECT course_number
     FROM courses_tbl
     WHERE course_name = name_in;

BEGIN

   OPEN c1;
   FETCH c1 INTO cnumber;

   if c1%notfound then
      cnumber := 9999;
   end if;

   CLOSE c1;

RETURN cnumber;

END;