Hello,
I want my code function to convert the JSON data to BYTEA and then inserts into the table. 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;
Hi neiljo10,
Could you please remove the utl_raw.cast_to_raw function and execute the below function, we have tested the same and it works in our local environment.
Please find the attached function for your reference.
edb=# CREATE OR REPLACE FUNCTION INS_BLOB() RETURNS VOID AS $$
edb$# BEGIN
edb$# INSERT INTO "TBL1" ("SN","COL1","COL2")
edb$# values(1,
edb$# '{
edb$# "APPLICATION": {
edb$# "MEMORY": {
edb$# "OPTIONS" :{
edb$# "SOMETHING" : "SOMETHING",
edb$# "format" : "SOMETHING",
edb$# "System" : "",
edb$# "IP" : "",
edb$# "Port" : "",
edb$# "template" : "",
edb$# "Path" : "" ,
edb$# "Name" : "QUEUE",
edb$# "URL" : ""
edb$# }}}}',
edb$# '{
edb$# "APPLICATION": {
edb$# "MEMORY": {
edb$# "OPTIONS" :{
edb$# "SOMETHING" : "SOMETHING",
edb$# "format" : "SOMETHING",
edb$# "System" : "",
edb$# "IP" : "",
edb$# "Port" : "",
edb$# "template" : "",
edb$# "Path" : "" ,
edb$# "Name" : "QUEUE",
edb$# "URL" : ""
edb$# }}}}');
edb$# END;
edb$# $$
edb-# LANGUAGE plpgsql;
CREATE FUNCTION
edb=# COMMIT;
COMMIT
Hi neiljo10,
Please provide us with below-required information.
1. Which EPAS version you are using.
2. Is the function code you have pasted executing successfully, if not can you paste an error you are facing.
3. Table structure in which trying to insert data after manipulation.
Hi neiljo10,
Could you please remove the utl_raw.cast_to_raw function and execute the below function, we have tested the same and it works in our local environment.
Please find the attached function for your reference.
edb=# CREATE OR REPLACE FUNCTION INS_BLOB() RETURNS VOID AS $$
edb$# BEGIN
edb$# INSERT INTO "TBL1" ("SN","COL1","COL2")
edb$# values(1,
edb$# '{
edb$# "APPLICATION": {
edb$# "MEMORY": {
edb$# "OPTIONS" :{
edb$# "SOMETHING" : "SOMETHING",
edb$# "format" : "SOMETHING",
edb$# "System" : "",
edb$# "IP" : "",
edb$# "Port" : "",
edb$# "template" : "",
edb$# "Path" : "" ,
edb$# "Name" : "QUEUE",
edb$# "URL" : ""
edb$# }}}}',
edb$# '{
edb$# "APPLICATION": {
edb$# "MEMORY": {
edb$# "OPTIONS" :{
edb$# "SOMETHING" : "SOMETHING",
edb$# "format" : "SOMETHING",
edb$# "System" : "",
edb$# "IP" : "",
edb$# "Port" : "",
edb$# "template" : "",
edb$# "Path" : "" ,
edb$# "Name" : "QUEUE",
edb$# "URL" : ""
edb$# }}}}');
edb$# END;
edb$# $$
edb-# LANGUAGE plpgsql;
CREATE FUNCTION
edb=# COMMIT;
COMMIT
Hi neiljo10,
Bytea data type is accepting data to get stored means it is bytea type data otherwise it would have through error while storing data in bytea type column.
You will see the data stored in bytea column which is in binary format.