CODEWITHSUNDEEP
inputplsqlparameterscursor
Sangathamilan Ravichandran
Sangathamilan Ravichandran

Reputation: 381

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

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.

Upvotes: 0

Views: 5409

Answers (2)

Sangathamilan Ravichandran
Sangathamilan Ravichandran

Reputation: 381

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;

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132570

Don't quote the variable name, as that makes it just a string of text 'v_Model_UserName':

SELECT granted_role from dba_role_privs where grantee = v_Model_UserName;

(I also removed the redundant parentheses. They did no harm, but added no value).

Upvotes: 1

Related Questions