Storing JSON Objects in BLOBS in Oracle

Introduction

Sometimes, you'll want to store an entire JSON object in the database for history purposes.

Background

What Kind of Data Can I Store?

CLOB – NORMALLY TEXT

BLOB - Video, Audio, PDF, JSON… (Just convert it to byte[]) - I’m going to convert my JSON to a string and store it in a blob.

BLOB vs CLOB?

From what I’ve read, if you have reason to believe you’ll need to view the data in SQL plus, you’ll want to use CLOB.

When storing text, CLOB is bigger than BLOB because each character is stored as two bytes (to support different character sets).

I did not research how to insert CLOBs, because I want to store a large JSON obj, and don’t want to use up twice the space. I also do not have any reason for someone to view the column in SQL developer.

Using the Code

Storing in a Blob

-- If its Text and it is under 2000 chars, you can use the following statement : 

insert into ORDERS.ORDER_HISTORY (ORDERNUM, CREATE_USER, JSON_VALUE) _
Values(5,'BLOB',utl_raw.cast_to_raw('a bunch of test with under a MAX 2000 char buffer.......'));
commit;

select Cust_PK, SUBMISSION_DATE, CUSTNUM, CREATE_USER, _
utl_raw.cast_to_varchar2(JSON_VALUE) from ORDERS.ORDER_HISTORY where ORDERNUM  = 5;

For files or items larger than 2,000 chars, you’ll need to create a web service and convert the data to a byte array using C#.

You’ll also need to bind the data in your INSERT statement.

Insertion

// In this code, I've converted the JSON object to a string,
// then to a byte array before insertion

public string AddNewOrderHist(int ORDERNUM, string orderStr)
{
    string SQLString = "";

    // Convert to byte[]
    byte[] toBytes = Encoding.ASCII.GetBytes(orderStr);

    // Notice bound variable
    SQLString = "insert into ORDERS.ORDER_HISTORY " +
                                 " (ORDERNUM, CREATE_USER, JSON_VALUE) " +
                                   " Values(" + ORDERNUM + ",'" + me.USRID + "',:1) ";

    using (var connection = new OracleConnection())
    {
        OracleCommand command = connection.CreateCommand();
        try
        {
            connection.ConnectionString = gblSYSConnStr;
            connection.Open();
            command.CommandText = SQLString;
            // add Parameter
            OracleParameter param = command.Parameters.Add("blobtodb", OracleDbType.Blob);
            param.Direction = ParameterDirection.Input;
            param.Value = toBytes;
            command.ExecuteNonQuery();
            connection.Close();
            return "SUCCESS";
        }
        catch (Exception e)
        {
            return "ERROR";
        }
    }
}

Reading Blob Data

(You need to know what you are storing in the BLOB column so that you can accurately convert it back from BYTE data.)

Retrieval

//Return the History Blob
// In this code the SQL has to be as follows to get the Blob Back
public string GetORDERHistRec(int apk)
{
    string SQLString = "";
    SQLString = "BEGIN select JSON_VALUE into :tempblob from ORDERS.ORDER_HISTORY " +
                         " where ORDERNUM = " + apk + "; END; ";
                         
    using (var connection = new OracleConnection())
    {
        OracleCommand command = connection.CreateCommand();
        try
        {
            connection.ConnectionString = gblSYSConnStr;
            connection.Open();
            command.Parameters.Add
                 (":tempblob", OracleDbType.Blob).Direction = ParameterDirection.Output;
            command.CommandText = SQLString;
            command.ExecuteNonQuery();
            
            // Create a byte array
            byte[] byteData = new byte[0];
            
            // fetch the value of Oracle parameter into the byte array
            byteData = (byte[])((OracleBlob)(command.Parameters[0].Value)).Value;
            
            string orderStr = Encoding.ASCII.GetString(byteData);
            connection.Close();
            return orderStr;
        }
        catch (Exception e)
        {
           return "ERROR";
        }
    }
} // End Get History Blob