java执行。oracle DB in shell脚本上的sql文件

Question

我想从shell脚本在oracle数据库上执行.sql个文件，这些文件是用sql*plus命令编写的

下面是一个.sql文件的示例：

  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;/

所以我需要一个shell命令来在oracleDB上执行.sql文件

我已经尝试过用java程序来实现这一点，然后从shell脚本中运行该程序.jar，但我得到了错误：

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

这是我的DBScriptRunner类的源代码：

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();
            }
        }
    }
} 

你知道吗

Answer 1

您可以使用下面的bash脚本

#!/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"

希望有帮助

如上图所示，您可以将.sql文件附加到sqlplus命令，如下所示

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

Answer 2

您的脚本只需要调用SQL*Plus本身。假设与Oracle相关的环境变量已在shell中设置好，则基本格式为：

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

或者，如果脚本末尾没有exit，请改为重定向：

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

如果所提供的凭据因任何原因无法工作，-l标志可防止它重新为凭据进行复制，这在无人参与运行脚本时尤其有用。-s标志会在命令运行时抑制SQL*Plus标志和命令的回显

然后，您可以通过多种方式对其进行调整和扩展，例如，在脚本中设置环境，将输出捕获添加到日志文件，或者从命令行隐藏登录凭据，使其对ps不可见，等等。后者可能如下所示：

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