unable to execute sql procedure on oracle live sql-CodePudding

I created a procedure

create or replace procedure dba_role 
as 
   user  varchar2(200); 
   ref   varchar2(200);  
   
begin  
   
  insert into dba_role_privs(grantee,granted_role) (select user as grantee,granted_role from dba_role_privs where grantee=ref);
 
end;

The procedure is getting created but I'm not able to execute the procedure. I've tried different methods to execute it by passing parameters but nothing worked.

Can anyone please tell how to execute this procedure in oracle live SQL the parameters to be passed are both strings(varchars)

for example: I've tried "Execute dba_role('alex','hunter');

The error is **ORA-06550: line 1, column 7: **PLS-00306: wrong number or types of arguments in call to 'DBA_ROLE' **

CodePudding user response：

create or replace procedure 
    dba_role(p_user IN VarChar2, p_ref IN VarChar2) AS  

begin  
  insert into dba_role_privs(grantee, granted_role) (select p_user as grantee, granted_role from dba_role_privs where grantee = p_ref);
end dba_role; 
/

Above is how it should be defined - with two VarChar2 parameters. And below is how to call it:

Begin
    dba_role('alex', 'hunter');
End;
/

The problem with your code was that user and ref were declared as variables within the scope of the procedure (not as parameters) so, when the procedure was called with parameters (like I did above) then you tryed to pass two parameters to the procedure not accepting any. On the other side, if you call it without parameters (just as dba_role;) then user and ref were both Null.

CodePudding user response：

As well as missing the two parameters that you are trying to pass (parameters should appear in brackets immediately following the procedure name), you can't insert into a DBA view. For one thing, it's not in your schema (unless you are creating your procedure as SYS, which you should never do because SYS is reserved for Oracle internals) and you haven't been granted INSERT privilege, but also because it is defined with multiple joins and is therefore not an updatable view.

Even if you did have privileges and it was updatable, directly updating internal data dictionary tables is unsupported and could damage your database. If you want to grant a privilege to a role you should use the GRANT command:

grant reports_user to hr;

To revoke the grant,

revoke reports_user from hr;