Previously we learnt that in PL/SQL we are allowed to create a cursor with parameter known as Cursor Parameter (a.k.a. Parameterized Cursor). Every cursor parameter has some restrictions which we have to comply with for example we have to specify the argument for each parameter when we open the cursor otherwise we get a PLS 00306 error.

To overcome this restriction, we have the option called default value. Default value is the value which you assign to the parameter of your cursor during the declaration of the cursor.

Example 1 of Cursor Parameter with Default Value

SET SERVEROUTPUT ON;
DECLARE
  v_name VARCHAR2(30);
  v_eid  NUMBER(10);
  CURSOR cur_RebellionRider(var_e_id NUMBER := 190 )
  IS
  SELECT first_name, employee_id FROM employees 
  WHERE employee_id > var_e_id; 
BEGIN
  OPEN cur_rebellionrider;
  LOOP
    FETCH cur_rebellionrider INTO v_name, v_eid; 
    EXIT WHEN cur_rebellionrider%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_name ||' '||v_eid); 
  END LOOP;
  CLOSE cur_rebellionrider;
END;
/

As you can see in the above code I assigned a value (which is 190) using assignment operator to the parameter var_e_id in the cursor declaration and nothing while opening the cursor. Upon the execution this PL/SQL block will show you the first name and employee id of all the employees who have employee id greater than 190.

For line by line explanation of the above code please watch the Video Tutorial on my YouTube.

Default Value Is Not a Substitute Value

Default value is not a substitute value for the parameter of your cursor. It comes into action only when the user does not specify the argument for the parameter while opening the cursor. In case user has supplied the argument for the parameter in OPEN CURSOR statement then the compiler will show the result according to that parameter and not according to the Default value.

Example 2 of Cursor Parameter with Default Value

DECLARE
  v_name VARCHAR2(30);
  v_eid  NUMBER(10);
  CURSOR cur_RebellionRider(var_e_id NUMBER := 190 )
  IS
  SELECT first_name, employee_id FROM employees 
  WHERE employee_id > var_e_id; 
BEGIN
  OPEN cur_rebellionrider (200);
  LOOP
    FETCH cur_rebellionrider INTO v_name, v_eid; 
    EXIT WHEN cur_rebellionrider%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_name ||' '||v_eid); 
  END LOOP;
  CLOSE cur_rebellionrider;
END;
/

If you compare the result of both the codes demonstrated in the above examples you will see the difference because the result returned by Example 1 is according to the default value (which is 190) which you specified during the declaration while the result returned by Example 2 will be according to the argument (which is 200) you have specified in the OPEN cursor statement.

Use of Default Value in Cursor Parameter

Specifying the default value for the parameter of your cursor can increase the efficiency of your app or code by minimizing the chances of PLS 00306 error.

Hope you enjoyed reading this blog. Please do share this on your Facebook or Twitter and help your friends in learning new concepts. From now on you can win a goody bag on sharing the blogs, just make sure to tag me.

Have a great day!