Executing SQL Script

Question

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;
/

Rajesh Chamarthi · Accepted Answer · 2013-02-11 21:52:07Z

2

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');

answered Feb 11, 2013 at 21:52

Rajesh Chamarthi

18.9k4 gold badges43 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Art Over a year ago

@user2062656 - You need to accept the answer - the one that you liked first, e.g. not mine. Always do this besides thanks.

Art · Accepted Answer · 2013-02-11 22:10:50Z

0

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.

answered Feb 11, 2013 at 22:10

Art

5,8222 gold badges24 silver badges22 bronze badges

Comments

user2001117 · Accepted Answer · 2013-02-12 03:48:33Z

0

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;

answered Feb 12, 2013 at 3:48

user2001117

3,7871 gold badge20 silver badges18 bronze badges

Collectives™ on Stack Overflow

Executing SQL Script

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related