How to use Ref Cursor example

How to use Ref Cursor example

A REF CURSOR is basically a data type.  A variable created based on such a data type is generally called a cursor variable.  A cursor variable can be associated with different queries at run-time.  The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.).

Declaring a SYS_REFCURSOR Cursor Variable

The following is the syntax for declaring a SYS_REFCURSOR cursor variable:

name SYS_REFCURSOR;

name is an identifier assigned to the cursor variable.

The following is an example of a SYS_REFCURSOR variable declaration.

DECLARE
    rc_emp SYS_REFCURSOR;

Opening a Cursor Variable

Once a cursor variable is declared, it must be opened with an associated SELECT command. The OPEN FOR statement specifies the SELECT command to be used to create the result set.

Syntax:
OPEN name FOR query;

name is the identifier of a previously declared cursor variable.  Query is a SELECT command that determines the result set when the statement is executed. The value of the cursor variable after the OPEN FOR statement is executed identifies the result set.

Declaring a User Defined REF CURSOR Type Variable

You must perform two distinct declaration steps in order to use a user defined REF CURSOR variable:

• Create a referenced cursor TYPE


• Declare the actual cursor variable based on that TYPE

The syntax for creating a user defined REF CURSOR type is as follows:

Syntax:
TYPE cursor_type_name IS REF CURSOR [RETURN return_type];

The following is an example of a cursor variable declaration.

DECLARE
    TYPE emp_cur_type IS REF CURSOR RETURN emp%ROWTYPE;
    rc_emp emp_cur_type;

Closing a Cursor Variable

Unlike static cursors, a cursor variable does not have to be closed before it can be re-opened again. The result set from the previous open will be lost. The example is completed with the addition of the CLOSE statement.

Syntax:
CLOSE cursor_name;

N.B:

• Comparison operators cannot be used to test cursor variables for equality, inequality, null, or not null.


• Null cannot be assigned to a cursor variable.


• The value of a cursor variable cannot be stored in a database column.


• Static cursors and cursor variables are not interchangeable. For example, a static cursor cannot be used in an OPEN FOR statement.

Permitted Cursor Variable Parameter Modes

Operation	IN	IN OUT	OUT
OPEN	No	Yes	No
FETCH	Yes	Yes	No
CLOSE	Yes	Yes	No

Create Country Table

SQL Code:
CREATE TABLE COUNTRIES
  (
    C_NAME  VARCHAR2(15 BYTE)
  );

Insert Data into Country Table

SQL Code:
REM INSERTING into COUNTRIES
Insert into COUNTRIES (C_NAME) values ('Poland');
Insert into COUNTRIES (C_NAME) values ('Germany');
Insert into COUNTRIES (C_NAME) values ('United States');
Insert into COUNTRIES (C_NAME) values ('Portugal');
Insert into COUNTRIES (C_NAME) values ('Czech Republic');
Insert into COUNTRIES (C_NAME) values ('China');
Insert into COUNTRIES (C_NAME) values ('Slovakia');
Insert into COUNTRIES (C_NAME) values ('Slovenia');

Procedure using ref cursor

create or replace
PROCEDURE p_Get_Country (
    p_CountryName     IN VARCHAR2
    )
  AS
  rc_Country_Search  SYS_REFCURSOR;
  v_query            VARCHAR2(30000) := NULL;
  rows_fetched       number;
  TYPE t_tab IS TABLE OF COUNTRIES%ROWTYPE;
  rec_tab t_tab;
BEGIN
  if (p_CountryName is not null) then
    v_query := 'SELECT C_NAME FROM COUNTRIES 
                where UTL_MATCH.JARO_WINKLER_SIMILARITY
                (C_NAME,'|| '''' || to_char(p_CountryName) || ''''  || ') > 60';
  else
    v_query := 'SELECT C_NAME FROM COUNTRIES';
  end if;

  OPEN  rc_Country_Search FOR v_query;
  FETCH rc_Country_Search BULK COLLECT INTO rec_tab;
  rows_fetched := rc_Country_Search%ROWCOUNT;
  --Print Data to the console
  dbms_output.put_line('--------------------------------');
  FOR i IN 1..rows_fetched LOOP
      dbms_output.put_line('Country Name -> '      ||  rec_tab(i).C_NAME||CHR( 13 ) || CHR( 10 ));
  END LOOP;
  dbms_output.put_line('--------------------------------');
  CLOSE rc_Country_Search;
END p_Get_Country;

Out put 1

Set serveroutput on
DECLARE
  RC_COUNTRY_SEARCH SYS_REFCURSOR;
BEGIN
  P_GET_COUNTRY(
    P_COUNTRYNAME => null
  );
END;
--------------------------------
Country Name -> Poland
Country Name -> Germany
Country Name -> United States
Country Name -> Portugal
Country Name -> Czech Republic
Country Name -> China
Country Name -> Slovakia
Country Name -> Slovenia
--------------------------------

Out put 2

Set serveroutput on
DECLARE
  RC_COUNTRY_SEARCH SYS_REFCURSOR;
BEGIN
  P_GET_COUNTRY(
    P_COUNTRYNAME => 'Slov'
  );
END;
--------------------------------
Country Name -> Slovakia
Country Name -> Slovenia
--------------------------------

Procedure for Ref Cursor population and return through out parameter

create or replace
PROCEDURE p_Get_Country2 (
    p_CountryName     IN VARCHAR2,
    rc_Country_Search  OUT SYS_REFCURSOR
    )
  AS
  v_query         VARCHAR2(30000) := NULL;
  TYPE t_tab IS TABLE OF COUNTRIES%ROWTYPE;
  rec_tab t_tab;
BEGIN
  if (p_CountryName is not null) then
    v_query := 'SELECT C_NAME FROM COUNTRIES
                where UTL_MATCH.JARO_WINKLER_SIMILARITY
                (C_NAME,'|| '''' || to_char(p_CountryName) || ''''  || ') > 60';
  else
    v_query := 'SELECT C_NAME FROM COUNTRIES';
  end if;

  OPEN  rc_Country_Search FOR v_query;

END p_Get_Country2;

Populate Ref cursor variable in C#.Net Console base application using ADO.NET

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using System.Data;

namespace TEST_ODP
{
    class Program
    {
        static void Main(string[] args)
        {
            // create connection
            OracleConnection conn = new OracleConnection("Data Source=orcl;User;Password=test;");
            // create the command for the stored procedure
            OracleCommand cmd = new OracleCommand();

            Console.WriteLine("Please input your searching keyword.");
            string str = Console.ReadLine();

            cmd.Connection = conn;
            cmd.CommandText = "p_Get_Country2";
            cmd.CommandType = CommandType.StoredProcedure;

            // add the parameters for the stored procedure including the REF CURSOR
            // to retrieve the result set
            cmd.Parameters.Add("p_CountryName", OracleType.VarChar).Value = str.ToString();
            cmd.Parameters.Add("rc_Country_Search", OracleType.Cursor).Direction = ParameterDirection.Output;

            // open the connection and create the DataReader
            conn.Open();
            OracleDataReader dr = cmd.ExecuteReader();

            // output the results and close the connection.
            while (dr.Read())
            {
                for (int i = 0; i < dr.FieldCount; i++)
                    Console.Write(dr[i].ToString() + ";");
                Console.WriteLine();
            }
            conn.Close();
            Console.ReadLine();
        }
    }
}