PL/SQL problem about 'alter' - i'm starter

[fongbao]

i'm writting a simple pl/sql block as follow:
begin
alter user xxx account unlock;
end;
but there is an error
ERROR at line 2:
ORA-06550: line 2, column 6:
PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:
begin declare exit for goto if loop mod null pragma raise
return select update while
<<
close current delete fetch lock insert open rollback
savepoint set sql commit

Pls help me to solve it!!!!!THX!!!!!

[julian]

Use dynamic SQL.

In versions previous to Oracle8i use dbms_sql.

From 8i you can use EXECUTE IMMEDIATE.

[balajiyes]

hi,
i think u have to use DYNAMIC block

for example:

EXECUTE IMMEDIATE
'begin
alter user xxx account unlock;
end';

this sholud help u

[pando]

Originally posted by balajiyes
hi,
i think u have to use DYNAMIC block

for example:

EXECUTE IMMEDIATE
'begin
alter user xxx account unlock;
end';

this sholud help u

that does not work...

begin
    execute immediate 'alter user xxx account lock';
end;
/

if you wanna lock many users you do (for example lock everyone's acc who's got expired in a speicific date, XXXX would your predicate)

begin
    for i in (select * from dba_users where expiry_date = to_date(XXXX))
    loop 
    execute immediate 'alter user '||i.username||' account lock';
    end loop;
end;
/

[Edited by pando on 01-19-2002 at 06:12 AM]

[fongbao]

THX for your help!!!!
i'm using Oracle 8.0.5 and i'll try!!

[pando]

8.0.5?
Then you have to use dbms_sql

execute immediate only works from 8i