Oracle JSON type. Can we determine this type?

[viplifes]

Hello! Thank you for the wonderful go-ora library.

We want to migrate our project from godror to go-ora, but we are having difficulty working with the JSON data type.

Oracle supports the JSON data type: https://cx-oracle.readthedocs.io/en/latest/user_guide/json_data_type.html

CREATE TABLE json_test("myjson" JSON);
INSERT INTO json_test ("myjson") VALUES (json('{"foo": "bar"}'));
SELECT myjson FROM json_test;

func newRowOracleTest(columnTypes []*sql.ColumnType) {
    for _, ct := range columnTypes {
        // godror print - JSON
        // go-ora print - OCIBlobLocator
        fmt.Println(ct.DatabaseTypeName())
    }
}

Can we somehow understand the type of a JSON field using go-ora?

Perhaps there is a way to retrieve the native OracleType? https://github.com/sijms/go-ora/blob/09e66f57e7c518239a126347a6e45ea40aede73a/parameter.go#L11

I found the part of the godror code where they define the JSON type: https://github.com/godror/godror/blob/d74c360907171a3c201d75a1d2bec80a9c7868b2/rows.go#L640

[sijms]

sorry for late I will test the data type and its network representation and update the package

[sijms]

I read about this type it is actually stored as a blob. its support started from oracle 21c so you can pass go_ora.Blob parameter older version of oracle will show its blob origin

create table customers (
    id integer not null primary key,
    json_data blob check (json_data is json)
);

[viplifes]

Thank you for your attention to this issue.

Unfortunately, our service cannot know the type of the returned field.

We look at DatabaseTypeName() to understand that the field type is json (not original BLOB) and convert json.Unmarshal to a map.

Here is the code that shows that in godror it was possible to understand that the field type is JSON.

package main

import (
    "database/sql"
    "fmt"

    _ "github.com/godror/godror"
    go_ora "github.com/sijms/go-ora/v2"
)

func main() {

    connOra, err := sql.Open("oracle", go_ora.BuildUrl("127.0.0.1", 1521, "ORCLPDB1", "SYS", "pass", nil))
    if err != nil {
        panic(err)
    }
    defer connOra.Close()
    run(connOra)
    // print
    // OCIBlobLocator OCIBlobLocator

    connGodror, err := sql.Open("godror", `user="SYS" sysdba="1" password="pass" connectString="127.0.0.1:1521/ORCLPDB1"`)
    if err != nil {
        panic(err)
    }
    defer connGodror.Close()
    run(connGodror)
    // print
    // BLOB JSON
}

func run(conn *sql.DB) {
    conn.Exec(`GRANT CREATE SESSION TO SYS`)
    conn.Exec(`DROP TABLE IF EXISTS types_test`)
    conn.Exec(`CREATE TABLE types_test (myblob BLOB NULL, myjson JSON NULL)`)
    conn.Exec(`INSERT INTO types_test (myblob, myjson)VALUES(UTL_RAW.CAST_TO_RAW('blobval - not json'), json('{"foo": "bar"}'))`)

    rows, err := conn.Query(`SELECT * FROM types_test`)
    if err != nil {
        panic(err)
    }
    cTypes, _ := rows.ColumnTypes()
    fmt.Println(cTypes[0].DatabaseTypeName(), cTypes[1].DatabaseTypeName())
}

[cjbj]

I read about this type it is actually stored as a blob.

Prior to 21c, JSON could be stored as VARCHAR, CLOB, or BLOB types by specifying the AS JSON clause at table creation. At some stage an extra attribute was added so that client apps could differentiate from other VARCHAR, CLOB or BLOBS columns. We only just found this out and added support in eg python-oracledb, see the release notes and ODPI-C commit.

From 21c there is an explicit JSON datatype that can/should be used in preference to the above, older table creation syntax.

[sijms]

sorry for late. these days i am so busy but soon i will fix this issue

[sijms]

till now I check the columns attribs for Blob and Blob with json constraint and I find no difference

&go_ora.ParameterInfo{Name:"JSON_VAL", TypeName:"", Direction:0, IsNull:false, AllowNull:true, ColAlias:"", DataType:113, IsXmlType:false, Flag:0x0, Precision:0x0, Scale:0x0, MaxLen:4000, MaxCharLen:0, MaxNoOfArrayElements:0, ContFlag:0, ToID:[]uint8(nil), Version:0, CharsetID:0, CharsetForm:0, BValue:[]uint8(nil), Value:driver.Value(nil), iPrimValue:driver.Value(nil), oPrimValue:driver.Value(nil), OutputVarPtr:interface {}(nil), getDataFromServer:true, oaccollid:0, cusType:(*go_ora.customType)(nil)}
&go_ora.ParameterInfo{Name:"BLOB_VAL", TypeName:"", Direction:0, IsNull:false, AllowNull:true, ColAlias:"", DataType:113, IsXmlType:false, Flag:0x0, Precision:0x0, Scale:0x0, MaxLen:4000, MaxCharLen:0, MaxNoOfArrayElements:0, ContFlag:0, ToID:[]uint8(nil), Version:0, CharsetID:0, CharsetForm:0, BValue:[]uint8(nil), Value:driver.Value(nil), iPrimValue:driver.Value(nil), oPrimValue:driver.Value(nil), OutputVarPtr:interface {}(nil), getDataFromServer:true, oaccollid:0, cusType:(*go_ora.customType)(nil)}

the information about json column is store in table all_constraints which can be queried for json constraint.

May be there are network calls that return more advanced information about the columns where we can find the difference

[sijms]

I will test the code with oracle server 21c and see the difference

[sijms]

I find the solution i will add it to next release thanks to oracle-db python driver

&go_ora.ParameterInfo{Name:"JSON_VAL", TypeName:"", SchemaName:"", DomainSchema:"", DomainName:"", Direction:0, IsNull:false, AllowNull:true, IsJson:true, ColAlias:"", DataType:113, IsXmlType:false, Flag:0x0, Precision:0x0, Scale:0x0, MaxLen:4000, MaxCharLen:0, MaxNoOfArrayElements:0, ContFlag:0, ToID:[]uint8(nil), Version:0, CharsetID:0, CharsetForm:0, BValue:[]uint8(nil), Value:driver.Value(nil), iPrimValue:driver.Value(nil), oPrimValue:driver.Value(nil), OutputVarPtr:interface {}(nil), getDataFromServer:true, oaccollid:0, cusType:(*go_ora.customType)(nil), Annotations:map[string]string(nil)}
&go_ora.ParameterInfo{Name:"BLOB_VAL", TypeName:"", SchemaName:"", DomainSchema:"", DomainName:"", Direction:0, IsNull:false, AllowNull:true, IsJson:false, ColAlias:"", DataType:113, IsXmlType:false, Flag:0x0, Precision:0x0, Scale:0x0, MaxLen:4000, MaxCharLen:0, MaxNoOfArrayElements:0, ContFlag:0, ToID:[]uint8(nil), Version:0, CharsetID:0, CharsetForm:0, BValue:[]uint8(nil), Value:driver.Value(nil), iPrimValue:driver.Value(nil), oPrimValue:driver.Value(nil), OutputVarPtr:interface {}(nil), getDataFromServer:true, oaccollid:0, cusType:(*go_ora.customType)(nil), Annotations:map[string]string(nil)}

[sijms]

fixed in v2.7.21 you can test json column with field ParameterInfo.IsJson