Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Unanswered: Use Accept And Prompt Within Plsql

    Hello, I want to add users to my database. I want to add all users from my usertable to oracle. i made i script like this.
    -- ADD USERS TO INSTANCE
    DECLARE
    CURSOR curUsers IS SELECT SUSENAME FROM ISH.SUSER;
    BEGIN
    FOR recUser IN curUsers LOOP
    DROP USER recUser.SUSENAME CASCADE;

    -- ACCEPT users_password PROMPT 'Please enter the password for the SYSTEM account: '

    EXECUTE IMMEDIATE 'CREATE USER '||recUser.SUSENAME||'
    IDENTIFIED BY &users_password
    DEFAULT TABLESPACE &&default_tablespace
    TEMPORARY TABLESPACE &&temp_tablespace
    QUOTA UNLIMITED ON &&default_tablespace';
    END LOOP;
    END;
    ------------------------------
    How can I get userinput here for the password of the user i'm going to add.

    thanx

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Use Accept And Prompt Within Plsql

    Originally posted by thepercival
    Hello, I want to add users to my database. I want to add all users from my usertable to oracle. i made i script like this.
    -- ADD USERS TO INSTANCE
    DECLARE
    CURSOR curUsers IS SELECT SUSENAME FROM ISH.SUSER;
    BEGIN
    FOR recUser IN curUsers LOOP
    DROP USER recUser.SUSENAME CASCADE;

    -- ACCEPT users_password PROMPT 'Please enter the password for the SYSTEM account: '

    EXECUTE IMMEDIATE 'CREATE USER '||recUser.SUSENAME||'
    IDENTIFIED BY &users_password
    DEFAULT TABLESPACE &&default_tablespace
    TEMPORARY TABLESPACE &&temp_tablespace
    QUOTA UNLIMITED ON &&default_tablespace';
    END LOOP;
    END;
    ------------------------------
    How can I get userinput here for the password of the user i'm going to add.

    thanx
    You can't dop that. ACCEPT and PROMPT are SQL Plus commands, they are not part of the PL/SQL or SQL languages.

    You could put the ACCEPT command in a SQL Plus script before starting the PL/SQL like this:

    Code:
    ACCEPT users_password PROMPT 'Please enter the password for the SYSTEM account: '
    
    -- ADD USERS TO INSTANCE 
    DECLARE 
    	CURSOR curUsers IS SELECT SUSENAME FROM ISH.SUSER;
    BEGIN
     	FOR recUser IN curUsers LOOP				
     		DROP USER recUser.SUSENAME CASCADE;
    			
     		EXECUTE IMMEDIATE 'CREATE USER '||recUser.SUSENAME||'
     		IDENTIFIED BY &users_password
     		DEFAULT TABLESPACE &&default_tablespace
     		TEMPORARY TABLESPACE &&temp_tablespace
     		QUOTA UNLIMITED ON &&default_tablespace';  
    	END LOOP;	
    END;
    Of course, the ACCEPT will only happen once, not for each iteration of the loop as you had it.

    If you want more than this basic interaction with the user - specifically, if you want to interact with the user from within your application - then you need to build it with something more sophisticated than SQL Plus, e.g. Oracle Forms, PSP, ASP, Pro*C, ...

  3. #3
    Join Date
    Sep 2010
    Posts
    5

    Alter quota size

    I am trying to alter tablespace quota size for a user for different tables in the same statement but I am getting a "cannot grant quota on tablespace" My query and error message is below

    For example, the user was added to the the APPS tablespace and the temporary tablespace TEMP when I created the user. My query is this:

    ALTER USER TESTER
    QUOTA UNLIMITED ON APPS
    QUOTA UNLIMITED ON TEMP;

    ERROR at line 1:
    ORA-30041: Cannot grant quota on the tablespace

    Is it not possible to alter the quota size for a temporary tablespace? Any help would be appreciated. Thanks

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    What happens when you do 1 TS at a time?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2010
    Posts
    5
    When I do them separately, one works but the temporary tablespace does not, this is the error message;

    ERROR at line 1:
    ORA-30041: Cannot grant quota on the tablespace

  6. #6
    Join Date
    Sep 2010
    Posts
    5

    diange very

    How would I show or list the roles and users who have access to the EMPLOYEES tables owned by the “HR” user. I am learning Oracle and using the sample schema that comes with Oracle 10g. Thanks

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I am learning Oracle
    I believe that this little "tip" might help you better understand Oracle: use dictionary.
    Code:
    SQL> desc dictionary
     Name                                                  Null?    Type
     ----------------------------------------------------- -------- ------------------------------------
     TABLE_NAME                                                     VARCHAR2(30)
     COMMENTS                                                       VARCHAR2(4000)
    
    SQL> select * from dictionary where table_name like '%ROLE%';
    
    TABLE_NAME                     COMMENTS
    ------------------------------ ---------------------------------------------
    USER_ROLE_PRIVS                Roles granted to current user
    ROLE_ROLE_PRIVS                Roles which are granted to roles
    ROLE_SYS_PRIVS                 System privileges granted to roles
    ROLE_TAB_PRIVS                 Table privileges granted to roles
    SESSION_ROLES                  Roles which the user currently has enabled.
    
    SQL>
    Now it is your turn to do some investigation. I believe someone has already pointed you to Oracle documentation, available at Search and Download Oracle Database, Application Server, and Collaboration Suite Documentation .

    Also, it would be a good idea to put new questions into new topics, not asking everything you have in an unrelated one.

  8. #8
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    mdb81,

    if you read the entire error message, the "problem" becomes evident:

    Code:
    GISDB1@as02971>/oracle> oerr ora 30041
    30041, 00000, "Cannot grant quota on the tablespace"
    // *Cause: User tried to grant quota on an undo or temporary tablespace
    // *Action: Check the tablespace name and reissue the command
    You cannot assign a quota to a temporary- or undo tablespace.

    (As always: When no version is mentioned, I assume the latest, which is - at the moment -11gR2)
    Last edited by magicwand; 09-21-10 at 09:58. Reason: typos fixed
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

Posting Permissions

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