CODEWITHSUNDEEP
sqlplsqloracle11g
user2062656
user2062656

Reputation: 63

Executing SQL Script

when i run 

SQL>execute deactivate_user

I get errors:

SQL> exec deactivate_user
BEGIN deactivate_user; END;

  *

ERROR at line 1: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'DEACTIVATE_USER' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

Here is my code:

create or replace procedure deactivate_user 
(
p_username varchar2
)
as
l_username varchar2(30):=upper(p_username);
cnmd varchar2(50);
begin
    for rec in (select privilege, admin_option from dba_sys_privs
            where grantee =l_username) loop
            cnmd := 'REVOKE '||rec.privilege||' from '||l_username;
execute immediate cnmd;
end loop;
end;
/

Upvotes: 0

Views: 140

Answers (3)

user2001117
user2001117

Reputation: 3777

yeah you need to pass the parameter while calling the store procedure as per your code.

as below:

exec deactivate_user('USERname1');

But if you dont want to pass any input value then set argument default parameter whenever you call sp then it will take automatic default value.

as below

create or replace procedure deactivate_user 
(
 p_username varchar2 DEFAULT 'USERname1'
)

.... Then you do not need to enter the value for p_username. But by default your procedure will always be executed for 'USERname1' unless you change the parameter value.

Then you will call the above procedure like:

exec deactivate_user;

Upvotes: 0

Art
Art

Reputation: 5782

In addition:

create or replace procedure deactivate_user 
(
 p_username varchar2 DEFAULT 'USER1'
)
....

Then you do not need to enter the value for p_username. But by default your procedure will always be executed for 'USER1' unless you change the parameter as in Rajesh's example.

Upvotes: 0

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18808

create or replace procedure deactivate_user 
(
p_username varchar2
)

your procedure is expecting an input (there is no default, so theinput must be provided).

SQL> exec deactivate_user

You need to pass in the userid that you are trying to deactivate. Something like.

SQL> exec deactivate_user('USER1');

Upvotes: 2

Related Questions