How to give parameter as input in PL/SQL CURSOR?

Question

I am trying to execute the below code. I am giving v_Model_UserName as input. And i am using it in the select statement. But the parameter is not getting inputed in the select statement.

SET SERVEROUTPUT ON;
DECLARE
    v_Model_UserName VARCHAR2(30) := UPPER('&Model_UserName');
    v_role VARCHAR2(3000);
    CURSOR v_role1 is
    SELECT granted_role from dba_role_privs where grantee = ('v_Model_UserName') ;
    BEGIN
    OPEN v_role1;
    LOOP
    FETCH v_role1 INTO v_role;
    EXIT WHEN v_role1%NOTFOUND;
    dbms_output.put_line(v_role);
    END LOOP;
    CLOSE v_role1;
    END;
    /

Eg. If i give &Mode UserName as HR, It is getting assigned to the variable as v_Model_UserName = HR.

But in the cursor select statement it is still as

"SELECT granted_role from dba_role_privs where grantee = 

('v_Model_UserName');"

I wanted it to be :

"SELECT granted_role from dba_role_privs where grantee = 'HR';"

I have also tried to give

  SELECT granted_role from dba_role_privs where grantee = v_Model_UserName;

Still the HR value is not getting assigned here.

Answer 1

I have made cursor syntax error. Below code works.

DECLARE
    v_Model_UserName VARCHAR2(30) := UPPER('&Model_UserName');
    v_role VARCHAR2(3000);
    CURSOR v_role1 (var01  Varchar2 )is
    SELECT granted_role from dba_role_privs where grantee = var01;
    BEGIN
    OPEN v_role1 (v_Model_UserName);
    LOOP
    FETCH v_role1 INTO v_role;
    EXIT WHEN v_role1%NOTFOUND;
      dbms_output.put_line(v_role);
    END LOOP;
    CLOSE v_role1;
    END;