In this post I will demonstrate how to call Oracle Stored Procedure which has an out parameter from Go using the Go DRiver for ORacle (godror) 

The code in this post is up on GitHub and was developed using Go 1.20.7 & VS Code running on Windows 10. I used a local version of the Oracle Database version 18. You will also need to set up godror please take a look at an earlier post which described how to do this.

The Oracle Stored Procedure

This is the Procedure that will be used during this post. It is named p and has two parameters. The first p_lowercase has a parameter mode of IN which indicates that this passes a value to the Procedure. The second parameter po_uppercase has parameter mode of OUT which means this returns a value to the invoker. This link will be of interest if you are interested in finding out more about parameter modes.

The procedure returns the value passed to it in uppercase, so if the procedure was called and the p_lowercase parameter was given the value of hello the po_uppercase will be assigned the value HELLO

CREATE OR REPLACE PROCEDURE p (p_lowercase  IN  VARCHAR2, 
                               po_uppercase OUT VARCHAR2)
IS
BEGIN
  po_uppercase := UPPER(p_lowercase);   
END p;

The Go Code

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"

	_ "github.com/godror/godror"
)

func main() {

	db, err := sql.Open("godror", "godror", `user="hr" password="your_password" connectString="LocationOfOracleDBserver:1521/XEPDB1"`)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	ctx := context.Background()

	const oracleAnonBlock = "BEGIN p(:1, :2); END;"

	stmt, err := db.PrepareContext(ctx, oracleAnonBlock)
	if err != nil {
		log.Fatal(err)
	}
	defer stmt.Close()

	inParam := "hello"
	var outParam string

	stmt.ExecContext(ctx, inParam, sql.Out{Dest: &outParam})

	fmt.Println("Out parameter is", outParam)

}

After making a successful connection to the database, an empty context is created and stored in the ctx variable. For this example an empty context is fine but if you need to find out more about the purpose of context this blog post from the Go Team is a good place to start. Next a constant named oracleAnonBlock is created and assigned the following

"BEGIN p(:1, :2); END;" 

This is an Oracle PL/SQL anonymous block which will be used sent to the database and executed. It contains one statement which is the call to the procedure p along with placeholders for the parameters which will be added when the statement is executed.

The next part of the function is to prepare the statement for execution by the call to PrepareContext. Once this completes successfully, the two parameters are defined. The inParam is given a value of hello and the outParam is defined and is used to store the value returned by the Procedure. The last step is to print out the value returned by the Procedure.

Run the code using the following command:

go run main.go

and the output will be:

Out parameter is HELLO

ACKNOWLEDGEMENTS

The Go DRiver for ORacle (godror) project.