Windows下用sqlplus导出SQL Developer查询结果至文件（免明文账号密码）

Got it, let's break down how to export your SQL Developer query results using sqlplus on Windows—without hardcoding your username or password, which is smart for security. Here are a few reliable methods, sorted by ease of use and security level:

This is the simplest way to avoid exposing credentials entirely. You'll log in manually, with your password hidden from view:

1. Open Command Prompt (CMD) or PowerShell on your Windows machine.
2. Launch sqlplus in "no-login" mode:

   sqlplus /nolog
   

3. Connect to your database using this command—you'll be prompted to enter your password (it won't display on screen):

   CONNECT your_username@your_database_name
   

4. Run the export logic using the SPOOL command to write results to a file. Adjust the settings to match your desired output format (here's a CSV example):

   -- Configure output settings
   SPOOL C:\your\target\path\output_file.csv
   SET COLSEP ','          -- Set column separator for CSV
   SET LINESIZE 1000       -- Adjust based on your widest row
   SET PAGESIZE 0          -- Disable pagination
   SET FEEDBACK OFF        -- Hide "rows selected" messages
   SET HEADINGS ON         -- Keep column headers in output
   
   -- Paste your SQL Developer query here
   SELECT column1, column2, column3 FROM your_table;
   
   -- Stop writing to the file and exit
   SPOOL OFF
   EXIT
   

If you want to automate the process but still avoid hardcoding credentials, use a temporary Windows environment variable that only exists for your current command line session:

1. In CMD, set a temporary variable for your password (it won't persist after you close the window):

   SET ORACLE_PWD=your_secure_password
   

2. Log into sqlplus using the environment variable. You can do this directly:

   sqlplus your_username@your_database_name/@%ORACLE_PWD%
   

   Or via the no-login mode:

   sqlplus /nolog
   CONNECT your_username@your_database_name/&ORACLE_PWD
   

3. Run the same SPOOL export script from Method 1 to save your results.

For frequent exports, an Oracle Wallet encrypts and stores your credentials, so you never have to enter or write them at all:

1. Create a wallet directory (e.g., C:\oracle_wallet) and initialize the wallet with a master password:

   mkstore -wrl C:\oracle_wallet -create
   

   Follow the prompts to set a secure master password (you'll need this to manage the wallet later).
2. Add your database credentials to the wallet:

   mkstore -wrl C:\oracle_wallet -createCredential your_database_name your_username your_password
   

3. Edit your sqlnet.ora file (usually located in ORACLE_HOME\network\admin) to point to the wallet:

   SQLNET.WALLET_OVERRIDE = TRUE
   WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = C:\oracle_wallet)))
   

4. Now you can log into sqlplus without any credentials:

   sqlplus /@your_database_name
   

   Run your SPOOL export script as before to save results.

小贴士: Always double-check your output file after export—tweak the SET parameters (like LINESIZE or COLSEP) if your data is truncated or formatted incorrectly.

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