Storing JSON Objects in BLOBS in Oracle
Storing Data 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