如何在Bash脚本中持续使用Oracle私有临时表？

Oracle私有临时表是会话绑定的，会话结束就会被自动删除，你用EOF包裹SQL的方式每次都会启动新的sqlplus会话，执行完就断开，自然没法复用临时表。下面是几种可行的解决方案：

方案1：用命名管道保持sqlplus会话持续打开 #

通过创建命名管道，让Bash和同一个sqlplus会话持续交互，所有操作都在同一会话中完成，私有临时表会一直存在直到会话关闭。

# 创建命名管道
mkfifo sql_pipe

# 启动sqlplus会话，后台运行并从管道读取命令
sqlplus ${username}/${password}${url} @sql_pipe &
SQLPLUS_PID=$!

# 1. 创建私有临时表
echo "CREATE PRIVATE TEMPORARY TABLE ora\$ppt_temp2(id INT, description VARCHAR2(100)) ON COMMIT PRESERVE DEFINITION;" > sql_pipe

# 2. 从临时表导出数据到本地文件（关闭SQL的表头和反馈信息）
echo "SET HEADING OFF; SET FEEDBACK OFF; SET PAGESIZE 0; SPOOL temp_data.txt; SELECT id || ',' || description FROM ora\$ppt_temp2; SPOOL OFF;" > sql_pipe

# 3. 在Bash中处理数据（这里写你的自定义逻辑，比如用awk/sed修改内容）
processed_file="processed_data.txt"
awk -F',' '{print $1 "," $2 "_processed"}' temp_data.txt > $processed_file

# 4. 将处理后的数据写回临时表
# 先准备SQL插入语句，这里假设每行是id,description格式
while IFS=',' read -r id desc; do
  echo "INSERT INTO ora\$ppt_temp2(id, description) VALUES($id, '$desc'); COMMIT;" > sql_pipe
done < $processed_file

# 5. 关闭sqlplus会话
echo "EXIT;" > sql_pipe
wait $SQLPLUS_PID

# 清理临时文件和管道
rm sql_pipe temp_data.txt $processed_file

方案2：在同一个sqlplus会话中嵌入Bash处理逻辑 #

利用Oracle的HOST命令，在sqlplus会话内部调用Bash脚本处理数据，全程保持同一会话，避免临时表被删除。

步骤说明： #

1. 先给Oracle用户创建并授权临时目录对象（用于读写文件）：

CREATE DIRECTORY TEMP_DIR AS '/your/temp/path';
GRANT READ, WRITE ON DIRECTORY TEMP_DIR TO ${username};

2. Bash脚本代码：

sqlplus ${username}/${password}${url} <<EOF
  -- 创建私有临时表
  CREATE PRIVATE TEMPORARY TABLE ora\$ppt_temp2(
    id INT,
    description VARCHAR2(100)
  ) ON COMMIT PRESERVE DEFINITION;

  -- 导出临时表数据到服务器本地文件
  SET HEADING OFF; SET FEEDBACK OFF; SET PAGESIZE 0;
  SPOOL /your/temp/path/temp_data.txt
  SELECT id || ',' || description FROM ora\$ppt_temp2;
  SPOOL OFF;

  -- 调用Bash脚本处理数据（process_data.sh是你自己的处理脚本）
  HOST ./process_data.sh /your/temp/path/temp_data.txt /your/temp/path/processed_data.txt;

  -- 将处理后的数据导入回临时表
  SET DEFINE OFF;
  DECLARE
    v_id INT;
    v_desc VARCHAR2(100);
    v_file UTL_FILE.FILE_TYPE;
    v_line VARCHAR2(200);
  BEGIN
    v_file := UTL_FILE.FOPEN('TEMP_DIR', 'processed_data.txt', 'R');
    LOOP
      UTL_FILE.GET_LINE(v_file, v_line);
      v_id := TO_NUMBER(SUBSTR(v_line, 1, INSTR(v_line, ',')-1));
      v_desc := SUBSTR(v_line, INSTR(v_line, ',')+1);
      INSERT INTO ora\$ppt_temp2(id, description) VALUES(v_id, v_desc);
    END LOOP;
    UTL_FILE.FCLOSE(v_file);
    COMMIT;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      UTL_FILE.FCLOSE(v_file);
      COMMIT;
  END;
  /

  -- 这里可以继续执行其他临时表操作
  SELECT COUNT(*) FROM ora\$ppt_temp2;
EOF

关键说明： #

• 无法恢复已经断开的Oracle会话：会话一旦终止，私有临时表会被Oracle自动清理，没有恢复可能。
• 两种方案的核心都是保持同一个sqlplus会话贯穿所有操作，确保私有临时表的生命周期覆盖整个数据处理流程。

内容的提问来源于stack exchange，提问作者abcd1234