Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2005
    Posts
    53

    Unanswered: creating user through command prompt

    Hi,

    I am trying to create user through the command prompt but it does not exit or end it just stays in running mode in the windows scheduler:

    Oracle 11.2.0.1 on Windows 2008 R2

    following way i am trying to do:
    1) create user script: test.sql
    CREATE USER test
    IDENTIFIED BY test123
    DEFAULT TABLESPACE TEST
    TEMPORARY TABLESPACE TEMP
    PROFILE "DEFAULT"
    ACCOUNT UNLOCK QUOTA UNLIMITED ON TEST;
    2) command file: createuser.cmd

    sqlplus test/user@test as sysdba @e:/script/test.sql

    what needs to be done:?

    thanks
    Last edited by patdev; 03-13-12 at 16:27. Reason: changes

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The last line in the file TEST.SQL should be as below
    EXIT
    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.

  3. #3
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Cool A more elegant solution

    Another option would be to pipe (or redirect) the test.sql into sqlplus. This method would allow you to leave out the EXIT command in the .SQL script. This has the benefit of being able to run the SQL script manually without being logged out of SQLPLUS.

    E.g.:
    Code:
    sqlplus test/user@test as sysdba < e:/script/test.sql
    or
    Code:
    type e:/script/test.sql | sqlplus test/user@test as sysdba
    Both should work.

    Here's my example:
    Firstly, my script.sql file
    Code:
    select * from user_catalog
     where rownum <= 10
    /
    Next, running the script using input stream redirection
    Code:
    CMD> sqlplus ocs/ocs@devwin10gr2 0<script.sql
    
    SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 14 17:19:33 2012
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    ocs@RMSD>   2    3
    TABLE_NAME                     TABLE_TYPE
    ------------------------------ -----------
    ABNORM_OPERATORS               TABLE
    ABNORM_OPERATORS_SEQ           SEQUENCE
    ABN_DETAIL                     TABLE
    ABN_DETAIL_SEQ                 SEQUENCE
    ANNUAL_HEAVYVEH_TOTALS         TABLE
    APP_PARAMETERS                 TABLE
    APP_PARAMETER_VALUES           VIEW
    AXLE_GROUP                     VIEW
    AXS000$                        SYNONYM
    AXS230$_RUN                    SYNONYM
    
    10 rows selected.
    
    ocs@RMSD> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.
    0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    CMD>
    Lastly, running the script using a DOS pipe
    Code:
    CMD> type script.sql | sqlplus ocs/ocs@devwin10gr2
    
    SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 14 17:20:29 2012
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    ocs@RMSD>   2    3
    TABLE_NAME                     TABLE_TYPE
    ------------------------------ -----------
    ABNORM_OPERATORS               TABLE
    ABNORM_OPERATORS_SEQ           SEQUENCE
    ABN_DETAIL                     TABLE
    ABN_DETAIL_SEQ                 SEQUENCE
    ANNUAL_HEAVYVEH_TOTALS         TABLE
    APP_PARAMETERS                 TABLE
    APP_PARAMETER_VALUES           VIEW
    AXLE_GROUP                     VIEW
    AXS000$                        SYNONYM
    AXS230$_RUN                    SYNONYM
    
    10 rows selected.
    
    ocs@RMSD> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.
    0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    CMD>
    Notice how I am automatically returned to the command prompt without the use of EXIT in the SQL script.

Posting Permissions

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