This discussion is archived
5 Replies Latest reply: Feb 7, 2013 8:54 PM by BillyVerreynne RSS

Combine 2 scripts

936666 Newbie
Currently Being Moderated
Hi,
i am having 2 scripts
Script 1 : user creation
Script 2 : Table, function ,sp creation
I need to combine this into a single which means
I need to login as sysdba and execute the Script 1 and with that user details i need to login and execute the Script 2

Thanks!
  • 1. Re: Combine 2 scripts
    riedelme Expert
    Currently Being Moderated
    What tool do you plan to run the scripts? SQL*PLUS? SQL*Developer? Something else?

    If you have to log in again with script 2 is there any point to combining the scripts?

    Anyway, did you try simply pasting the contents of script 2 to the end of script 1? Alternately, in SQL*PLUS you can call a script from a script using the '@' (run) command and just run script 2 at the end of script 1
  • 2. Re: Combine 2 scripts
    936666 Newbie
    Currently Being Moderated
    I am using command prompt
    C> sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 7 14:32:57 2013
    Copyright (c) 1982, 2010, Oracle. All rights reserved.
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

    SQL >@E:\DB\createuser.sql
    so it will create new user and have the necessary access details for this user this is fine
    next
    using user name and password
    SQL > sqlplus
    Enter user-name :*****
    Enter password:*****
    SQL >@E:\DB\Tablecreationsql

    Anyway, did you try simply pasting the contents of script 2 to the end of script 1? Alternately, in SQL*PLUS you can call a script from a script using the '@' (run) command and just run script 2 at the end of script 1
    how to pass the username and password in the script.
    Thanks
  • 3. Re: Combine 2 scripts
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    In SQL*Plus, if you want to log in as a different user (say SCOTT, password TIGER), you can use the CONNect command:
    CONN  scott/tiger@dbname
    In this case, you may want to use substitution variables, like this
    DEFINE  username  = scott
    DEFINE  password  = tiger
    
    -- This part is done by SYS:
    
    CREATE USER &username  IDENTIFIED BY  &password;
    
    GRANT  some_role  TO &username;
    ...
    
    -- Now connect as the new user and run script_2.sql:
    CONN  &username/&password@dbname
    
    @script_2
  • 4. Re: Combine 2 scripts
    936666 Newbie
    Currently Being Moderated
    Super this is what i am looking for.

    Thanks!
  • 5. Re: Combine 2 scripts
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Keep in mind that your example showed a local direct connection to the database - and Frank's posting will work for that. However, when the user runs this installation remotely and uses a TNS connection, this approach will not work.

    You also need to pass the connection string to the CONNECT command, that the user used to launch SQL*Plus and ran the 1st part of the installation (creating the user).

    Doing this (passing connection string) safely and securely is problematic. So much so that even Oracle stopped using this approach with Apex v3's installer.

    There is an alternative approach - as the SYS user is used, it can create the user objects on behalf of the user. E.g.
    --// connected as a SYS user
    
    --// create user
    create user FOO identified by ...;
    
    --// set user privs
    grant .. to FOO;
    
    --// change resolution scope to user schema
    alter session set current_schema=FOO;
    
    --// create user objects (these are created by
    --// SYS in the FOO schema)
    create table ...;
    create sequence ..;
    create trigger ...;
    etc...
    The only objects that cannot be created like this, are private/local database links and materialised views, if I recall correctly. In such a case, the installation needs to create a procedure as the user schema, running with definer rights, that creates the object. When SYS calls this procedure during the installation, the procedure runs as FOO and creates the database link/whatever as FOO.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points