5 Replies Latest reply: Feb 7, 2013 10:54 PM by Billy~Verreynne RSS

    Combine 2 scripts

    936666
      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
          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
            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
              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
                Super this is what i am looking for.

                Thanks!
                • 5. Re: Combine 2 scripts
                  Billy~Verreynne
                  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.