During the Golang development process, we sometimes encounter errors such as "ORA-00911: invalid character". This error is usually caused by using invalid characters in the SQL statement. To solve this problem, we can take some simple methods. First of all, before executing the SQL statement, we should carefully check whether there are special characters or invalid characters in the statement. Secondly, we can try to use quotes to enclose fields or values that contain special characters to avoid errors. In addition, you can also use escape characters to process special characters to ensure that they are correctly recognized and processed in SQL statements. Through these methods, we can effectively solve the "ORA-00911: invalid character" problem in Golang and ensure that our program can run normally.
I encountered the error "ORA-00911: invalid character" when calling the following function. If I use a SQL query with hardcoded values (as of now, it's commented out in the snippet below), then I can get the database records in a JSON response in Postman without any issues. So, it looks like I'm doing my argument wrong. FYI, I am using "github.com/sijms/go-ora/v2" package to connect to oracle db. Also, the "DashboardRecordsRequest" structure is in the data model package, but I've pasted it into the snippet below for reference. Please note that when I do the POC we will be using stored procedures to interact with Oracle.
Postman request payload:
<code>{
"username": "UserABC",
"startindex": 0,
"pagesize": 10,
"sortby": "requestnumber",
"sortorder": "DESC"
}
</code>Execution code:
<code>type DashboardRecordsRequest struct {
Username string `json:"username"`
StartIndex int `json:"startindex"`
PageSize int `json:"pagesize"`
SortBy string `json:"sortby"`
SortOrder string `json:"sortorder"`
}
func GetDashboardActiveRequestRecords(request datamodel.DashboardRecordsRequest) ([]datamodel.ActiveRequestRecord, error) {
sortby := request.SortBy
sortorder := request.SortOrder
startindex := request.StartIndex
pagesize := request.PageSize
activerecords := []datamodel.ActiveRequestRecord{}
slog.Info("Verify values", slog.String("sortby", sortby), slog.String("sortorder", sortorder), slog.Int("startindex", startindex), slog.Int("pagesize", pagesize))
dbconn, err := getDBConnection()
if err != nil {
logger.Error("Could not connect to database")
return activerecords, err
}
stmt, err := dbconn.Prepare("SELECT requestnumber, requeststatus, NVL(requestor, 'N/A'), NVL(pendingwith, 'N/A'), NVL(processtype, 'N/A'), actiondate FROM requests WHERE requeststatus = 'PENDINGAPPROVAL' ORDER BY ? ? OFFSET ? ROWS FETCH NEXT ? ROWS ONLY")
/*stmt, err := dbconn.Prepare("SELECT requestnumber, requeststatus, NVL(requestor, 'N/A'), NVL(pendingwith, 'N/A'), NVL(processtype, 'N/A'), actiondate FROM requests WHERE requeststatus = 'PENDINGAPPROVAL' ORDER BY requestnumber DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY")*/
if err != nil {
logger.Error("Error while building prepared statement for retrieving dashboard active records", slog.String("Error", err.Error()))
return activerecords, err
}
rows, err := stmt.Query(sortby, sortorder, startindex, pagesize)
//rows, err := stmt.Query()
if err != nil {
logger.Error("Error while executing prepared statement for retrieving dashboard active records", slog.String("Error", err.Error()))
return activerecords, err
}
defer rows.Close()
for rows.Next() {
var rec datamodel.ActiveRequestRecord
err = rows.Scan(&rec.RequestNumber, &rec.RequestStatus, &rec.RequestorName, &rec.PendingWith, &rec.ProcessType, &rec.LastActionDate)
if err != nil {
logger.Error("Error while processing database resultset for dashboard active records", slog.String("Error", err.Error()))
return activerecords, err
}
activerecords = append(activerecords, rec)
}
return activerecords, err
}
</code>Request table structure:
<code>CREATE TABLE "REQUESTS" (
"REQUESTNUMBER" VARCHAR2(64 CHAR) NOT NULL ENABLE,
"REQUESTSTATUS" VARCHAR2(128 CHAR) NOT NULL ENABLE,
"SUBMISSIONDATE" TIMESTAMP(6),
"PROCESSTYPE" VARCHAR2(256 CHAR),
"SUBMITTER" VARCHAR2(256 CHAR) NOT NULL ENABLE,
"REQUESTOR" VARCHAR2(512 CHAR),
"PENDINGWITH" VARCHAR2(512 CHAR),
"ACTIONDATE" TIMESTAMP(6),
"RESUBMISSIONDATE" TIMESTAMP(6),
PRIMARY KEY ( "REQUESTNUMBER" ),
FOREIGN KEY ( "SUBMITTER" )
REFERENCES "SUBMITTERS" ( "USERNAME" )
)
</code>mistake:
time=2023-10-04T06:43:06.304Z level=INFO source=C:/code/tutorials/myapp/internal/storage/dashboard.go:19 msg="Verify values" sortby=requestnumber sortorder=DESC startindex=0 pagesize=10 time=2023-10-04T06:43:06.603Z level=ERROR source=C:/code/tutorials/myapp/internal/storage/dashboard.go:34 msg="Error while executing prepared statement for retrieving dashboard active records" Error="ORA-00911: invalid character\n"
The immediate problem is that you are using the JDBC-style ? binding placeholder instead of the expected :var form. From the documentation of the go-ora package, you say you are using: < /p>
So your stmt should be:
SELECT requestnumber, requeststatus, NVL(requestor, 'N/A'), NVL(pendingwith, 'N/A'), NVL(processtype, 'N/A'), actiondate FROM requests WHERE requeststatus = 'PENDINGAPPROVAL' ORDER BY :sortby :sortorder OFFSET :startindex ROWS FETCH NEXT :pagesize ROWS ONLY
But you can't bind anything other than a variable, so it won't let you have sortorder as a variable at all, if you just remove it and do:
ORDER BY :sortby OFFSET :startindex ROWS FETCH NEXT :pagesize ROWS ONLY
This seems to work, but even this doesn't do exactly what you want because the sort will sort by the literal column name rather than its value; so it will work as ORDER BY 'requestnumber'## The equivalent of # runs instead of ORDER BY requestnumber. And sorting by that constant string won't accomplish anything.
"... ORDER BY " + sortby + " " + sortorder + " OFFSET :startindex ROWS FETCH NEXT :pagesize ROWS ONLY"
The above is the detailed content of How to resolve error 'ORA-00911: invalid character' in Golang?. For more information, please follow other related articles on the PHP Chinese website!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
