execute.sql files on a oracle DB in shell script

Question

I want to execute .sql files which are written with sql*plus commands on a oracle DB from a shell script.

An example of one .sql file you can see below:

  SET DEFINE OFF;

  -- Changeset om-core/om-core.sys.db-changelog.xml::om-core-createUser.24.0.0::mlo
  -- Creating om-dashboard schema/user
  CREATE USER omcore27 IDENTIFIED BY omcore;
  GRANT CONNECT TO omcore27;
  GRANT CREATE TABLE TO omcore27;
  GRANT CREATE SEQUENCE TO omcore27;
  GRANT CREATE ANY INDEX, SELECT ANY TABLE TO omcore27;
  GRANT CREATE TRIGGER TO omcore27;
  GRANT CREATE PROCEDURE TO omcore27;
  GRANT UNLIMITED TABLESPACE TO omcore27;
  GRANT EXECUTE ON SYS.DBMS_AQADM to omcore27;
  GRANT EXECUTE ON SYS.DBMS_AQ to omcore27;
  ALTER USER omcore27 QUOTA UNLIMITED ON SYSTEM;
  GRANT SELECT ON SYS.DBA_RECYCLEBIN TO omcore27;
  GRANT EXECUTE ON DBMS_AQIN to omcore27;
  GRANT select on v_$sysmetric to omcore27;
  GRANT select on dba_hist_sysmetric_summary TO omcore27;
  GRANT create job TO omcore27;
  GRANT create external job TO omcore27;

  -- Changeset om-core/om-core.sys.db-changelog.xml::om-core-grantAQ.24.0.0::mlo
  begin
            DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE (privilege => 'ENQUEUE_ANY', grantee => 'omcore27', admin_option => FALSE);
            DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE (privilege => 'DEQUEUE_ANY', grantee => 'omcore27', admin_option => FALSE);
            DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE (privilege => 'MANAGE_ANY', grantee => 'omcore27', admin_option => FALSE);
        end;/

So I need a shell command to execute above .sql file in on oracleDB.

I already tried to do that with a java program and then run that program .jar from my shell script, but I get that Error:

 Line: SET DEFINE OFF;
 *** Error : java.sql.SQLSyntaxErrorException: ORA-00922: missing or invalid option

This is the sourecode of my DBScriptRunner class:

public class DBScriptRunner {
    public static void main(String[] args) {
        try {
            String pathname = args[0];
            System.out.println("path name: " +pathname);
            executeScript(pathname);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    static void executeScript(String scriptFilePath) throws IOException, SQLException {
        // initialize script path
        // String scriptFilePath = "e:/script.sql";
        BufferedReader reader = null;
        Connection con = null;
        Statement statement = null;
        try {

            Class.forName("oracle.jdbc.driver.OracleDriver");
            // create connection
            con = DriverManager.getConnection("jdbc:oracle:thin:@//hostname:1521/romdb", "user",
                    "pwd");
            statement = con.createStatement();
            // initialize file reader
            reader = new BufferedReader(new FileReader(scriptFilePath));
            String line = null;
            // read script line by line
            while ((line = reader.readLine()) != null) {
                // execute query
                if (line.startsWith("--")|| line.isEmpty()) {
                    System.out.println("comment line or empty line: " + line);
                } else {
                    System.out.println("Line: " + line);
                    statement.execute(line);
                }

            }
        } catch (Exception e) {
            System.out.println("*** Error : "+e.toString());  
            System.out.println("*** ");  
            System.out.println("*** Error : ");  
            e.printStackTrace();
        } finally {
            // close file reader
            if (reader != null) {
                reader.close();
            }
            // close db connection
            if (con != null) {
                con.commit();
                con.close();
            }
        }
    }
}

Do you have any idea?

Why aren't you just calling SQL*Plus from your shell script? I don't get why you are trying to involve Java here? — Alex Poole
– Alex Poole, Commented Sep 20, 2018 at 8:01
because i'm not to familiar with shell script. can you tell me how i can to call SQL*plus in shell script please. THX — guguli
– guguli, Commented Sep 20, 2018 at 8:06

Alex Poole · Accepted Answer · 2018-09-20 08:26:35Z

3

Your script just needs to call SQL*Plus itself. Assuming the Oracle-related environment variables are set up already in the shell you'll call this from, the basic format is:

sqlplus -l -s user/pwd@hostname:1521/romdb @your_script.sql

or if your script doesn't have an exit at the end, with redirection instead:

sqlplus -l -s user/pwd@hostname:1521/romdb < your_script.sql

The -l flag prevents it reprompting for credentials if the supplied ones don't work for any reason, which can be particularly useful when running the script unattended. The -s flag suppresses the SQL*Plus banner and echoing of the commands as they are run.

You can then adapt and extend that in many ways, e.g. to set up the environment within the script, to add capture of the output to a log file, or to hide the login credentials from the command line so they aren't visible to ps, etc. The latter might look like:

sqlplus -s /nolog <<EOF
connect user/pwd@hostname:1521/romdb
@your_script.sql
EOF

edited Sep 20, 2018 at 8:26

answered Sep 20, 2018 at 8:14

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

guguli Over a year ago

Thanks, i have another issue, i get : sqlpl: command not found. So at the end my script should create a test environment automatically, and i don't know if other people, who want to use that script have already installed sqlplus or not. Do you have any idea if there is a docker image for sqlplus which I can run in my script to be able to use sqlplus?!

Alex Poole Over a year ago

I don't know anything about Docker. There is an instant client you can install easily. And the script can set up the environment to point to that.

Raja G · Accepted Answer · 2018-09-20 08:22:49Z

0

You can use below bash script.

#!/usr/bin/env bash
username="username"
password="XXXXXXXXXXX"
db_ip="ipaddress"
port="1521"
sid=""
schema="schema"


echo "

select * from $schema.$tablename ;
## you can place all your queries here.
commmit;
exit
" | sqlplus -s "$username/$password@$db_ip:$port/$sid"

Hope it helps.

As shown above your can append your .sql file to sqlplus command as below

sqlplus -s "$username/$password@$db_ip:$port/$sid @script.sql"

edited Sep 20, 2018 at 8:22

answered Sep 20, 2018 at 8:09

Raja G

6,80315 gold badges54 silver badges90 bronze badges

1 Comment

guguli Over a year ago

thanks. But my queries are in a .sql file (see above). how can I execute the whole file?

Collectives™ on Stack Overflow

execute.sql files on a oracle DB in shell script

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related