Mac本地通过单Bash命令连接远程Oracle并导出脚本结果至CSV

Got it, let's get this sorted for you! The key here is to use SQL*Plus's silent mode combined with a Bash here-document to run your script, format the output properly for CSV, and redirect everything to a local file in one go.

Here's the working single Bash command you can use:

sqlplus -s 'user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))' << EOF > local_output.csv
-- Set formatting options for clean CSV output
SET COLSEP ','
SET PAGESIZE 0
SET LINESIZE 1000
SET TRIMSPOOL ON
SET FEEDBACK OFF
SET HEADING ON
SET ECHO OFF

-- Execute your target script (replace with your actual script path)
@/path/to/your/local/script.sql

-- Exit SQL*Plus cleanly
EXIT;
EOF

Let me break down what each part does to help you adjust it to your needs:

• -s: Runs SQL*Plus in silent mode, stripping out the default welcome messages and prompts so your output only has the query results.
• The SET commands: These tweak SQL*Plus's output to fit CSV standards:
  • COLSEP ',': Sets the column separator to a comma.
  • PAGESIZE 0: Removes page breaks, repeated headers, and empty lines from the output.
  • LINESIZE 1000: Adjusts the line width to prevent your data from wrapping (increase this if your columns are wider).
  • TRIMSPOOL ON: Trims trailing whitespace from each line, avoiding extra commas or spaces in your CSV.
  • FEEDBACK OFF: Hides the "X rows selected" message that SQL*Plus normally adds.
  • HEADING ON: Keeps the column headers in your CSV (set to OFF if you don't want them).
• @/path/to/your/local/script.sql: This executes your script. SQL*Plus will read the local script file and run its commands against the remote database.
• > local_output.csv: Redirects all the formatted output to your local CSV file.

Handling edge cases #

If your script returns columns with commas (like free-text fields), the basic COLSEP approach will break the CSV structure. Fix this by modifying your script's SELECT statements to wrap those columns in double quotes:

SELECT 
  '"'||customer_name||'"',  -- Wrap name in quotes to handle commas
  customer_id,
  '"'||order_notes||'"'
FROM your_table;

Alternatively, if you don't want to modify your script, you can add a SET ENQUOTE ON command (available in newer SQL*Plus versions) to automatically wrap all columns in quotes:

sqlplus -s 'user/pass@(...)' << EOF > local_output.csv
SET COLSEP ','
SET PAGESIZE 0
SET LINESIZE 1000
SET TRIMSPOOL ON
SET FEEDBACK OFF
SET HEADING ON
SET ENQUOTE ON  -- Auto-wrap columns in double quotes
@/path/to/script.sql
EXIT;
EOF

Just replace all the placeholders (user/pass, hostname, script path, output filename) with your actual values, and this command should work perfectly in one go.

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