PL/SQL problem about 'alter' - i'm starter
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

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

  1. #1
    Join Date
    Dec 2001
    Posts
    4
    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!!!!!


  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Use dynamic SQL.

    In versions previous to Oracle8i use dbms_sql.

    From 8i you can use EXECUTE IMMEDIATE.


  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    hi,
    i think u have to use DYNAMIC block

    for example:

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

    this sholud help u
    Cheers!
    OraKid.

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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...

    Code:
    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]

  5. #5
    Join Date
    Dec 2001
    Posts
    4
    THX for your help!!!!
    i'm using Oracle 8.0.5 and i'll try!!

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    8.0.5?
    Then you have to use dbms_sql

    execute immediate only works from 8i

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width