如何在Golang中通过SQL Plus执行.sql脚本生成表与数据文件？

Alright, let's get this sorted. You're trying to run that SQL*Plus script from Go using os/exec, and you need a complete working solution. I've put together an implementation that handles all the moving parts, including script execution, output capture, and cleanup.

First, here's a full working example that executes your SQL script, manages temporary files, captures output, and includes proper error handling:

package main

import (
	"io/ioutil"
	"log"
	"os"
	"os/exec"
)

func main() {
	// Your SQL script content (exactly as you provided)
	sqlScript := `set headsep off
set pagesize 0
set trimspool on
set trimout on
create table tb_data_20180502104923 as select * from tb_data;
spool tb_data_20180502104923.txt
SELECT data_id||';'||data_content FROM tb_data_20180502104923;
spool off`

	// Create a temporary SQL file to pass to SQL*Plus
	tmpFile, err := ioutil.TempFile("", "temp-*.sql")
	if err != nil {
		log.Fatalf("Couldn't create temporary file: %v", err)
	}
	// Clean up the temp file when we're done, no matter what
	defer func() {
		_ = tmpFile.Close()
		_ = os.Remove(tmpFile.Name())
	}()

	// Write your script content to the temp file
	if _, err := tmpFile.WriteString(sqlScript); err != nil {
		log.Fatalf("Failed to write script to temp file: %v", err)
	}
	// Ensure all content is flushed to disk before running SQL*Plus
	if err := tmpFile.Sync(); err != nil {
		log.Fatalf("Couldn't sync temp file to disk: %v", err)
	}

	// Build the SQL*Plus command
	// Replace "your_username/your_password@your_database" with your actual Oracle connection string
	// The -S flag runs SQL*Plus in silent mode (hides welcome banners)
	sqlPlusCmd := exec.Command(
		"sqlplus",
		"-S",
		"your_username/your_password@your_database",
		"@"+tmpFile.Name(),
	)

	// Capture stdout and stderr to debug any issues
	stdout, err := sqlPlusCmd.StdoutPipe()
	if err != nil {
		log.Fatalf("Failed to get stdout pipe: %v", err)
	}
	stderr, err := sqlPlusCmd.StderrPipe()
	if err != nil {
		log.Fatalf("Failed to get stderr pipe: %v", err)
	}

	// Start the SQL*Plus process
	if err := sqlPlusCmd.Start(); err != nil {
		log.Fatalf("Couldn't start SQL*Plus: %v", err)
	}

	// Read output streams
	stdoutOutput, _ := ioutil.ReadAll(stdout)
	stderrOutput, _ := ioutil.ReadAll(stderr)

	// Wait for the process to finish
	if err := sqlPlusCmd.Wait(); err != nil {
		log.Fatalf("SQL*Plus execution failed: %v\nError details: %s", err, string(stderrOutput))
	}

	// Optional: Print output for debugging
	log.Printf("SQL*Plus run output:\n%s", string(stdoutOutput))
	log.Println("Success! Your table was created and data exported to tb_data_20180502104923.txt.")
}

Key Details Explained #

• Temporary File Usage: SQL*Plus works best with script files (using the @filename syntax) rather than passing long scripts as command-line arguments. This avoids issues with line breaks and special characters that can break command execution.
• Silent Mode (-S): This suppresses SQL*Plus's default welcome and exit messages, making the output cleaner and easier to parse.
• Output Capture: By capturing stdout and stderr, you can debug common issues like invalid database credentials, missing source tables, or file permission errors.
• Automatic Cleanup: The defer block ensures the temporary SQL file is deleted after execution, even if an error stops the program early.

Important Notes #

1. SQL*Plus Path: Make sure sqlplus is in your system's PATH. If not, replace "sqlplus" in the command with the full path to the executable (e.g., /usr/bin/sqlplus on Linux or C:\Oracle\bin\sqlplus.exe on Windows).
2. Connection String: Replace your_username/your_password@your_database with your actual Oracle connection details (e.g., scott/tiger@localhost:1521/orcl).
3. Permissions: Ensure your Go program has write access to the current working directory (where the tb_data_20180502104923.txt file will be created) and can create temporary files in your system's temp directory.
4. Error Handling: The example uses log.Fatalf for simplicity, but in production code, you might want to return errors to the caller instead of exiting immediately for more graceful error handling.

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