So, I came across the function script which is converting JSON data to BYTEA and then insert as a record in the table in a BYTEA column. (As I assumed what the code is doing)
In Oracle the function utl_raw.cast_to_raw converts the data to blob data and records the data in the table in Blob column. Giving the following output message, "anonymous block completed"
The following is the code,
CREATE OR REPLACE FUNCTION INS_BLOB() RETURNS VOID AS $$
DECLARE
v1 "TBL1"."COL1"%TYPE;
v2 "TBL1"."COL2"%TYPE;
BEGIN
v1 := utl_raw.cast_to_raw('{
"APPLICATION": {
"MEMORY": {
"OPTIONS" :{
"SOMETHING" : "SOMETHING",
"format" : "SOMETHING",
"System" : "",
"IP" : "",
"Port" : "",
"template" : "",
"Path" : "" ,
"Name" : "QUEUE",
"URL" : ""
}');
v2 := utl_raw.cast_to_raw('{
"APPLICATION": {
"MEMORY": {
"OPTIONS" :{
"SOMETHING" : "SOMETHING",
"format" : "SOMETHING",
"System" : "",
"IP" : "",
"Port" : "",
"template" : "",
"Path" : "" ,
"Name" : "QUEUE",
"URL" : ""
}');
INSERT INTO "TBL1" ("SN","COL1","COL2") values(1,v1, v2);
END;
$$
LANGUAGE 'plpgsql';
COMMIT;
[SOLVED] Edit: So generally the problem was nothing. So all I had to remove was the utl_raw.cast_to_raw line and execute it as it is. No errors, no problems. THANKS
Best Answer
There is no reason to convert anything, just insert the JSON strings (after making them valid JSON):
You also don't need PL/pgSQL for this. A
language sql
function would be enough (you would need to remove thebegin
andend
though)