Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
go-oci8 test case that show it doing fetch for each row if query as a clob in the query
/*
All these OS environment variables to specify how to connect.
* DB_USERNAME
* DB_PASSWORD
* DB_PORT
* DB_HOSTNAME
* DB_SERVICENAME
* DB_PREFETCHMEMORY
* DB_PREFETCHROWS
DB Privileges need
* create session
* create table
* select on v$session
* select on v§sql
* And some quota on default tablespace for the ues
*/
package main
import (
"context"
"database/sql"
"fmt"
"log"
"os"
"time"
_ "github.com/mattn/go-oci8"
)
const (
tblQuery = `
CREATE TABLE PREFETH_ROWS_CLOB_TEST (
C1 INTEGER,
C2 varchar2(300),
C3 CLOB
)
`
dropTblQuery = `
DROP TABLE PREFETH_ROWS_CLOB_TEST
`
loadTestData = `
BEGIN
for rec in 1..1000
loop
insert into PREFETH_ROWS_CLOB_TEST values(rec,rpad(' ',255,'X'),rpad(' ',3000,'X'));
end loop;
end;
`
queryClob = `
SELECT /*clobQuery*/
C1
,C2
,C3
FROM PREFETH_ROWS_CLOB_TEST
`
querySQLStats = `
select
fetches,
rows_processed
from v$sql where sql_id = :1
`
queryGetPrevSQLID = `
SELECT
prev_sql_id
FROM v$session s
WHERE username = 'SYSTEM'
AND s.module = 'PREFETCHROWS_TEST'
`
contextTimeout = 10 * time.Second
)
func main() {
var dbUsername, dbPassword, dbHostname, dbPort, dbServiceName, prefetchMemory, prefetchRows string
if dbUsername = os.Getenv("DB_USERNAME"); dbUsername == "" {
log.Printf("Set OS environment variable DB_USERNAME\n")
}
if dbPassword = os.Getenv("DB_PASSWORD"); dbPassword == "" {
log.Printf("Set OS environment variable DB_PASSWORD\n")
}
if dbHostname = os.Getenv("DB_HOSTNAME"); dbHostname == "" {
log.Printf("Set OS environment variable DB_HOSTNAME\n")
}
if dbPort = os.Getenv("DB_PORT"); dbPort == "" {
log.Printf("Set OS environment variable DB_USERNAME\n")
}
if dbServiceName = os.Getenv("DB_SERVICENAME"); dbServiceName == "" {
log.Printf("Set OS environment variable DB_SERVICENAME\n")
}
if prefetchMemory = os.Getenv("DB_PREFETCHMEMORY"); prefetchMemory == "" {
log.Printf("Set OS environment variable DB_PREFETCHMEMORY\n")
}
if prefetchRows = os.Getenv("DB_PREFETCHROWS"); prefetchRows == "" {
log.Printf("Set OS environment variable DB_PREFETCHROWS\n")
}
if dbUsername == "" || dbPassword == "" || dbHostname == "" || dbPort == "" || dbServiceName == "" || prefetchMemory == "" || prefetchRows == "" {
log.Fatalf("All these OS environment variables DB_USERNAME, DB_PASSWORD, DB_PORT, DB_HOSTNAME, DB_SERVICENAME, DB_PREFETCHMEMORY DB_PREFETCHROWS must be set")
}
conn := fmt.Sprintf("%s/%s@%s:%s/%s?prefetch_rows=%s&prefetch_memory=%s", dbUsername, dbPassword, dbHostname, dbPort, dbServiceName, prefetchRows, prefetchMemory)
log.Printf("conn: %s\n", conn)
db, err := sql.Open("oci8", conn)
if err != nil {
log.Fatalf("db connect failed with error: %v\n", err)
}
err = db.Ping()
if err != nil {
log.Fatalf("db ping failed with error: %v\n", err)
}
adminConn, err := sql.Open("oci8", conn)
if err != nil {
log.Fatalf("db connect failed with error: %v\n", err)
}
err = db.Ping()
if err != nil {
log.Fatalf("db ping failed with error: %v\n", err)
}
db.Exec(`
begin
DBMS_APPLICATION_INFO.SET_MODULE (
module_name => 'PREFETCHROWS_TEST',
action_name => 'SETUP');
end;
`)
//Setup test table
ctx, cancel := context.WithTimeout(context.Background(), contextTimeout)
stmt, err := db.PrepareContext(ctx, tblQuery)
cancel()
ctx, cancel = context.WithTimeout(context.Background(), contextTimeout)
_, err = stmt.ExecContext(ctx)
cancel()
if err != nil {
log.Fatalf("err:%v\n", err)
}
db.Exec(`
begin
DBMS_APPLICATION_INFO.SET_MODULE (
module_name => 'PREFETCHROWS_TEST',
action_name => 'LOAD_DATA');
end;
`)
//Load data into test table
ctx, cancel = context.WithTimeout(context.Background(), contextTimeout)
stmt, err = db.PrepareContext(ctx, loadTestData)
ctx, cancel = context.WithTimeout(context.Background(), contextTimeout)
_, err = stmt.ExecContext(ctx)
cancel()
if err != nil {
log.Fatalf("err:%v\n", err)
}
db.Exec(`
begin
DBMS_APPLICATION_INFO.SET_MODULE (
module_name => 'PREFETCHROWS_TEST',
action_name => 'CLOB_QUERY');
end;
`)
// Execute test with one query
var rows *sql.Rows
ctx, cancel = context.WithTimeout(context.Background(), contextTimeout)
defer cancel()
stmt, err = db.PrepareContext(ctx, queryClob)
rows, err = stmt.QueryContext(ctx)
if err != nil {
log.Fatal(err)
}
log.Printf("Start looping result\n")
defer rows.Close()
start := time.Now()
for rows.Next() {
var data1 int64
rows.Scan(
&data1,
)
}
elapsed := time.Since(start)
log.Printf("Query with clob took %s", elapsed)
var sqlID string
adminConn.QueryRow(queryGetPrevSQLID).Scan(&sqlID)
var fetches, rowsProcessed int64
adminConn.QueryRow(querySQLStats, sqlID).Scan(&fetches, &rowsProcessed)
log.Printf("Query with clob SQLID: %s, fetches:%d , rowsProcessed: %d \n", sqlID, fetches, rowsProcessed)
//Clean up
ctx, cancel = context.WithTimeout(context.Background(), contextTimeout)
stmt, err = db.PrepareContext(ctx, dropTblQuery)
ctx, cancel = context.WithTimeout(context.Background(), contextTimeout)
_, err = stmt.ExecContext(ctx)
cancel()
if err != nil {
log.Fatalf("err:%v\n", err)
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment