PL/SQL Scripting Error.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: PL/SQL Scripting Error.

  1. #1
    Join Date
    Jun 2002
    Posts
    2
    I have recently started Oracle script's and have run into an error with my PL/SQL. Can anyone tell me what I'm doing wrong? Thanks,

    DECLARE
    v_userid VARCHAR2 (9) DEFAULT 'User1';
    v_passwrd VARCHAR2 (9) DEFAULT 'User1';

    BEGIN

    CREATE USER v_userid
    DEFAULT TABLESPACE USERS
    TEMPORARY TABLESPACE TEMP
    IDENTIFIED BY v_passwrd ;
    GRANT viewA viewB viewC TO v_userid;

    ALTER USER v_userid DEFAULT ROLE roleA roleB roleC FROM DBA_USERS
    WHERE USERNAME <> 'SYS' AND USERNAME <> 'SYSTEM';

    END;
    /

  2. #2
    Join Date
    May 2002
    Posts
    2,645


    DECLARE
    /*
    || Procedure for dynamically executing SQL statements,
    || needed to execute DDL commands.
    */
    PROCEDURE execute_sql_statement (sql_statement IN VARCHAR2)
    IS
    cursor_handle INTEGER;
    BEGIN
    cursor_handle := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE
    (cursor_handle, sql_statement, DBMS_SQL.NATIVE);
    DBMS_SQL.CLOSE_CURSOR(cursor_handle);
    END execute_sql_statement;

    BEGIN
    execute_sql_statement
    ('CREATE USER user1 identified by user1
    default tablespace users
    temporary tablespace temp');

    and so on for other statements...

    END;

  3. #3
    Join Date
    Jun 2002
    Posts
    2

    Smile

    Thank you, I'll try this out

  4. #4
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105
    if you are using 8i it will be much easier to do this:

    EXECUTE IMMEDIATE 'CREATE USER '||v_userid||'
    DEFAULT TABLESPACE USERS
    TEMPORARY TABLESPACE TEMP
    IDENTIFIED BY '||v_passwrd||' ;
    GRANT viewA viewB viewC TO '||v_userid;

  5. #5
    Join Date
    May 2002
    Posts
    2,645
    This is true - actually, we're switching over to execute immediate for new coding, but I thought the execute sql statement would be clearer because of the comments I snipped out of one of our scripts.

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