Oracle / PLSQL: Cursor with variable in an IN CLAUSE

Question: I'm trying to use a variable in an IN CLAUSE of a cursor. Here are my assumptions and declarations.

1. Ref_cursor is of type REF CURSOR declared in Package
2. I will to pass a comma separated Numbers as a string
3. This should be used in the query in the IN clause
4. Execute the Query and Return the Output as REF Cursor

Something similar to the following:

CREATE OR REPLACE FUNCTION func_name (inNumbers in Varchar2)
   RETURN PackageName.ref_cursor
IS
   out_cursor PackageName.Ref_cursor;

BEGIN
   OPEN out_cursor
   FOR SELECT * FROM Table_name
   WHERE column_name IN (inNumbers);

   RETURN out_cursor;
END;

I seem to be getting an error when I try the code above. How can I use a variable in an IN CLAUSE?

Answer: Unfortunately, there is no easy way to use a variable in an IN CLAUSE if the variable contains a list of items. We can, however, suggest two alternative options:

Option #1

Instead of creating a string variable that contains a list of numbers, you could try storing each value in a separate variable. For example:

CREATE OR REPLACE FUNCTION func_name
   RETURN PackageName.ref_cursor
IS
   out_cursor PackageName.Ref_cursor;
   v1 varchar(2);
   v2 varchar(2);
   v3 varchar(2);

BEGIN

   v1 := '1';
   v2 := '2';
   v3 := '3';

   OPEN out_cursor
   FOR SELECT * FROM Table_name
   WHERE column_name IN (v1, v2, v3);

   RETURN out_cursor;

END;

Option #2

You could try storing your values in a table. Then use a sub-select to retrieve the values.

For example:

CREATE OR REPLACE FUNCTION func_name
   RETURN PackageName.ref_cursor
IS
   out_cursor PackageName.Ref_cursor;

BEGIN

   OPEN out_cursor
   FOR SELECT * FROM Table_name
   WHERE column_name IN (SELECT values FROM list_table);

   RETURN out_cursor;

END;

In this example, we've stored our list in a table called list_table.

 

Oracle / PLSQL: Cursor within a cursor

Question: In PSQL, I want to declare a cursor within cursor. The second cursor should use a value from the first cursor in the "where clause". How can I do this?

Answer: Below is an example of how to declare a cursor within a cursor.

In this example, we have a cursor called get_tables that retrieves the owner and table_name values. These values are then used in a second cursor called get_columns.

CREATE OR REPLACE PROCEDURE MULTIPLE_CURSORS_PROC is
   v_owner varchar2(40);
   v_table_name varchar2(40);
   v_column_name varchar2(100);

   /* First cursor */
   CURSOR get_tables IS
     SELECT DISTINCT tbl.owner, tbl.table_name
     FROM all_tables tbl
     WHERE tbl.owner = 'SYSTEM';

   /* Second cursor */
   CURSOR get_columns IS
     SELECT DISTINCT col.column_name
     FROM all_tab_columns col
     WHERE col.owner = v_owner
     AND col.table_name = v_table_name;

   BEGIN

   -- Open first cursor
   OPEN get_tables;
   LOOP
      FETCH get_tables INTO v_owner, v_table_name;

      -- Open second cursor
      OPEN get_columns;
      LOOP
         FETCH get_columns INTO v_column_name;
      END LOOP;

      CLOSE get_columns;

   END LOOP;

   CLOSE get_tables;

EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
end MULTIPLE_CURSORS_PROC;

The trick to declaring a cursor within a cursor is that you need to continue to open and close the second cursor each time a new record is retrieved from the first cursor. That way, the second cursor will use the new variable values from the first cursor.

 

Oracle / PLSQL: Cursor with variable in an IN CLAUSE

Question: I'm trying to use a variable in an IN CLAUSE of a cursor. Here are my assumptions and declarations.

1. Ref_cursor is of type REF CURSOR declared in Package
2. I will to pass a comma separated Numbers as a string
3. This should be used in the query in the IN clause
4. Execute the Query and Return the Output as REF Cursor

Something similar to the following:

CREATE OR REPLACE FUNCTION func_name (inNumbers in Varchar2)
   RETURN PackageName.ref_cursor
IS
   out_cursor PackageName.Ref_cursor;

BEGIN
   OPEN out_cursor
   FOR SELECT * FROM Table_name
   WHERE column_name IN (inNumbers);

   RETURN out_cursor;
END;

I seem to be getting an error when I try the code above. How can I use a variable in an IN CLAUSE?

Answer: Unfortunately, there is no easy way to use a variable in an IN CLAUSE if the variable contains a list of items. We can, however, suggest two alternative options:

Option #1

Instead of creating a string variable that contains a list of numbers, you could try storing each value in a separate variable. For example:

CREATE OR REPLACE FUNCTION func_name
   RETURN PackageName.ref_cursor
IS
   out_cursor PackageName.Ref_cursor;
   v1 varchar(2);
   v2 varchar(2);
   v3 varchar(2);

BEGIN

   v1 := '1';
   v2 := '2';
   v3 := '3';

   OPEN out_cursor
   FOR SELECT * FROM Table_name
   WHERE column_name IN (v1, v2, v3);

   RETURN out_cursor;

END;

Option #2

You could try storing your values in a table. Then use a sub-select to retrieve the values.

For example:

CREATE OR REPLACE FUNCTION func_name
   RETURN PackageName.ref_cursor
IS
   out_cursor PackageName.Ref_cursor;

BEGIN

   OPEN out_cursor
   FOR SELECT * FROM Table_name
   WHERE column_name IN (SELECT values FROM list_table);

   RETURN out_cursor;

END;

In this example, we've stored our list in a table called list_table.

 

Oracle / PLSQL: Cursor within a cursor

Question: In PSQL, I want to declare a cursor within cursor. The second cursor should use a value from the first cursor in the "where clause". How can I do this?

Answer: Below is an example of how to declare a cursor within a cursor.

In this example, we have a cursor called get_tables that retrieves the owner and table_name values. These values are then used in a second cursor called get_columns.

CREATE OR REPLACE PROCEDURE MULTIPLE_CURSORS_PROC is
   v_owner varchar2(40);
   v_table_name varchar2(40);
   v_column_name varchar2(100);

   /* First cursor */
   CURSOR get_tables IS
     SELECT DISTINCT tbl.owner, tbl.table_name
     FROM all_tables tbl
     WHERE tbl.owner = 'SYSTEM';

   /* Second cursor */
   CURSOR get_columns IS
     SELECT DISTINCT col.column_name
     FROM all_tab_columns col
     WHERE col.owner = v_owner
     AND col.table_name = v_table_name;

   BEGIN

   -- Open first cursor
   OPEN get_tables;
   LOOP
      FETCH get_tables INTO v_owner, v_table_name;

      -- Open second cursor
      OPEN get_columns;
      LOOP
         FETCH get_columns INTO v_column_name;
      END LOOP;

      CLOSE get_columns;

   END LOOP;

   CLOSE get_tables;

EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
end MULTIPLE_CURSORS_PROC;

The trick to declaring a cursor within a cursor is that you need to continue to open and close the second cursor each time a new record is retrieved from the first cursor. That way, the second cursor will use the new variable values from the first cursor.

 

Oracle / PLSQL: Procedure that outputs a dynamic PLSQL cursor

Question: In Oracle, I have a table called "wine" and a stored procedure that outputs a cursor based on the "wine" table.

I've created an HTML Form where the user can enter any combination of three values to retrieve results from the "wine" table. My problem is that I need a general SELECT statement that will work no matter what value(s), the user enters.

Example

parameter_1= "Chianti"
parameter_2= "10"
parameter_3= wasn't entered by the user but I have to use in the select statement. And this is my problem. How to initialize this parameter to get all rows for column3?

SELECT *
FROM wine
WHERE column1 = parameter_1
AND column2 = parameter_2
AND column3 = parameter_3;

The output of my stored procedure must be a cursor.

Answer: To solve your problem, you will need to output a dynamic PLSQL cursor in Oracle.

Let's look at how we can do this. We've divided this process into 3 steps.

Step 1 - Table Definition

First, we need a table created in Oracle called "wine". Below is the create statement for the wine table.

CREATE TABLE wine
( col1 varchar2(40),
  col2 varchar2(40),
  col3 varchar2(40)
);

We've made this table definition very simple, for demonstration purposes.

Step 2 - Create package

Next, we've created a package called "winepkg" that contains our cursor definition. This needs to be done so that we can use a cursor as an output parameter in our stored procedure.

CREATE OR REPLACE PACKAGE winepkg
IS
   /* Define the REF CURSOR type. */
   TYPE wine_type IS REF CURSOR RETURN wine%ROWTYPE;
END winepkg;

This cursor will accept all fields from the "wine" table.

Step 3 - Create stored procedure

Our final step is to create a stored procedure to return the cursor. It accepts three parameters (entered by the user on the HTML Form) and returns a cursor (c1) of type "wine_type" which was declared in Step 2.

The procedure will determine the appropriate cursor to return, based on the value(s) that have been entered by the user (input parameters).

CREATE OR REPLACE PROCEDURE find_wine2
 (col1_in in varchar2,
  col2_in in varchar2,
  col3_in in varchar2,
  c1 out winepkg.wine_type)
IS

BEGIN

   /* all columns were entered */
   IF (length(col1_in) > 0) and (length(col2_in) > 0) and (length(col3_in) > 0)
   THEN
      OPEN c1 FOR
        SELECT *
        FROM wine
        WHERE wine.col1 = col1_in
        AND wine.col2 = col2_in
        AND wine.col3 = col3_in;

   /* col1 and col2 were entered */
   ELSIF (length(col1_in) > 0) and (length(col2_in) > 0) and (length(col3_in) = 0)
   THEN
      OPEN c1 FOR
        SELECT *
        FROM wine
        WHERE wine.col1 = col1_in
        AND wine.col2 = col2_in;

   /* col1 and col3 were entered */
   ELSIF (length(col1_in) > 0) and (length(col2_in) = 0) and (length(col3_in) > 0)
   THEN
      OPEN c1 FOR
        SELECT *
        FROM wine
        WHERE wine.col1 = col1_in
        wine.col3 = col3_in;

   /* col2 and col3 where entered */
   ELSIF (length(col1_in) = 0) and (length(col2_in) > 0) and (length(col3_in) > 0)
   THEN
      OPEN c1 FOR
        SELECT *
        FROM wine
        WHERE wine.col2 = col2_in
        AND wine.col3 = col3_in;

   /* col1 was entered */
   ELSIF (length(col1_in) > 0) and (length(col2_in) = 0) and (length(col3_in) = 0)
   THEN
      OPEN c1 FOR
        SELECT *
        FROM wine
        WHERE wine.col1 = col1_in;

   /* col2 was entered */
   ELSIF (length(col1_in) = 0) and (length(col2_in) > 0) and (length(col3_in) = 0)
   THEN
      OPEN c1 FOR
        SELECT *
        FROM wine
        WHERE wine.col2 = col2_in;

   /* col3 was entered */
   ELSIF (length(col1_in) = 0) and (length(col2_in) = 0) and (length(col3_in) > 0)
   THEN
      OPEN c1 FOR
        SELECT *
        FROM wine
        WHERE wine.col3 = col3_in;

   END IF;

END find_wine2;

 

Oracle / PLSQL: WHERE CURRENT OF Statement

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

Description

If you plan on updating or deleting records that have been referenced by a SELECT FOR UPDATE statement, you can use the WHERE CURRENT OF statement.

Syntax

The syntax for the WHERE CURRENT OF statement in Oracle/PLSQL is either:

UPDATE table_name
  SET set_clause
  WHERE CURRENT OF cursor_name;

OR

DELETE FROM table_name
WHERE CURRENT OF cursor_name;

Note

• The WHERE CURRENT OF statement allows you to update or delete the record that was last fetched by the cursor.

Example

Updating using the WHERE CURRENT OF Statement

Here is an example where we are updating records using the WHERE CURRENT OF 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
     FOR UPDATE of instructor;

BEGIN

   OPEN c1;
   FETCH c1 INTO cnumber;

   if c1%notfound then
      cnumber := 9999;

   else
      UPDATE courses_tbl
        SET instructor = 'SMITH'
        WHERE CURRENT OF c1;

      COMMIT;

   end if;

   CLOSE c1;

RETURN cnumber;

END;

Deleting using the WHERE CURRENT OF Statement

Here is an example where we are deleting records using the WHERE CURRENT OF 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
     FOR UPDATE of instructor;

BEGIN

   open c1;
   fetch c1 into cnumber;

   if c1%notfound then
      cnumber := 9999;

   else
      DELETE FROM courses_tbl
        WHERE CURRENT OF c1;

      COMMIT;

   end if;

   close c1;

RETURN cnumber;

END;

 

Oracle / PLSQL: SELECT FOR UPDATE Statement

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

Description

The SELECT FOR UPDATE statement allows you to lock the records in the cursor result set. You are not required to make changes to the records in order to use this statement. The record locks are released when the next commit or rollback statement is issued.

Syntax

The syntax for the SELECT FOR UPDATE statement in Oracle/PLSQL is:

CURSOR cursor_name
IS
   select_statement
   FOR UPDATE [OF column_list] [NOWAIT];

Parameters or Arguments

cursor_name

The name of the cursor.

select_statement

A SELECT statement that will populate your cursor result set.

column_list

The columns in the cursor result set that you wish to update.

NOWAIT

Optional. The cursor does not wait for resources.

Example

For example, you could use the SELECT FOR UPDATE statement as follows:

CURSOR c1
IS
  SELECT course_number, instructor
  FROM courses_tbl
  FOR UPDATE OF instructor;

If you plan on updating or deleting records that have been referenced by a SELECT FOR UPDATE statement, you can use the WHERE CURRENT OF statement.

 

Oracle / PLSQL: Cursor Attributes

While dealing with cursors, you may need to determine the status of your cursor. The following is a list of the cursor attributes that you can use.

Attribute	Explanation
%ISOPEN	- Returns TRUE if the cursor is open, FALSE if the cursor is closed.
%FOUND	- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed. - Returns NULL if cursor is open, but fetch has not been executed. - Returns TRUE if a successful fetch has been executed. - Returns FALSE if no row was returned.
%NOTFOUND	- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed. - Return NULL if cursor is open, but fetch has not been executed. - Returns FALSE if a successful fetch has been executed. - Returns TRUE if no row was returned.
%ROWCOUNT	- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed. - Returns the number of rows fetched. - The ROWCOUNT attribute doesn't give the real row count until you have iterated through the entire cursor. In other words, you shouldn't rely on this attribute to tell you how many rows are in a cursor after it is opened.

Below is an example of how you might use the %NOTFOUND attribute.

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;

 

Oracle / PLSQL: FETCH Statement

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

Description

The purpose of using a cursor, in most cases, is to retrieve the rows from your cursor so that some type of operation can be performed on the data. After declaring and opening your cursor, the next step is to use the FETCH statement to fetch rows from your cursor.

Syntax

The syntax for the FETCH statement in Oracle/PLSQL is:

FETCH cursor_name INTO variable_list;

Parameters or Arguments

cursor_name

The name of the cursor that you wish to fetch rows.

variable_list

The list of variables, comma delimited, that you wish to store the cursor result set in.

Example

For example, you could have a cursor defined as:

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

The command that would be used to fetch the data from this cursor is:

FETCH c1 into cnumber;

This would fetch the first course_number into the variable called cnumber.

Below is a function that demonstrates how to use the FETCH 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;

 

Oracle / PLSQL: CLOSE Statement

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

Description

The final step of working with cursors is to close the cursor once you have finished using it.

Syntax

The syntax to close a cursor in Oracle/PLSQL using the CLOSE statement is:

CLOSE cursor_name;

Parameters or Arguments

cursor_name

The name of the cursor that you wish to close.

Example

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

CLOSE c1;

Below is a function that demonstrates how to use the CLOSE 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;

 

Oracle / PLSQL: OPEN Statement

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

Description

Once you've declared your cursor, the next step is to use the OPEN statement to open the cursor.

Syntax

The syntax to open a cursor using the OPEN statement in Oracle/PLSQL is:

OPEN cursor_name;

Parameters or Arguments

cursor_name

The name 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 function 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;