UNIX:在后台登录 SQLPLUS 中的 DB，使用该后台连接进行查询执行

在Unix中是否有可能通过sqlplus登录Oracle DB(所有内容都在bash中(，在后台保持连接(只需登录到DB，不执行任何查询(，并在其他进程中使用该进程(连接(来执行SQL查询？

因此，将该连接用作Oracle的"管道"？

示例 1。您可以在后台运行脚本pipe.sh。在另一个会话中，发送 sql 文件或 sql。运行 sql 后重新连接。

more pipe.sh
#!/bin/bash
rm /tmp/sqlplus_pipe.sql
mknod /tmp/sqlplus_pipe.sql p
while :
do
$ORACLE_HOME/bin/sqlplus   "system/manager" <<EOF
@/tmp/sqlplus_pipe.sql
EOF
sleep 1
done

在后台运行此脚本

   nohup ./pipe.sh  >pipe_log.log 2>&1  &

在其他 bash 会话中，您可以将 sql 文件或 sql 发送到此后台进程。

oracle@esmd:/tmp> cat test2.sql >>/tmp/sqlplus_pipe.sql
oracle@esmd:/tmp> cat test2.sql >>/tmp/sqlplus_pipe.sql
oracle@esmd:/tmp> echo "select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') from dual;" >>/tmp/sqlplus_pipe.sql
oracle@esmd:/tmp> echo "select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') from dual;" >>/tmp/sqlplus_pipe.sql

oracle@esmd:/tmp> more test2.sql
select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') from dual;

oracle@esmd:~> more pipe_log.log
SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 8 14:50:35 2019
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL>
TO_CHAR(SYSDATE,'DD
-------------------
08-08-2019 14:50:46
SQL> Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 8 14:50:47 2019
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL>
TO_CHAR(SYSDATE,'DD
-------------------
08-08-2019 14:50:48
SQL> Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 8 14:50:49 2019
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL>
TO_CHAR(SYSDATE,'DD
-------------------
08-08-2019 14:50:49
SQL> Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

更新。 例 2.可以在 Oracle 数据库服务器的后台运行脚本pipev2.sh。在另一个会话中，发送 sql 文件或 sql。运行 sql 后无需重新连接。

 nohup ./pipev2.sh >output.log 2>&1 &
#!/bin/bash
rm /home/trs/db2Toora/sql/sqlplus_pipe.sql
mknod /home/trs/db2Toora/sql/sqlplus_pipe.sql p

$ORACLE_HOME/bin/sqlplus   "system/manager" <<EOF
SET SERVEROUTPUT ON 
    BEGIN
      RUN_SQL;
    END;
/
EOF

CREATE OR REPLACE DIRECTORY TEMP_DIR_CHANGE AS '/home/trs/db2Toora/sql'
/
GRANT READ ON DIRECTORY TEMP_DIR_CHANGE TO SYSTEM
/
GRANT WRITE ON DIRECTORY TEMP_DIR_CHANGE TO SYSTEM
/

    CREATE OR REPALCE PROCEDURE RUN_SQL
is
    sql_text VARCHAR2(2000);
    file_sql_name VARCHAR2(100):='sqlplus_pipe.sql';
    sql_delimiter VARCHAR2(1):=';';
    stop_script VARCHAR2(10):='%QUIT%';
    sql_output VARCHAR2(2000);
    InFile           utl_file.file_type;
    vNewLine         VARCHAR2(4000);
    k  pls_integer :=0;
    BEGIN
    dbms_output.enable;
    while k <>1
    loop
    InFile := utl_file.fopen('TEMP_DIR_CHANGE', file_sql_name,'r');
    LOOP
      BEGIN
      utl_file.get_line(InFile, vNewLine);
      if vNewLine like '%'||sql_delimiter||'%' then
         sql_text:=sql_text||vNewLine;
      dbms_output.put_line(sql_text);
       begin
        execute immediate replace(sql_text,sql_delimiter,'' ) into sql_output;
        EXCEPTION
        WHEN OTHERS THEN
        dbms_output.put_line('!---!--Error--!---!');
        dbms_output.put_line(substr(sqlerrm, 1, 500));
       end;
      dbms_output.put_line(sql_output);
      dbms_output.put_line('---------------------------------------------------------------');
      sql_text:='';
      elsif vNewLine like  stop_script then
      dbms_output.put_line('---!--QUIT--!---');
      EXIT;
      else
      sql_text:=sql_text||vNewLine;
      end if;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
        EXIT;
      END;

    END LOOP;
      if vNewLine like stop_script then
       exit;
      end if;
end loop;
    utl_file.fclose(InFile);
    END;

测试

oracle@esmd:/home/trs/db2Toora/sql> cat test2.sql >sqlplus_pipe.sql
oracle@esmd:/home/trs/db2Toora/sql> cat test2.sql >sqlplus_pipe.sql
oracle@esmd:/home/trs/db2Toora/sql> cat test2.sql >sqlplus_pipe.sql
oracle@esmd:/home/trs/db2Toora/sql> cat test2.sql >sqlplus_pipe.sql
oracle@esmd:/home/trs/db2Toora/sql> cat test2.sql >sqlplus_pipe.sql
oracle@esmd:/home/trs/db2Toora/sql> cat test2.sql >sqlplus_pipe.sql
oracle@esmd:/home/trs/db2Toora/sql> cat test2.sql >sqlplus_pipe.sql
oracle@esmd:/home/trs/db2Toora/sql> cat test2.sql >sqlplus_pipe.sql
oracle@esmd:/home/trs/db2Toora/sql> cat test2.sql >sqlplus_pipe.sql
oracle@esmd:/home/trs/db2Toora/sql> cat test2.sql >sqlplus_pipe.sql
oracle@esmd:/home/trs/db2Toora/sql> cat test2.sql >sqlplus_pipe.sql
oracle@esmd:/home/trs/db2Toora/sql> cat test2.sql >sqlplus_pipe.sql
oracle@esmd:/home/trs/db2Toora/sql> echo 'QUIT'  >sqlplus_pipe.sql
oracle@esmd:/home/trs/db2Toora/sql>

oracle@esmd:/home/trs/db2Toora/sql> more test2.sql
select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS')
from dual
;
select to_char(sysdate,'DD-MM-YYYY HH24:MI')
from dual
;

nohup: ignoring input
SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 13 10:11:23 2019
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL> SQL>   2    3    4  select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS')  from dual;
13-08-2019 10:11:34
---------------------------------------------------------------
select to_char(sysdate,'DD-MM-YYYY HH24:MI')  from dual;
13-08-2019 10:11
---------------------------------------------------------------
select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS')  from dual;
13-08-2019 10:11:35
---------------------------------------------------------------
select to_char(sysdate,'DD-MM-YYYY HH24:MI')  from dual;
13-08-2019 10:11
---------------------------------------------------------------
select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS')  from dual;
13-08-2019 10:11:35
---------------------------------------------------------------
select to_char(sysdate,'DD-MM-YYYY HH24:MI')  from dual;
13-08-2019 10:11
---------------------------------------------------------------
select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS')  from dual;
13-08-2019 10:11:36
---------------------------------------------------------------
select to_char(sysdate,'DD-MM-YYYY HH24:MI')  from dual;
13-08-2019 10:11
---------------------------------------------------------------
select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS')  from dual;
13-08-2019 10:11:36
---------------------------------------------------------------
select to_char(sysdate,'DD-MM-YYYY HH24:MI')  from dual;
13-08-2019 10:11
---------------------------------------------------------------
select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS')  from dual;
13-08-2019 10:11:37
---------------------------------------------------------------
select to_char(sysdate,'DD-MM-YYYY HH24:MI')  from dual;
13-08-2019 12:19
---------------------------------------------------------------
---!--QUIT--!---
PL/SQL procedure successfully completed.
SQL> Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

相关内容

最新更新

热门标签：