github.com/mattn/go-oci8select *desc package main
import (
"bufio"
"database/sql"
"fmt"
"log"
"os"
"strings"
_ "github.com/mattn/go-oci8"
)
const (
username = "<username>"
password = "<password>"
connstring = "<hostname>:1521/<sid>"
)
func main() {
connectionStr := ""
if len(os.Args) > 1 {
connectionStr = os.Args[1]
} else {
connectionStr = fmt.Sprintf("%s/%s@%s", username, password, connstring)
}
db, err := sql.Open("oci8", connectionStr)
if err != nil {
log.Printf("ERROR: Cannot open [%s/XXXX@%s], %v\n", username, connstring, err)
return
}
defer db.Close()
if err := db.Ping(); err != nil {
log.Printf("ERROR: Cannot ping database, %v\n", err)
return
}
var sql string
scanner := bufio.NewScanner(os.Stdin)
for {
fmt.Print("SQL> ")
scanner.Scan()
text := scanner.Text()
if text == "" {
continue
} else if text == "q" || text == "quit" {
break
} else if text[len(text)-1:] == ";" {
sql = strings.TrimSpace(sql + " " + text[0:len(text)-1])
//fmt.Printf("SQL=[%s]\n", sql)
executeSQL(db, strings.TrimSpace(sql))
sql = ""
} else {
sql = sql + " " + text
}
}
}
func executeSQL(db *sql.DB, sql string) error {
command := ""
spaceIndex := strings.IndexByte(sql, ' ')
if spaceIndex > 0 {
command = strings.ToUpper(sql[0:spaceIndex])
} else {
command = strings.ToUpper(sql)
}
if strings.HasPrefix(command, "SELECT") {
return executeQuery(db, sql)
} else if strings.HasPrefix(command, "DESC") {
sql = "SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE" +
" FROM USER_TAB_COLUMNS" +
" WHERE TABLE_NAME = '"+ strings.ToUpper(sql[spaceIndex + 1:])+ "' ORDER BY COLUMN_ID"
return executeQuery(db, sql)
} else {
return executeUpdate(db, sql)
}
}
func executeUpdate(db *sql.DB, update string) error {
result, err := db.Exec(update)
if err != nil {
log.Printf("ERROR: Cannot execute update [%s], %v\n", update, err)
return err
}
rows, err := result.RowsAffected()
if err != nil {
log.Printf("ERROR: Cannot get rows affected, %v\n", err)
return err
}
fmt.Printf("%d row(s) affected\n", rows)
return nil
}
func executeQuery(db *sql.DB, query string) error {
rows, err := db.Query(query)
if err != nil {
log.Printf("ERROR: Cannot execute query [%s], %v\n", query, err)
return err
}
defer rows.Close()
queryResult, err := parseRows(rows)
if err != nil {
log.Printf("ERROR: Cannot parse query result, %v\n", err)
return err
}
queryResult.Print()
return nil
}
func parseRows(rows *sql.Rows) (*QueryResult, error) {
columns, err := rows.Columns()
if err != nil {
log.Printf("ERROR: Cannot get columns, %v\n", err)
return nil, err
}
columnCount := len(columns)
// handle column data
queryResult := &QueryResult{}
for c := 0; c < columnCount; c++ {
queryResult.ColumnResult = append(queryResult.ColumnResult, columns[c])
}
// handle row data
for rows.Next() {
rowRaw := make([]interface{}, columnCount)
rowString := make([]string, columnCount)
for c := 0; c < columnCount; c++ {
rowRaw[c] = new(sql.RawBytes)
}
err := rows.Scan(rowRaw...)
if err != nil {
log.Printf("ERROR: Cannot scan row, %v\n", err)
return nil, err
}
for c := 0; c < columnCount; c++ {
if fieldRaw, ok := rowRaw[c].(*sql.RawBytes); ok {
rowString[c] = string(*fieldRaw)
} else {
log.Printf("ERROR: Cannot convert index %d column %s to type *sql.RawBytes", c, columns[c])
return nil, err
}
}
queryResult.RowResult = append(queryResult.RowResult, rowString)
}
return queryResult, nil
}
type QueryResult struct {
ColumnResult []string
RowResult [][]string
}
func (result *QueryResult) Print() {
// calculate maximum column field length
columnLength := make([]int, len(result.ColumnResult))
for c := 0; c < len(result.ColumnResult); c++ {
columnLength[c] = len(result.ColumnResult[c])
for r := 0; r < len(result.RowResult); r++ {
if len(result.RowResult[r][c]) > columnLength[c] {
columnLength[c] = len(result.RowResult[r][c])
}
}
}
// print header
for c := 0; c < len(result.ColumnResult); c++ {
f := fmt.Sprintf("%%-%ds|", columnLength[c])
fmt.Printf(f, result.ColumnResult[c])
}
fmt.Println()
for c := 0; c < len(result.ColumnResult); c++ {
fmt.Printf("%s|", strings.Repeat("-", columnLength[c]))
}
fmt.Println()
// print data
for r := 0; r < len(result.RowResult); r++ {
for c := 0; c < len(result.ColumnResult); c++ {
f := fmt.Sprintf("%%-%ds", columnLength[c])
fmt.Printf(f+"|", result.RowResult[r][c])
}
fmt.Println()
}
}
运行结果：
SQL> create table testtab(a int, b char(10), c varchar2(100));
0 row(s) affected
SQL>
SQL> insert into testtab values(1, 'a', 'aaaaa');
1 row(s) affected
SQL>
SQL> insert into testtab values(2, 'bbbb', 'bbbbbbbb');
1 row(s) affected
SQL>
SQL> select a, c from testtab;
A|C |
-|--------|
1|aaaaa |
2|bbbbbbbb|
SQL> select * from testtab;
A|B |C |
-|----------|--------|
1|a |aaaaa |
2|bbbb |bbbbbbbb|
SQL>
SQL> desc testtab;
COLUMN_NAME|DATA_TYPE|DATA_LENGTH|DATA_PRECISION|DATA_SCALE|NULLABLE|
-----------|---------|-----------|--------------|----------|--------|
A |NUMBER |22 | |0 |Y |
B |CHAR |10 | | |Y |
C |VARCHAR2 |100 | | |Y |
SQL>
SQL>
SQL> drop table testtab;
0 row(s) affected
SQL> select * from testtab;
2021/05/28 09:42:07 ERROR: Cannot execute query [select * from testtab], ORA-00942: table or view does not exist