0

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

  • Unrelated, but: you should really avoid those dreaded quoted identifiers ("COL1" - they are much more trouble than they are worth it). – a_horse_with_no_name Feb 12 at 6:41
  • I agree, but I already made a leap on that one by making all of it case sensitive. :( – devilboy477 Feb 12 at 6:47
1

There is no reason to convert anything, just insert the JSON strings (after making them valid JSON):

CREATE OR REPLACE FUNCTION INS_BLOB() RETURNS VOID AS $$
BEGIN
 INSERT INTO "TBL1" ("SN","COL1","COL2") 
 values(1, 
    '{
      "APPLICATION": {
         "MEMORY": {
         "OPTIONS" :{
                      "SOMETHING" : "SOMETHING",
                      "format" : "SOMETHING",
                      "System" : "",
                      "IP" : "",
                      "Port" : "",
                      "template" : "",
                      "Path" : "" ,
                      "Name" : "QUEUE",
                      "URL" : ""              
        }}}}', --<< add the missing curly braces!
     '{
      "APPLICATION": {
       "MEMORY": {
       "OPTIONS" :{
                "SOMETHING" : "SOMETHING",
                "format" : "SOMETHING",
                "System" : "",
                "IP" : "",
                "Port" : "",
                "template" : "",
                "Path" : "" ,
                "Name" : "QUEUE",
                "URL" : ""  
        }}}}'); --<< add the missing curly braces!
END;
$$
LANGUAGE plpgsql;
COMMIT;

You also don't need PL/pgSQL for this. A language sql function would be enough (you would need to remove the begin and end though)

0

There is no reason to ever do that. PostgreSQL has a binary JSON type, jsonb. Just store your data as JSONB.

CREATE TABLE foo (
    jsondata  jsonb
);

INSERT INTO foo (jsondata) VALUES ( $${"foo": "bar"}$$ );

This will give you a ton of operators and functions that will natively work with this type. JSONB are varlena (just like bytea) under the hood anyway

See also,

  • 1
    Hi, I actually just inserted the values and it worked. But is there any way I can store it as a raw file in the records ? – devilboy477 Feb 12 at 6:46
  • 1
    @devilboy477: JSONB is a binary format (which compresses large values), why would you want anything else. What do you mean with "raw file in the record"? You are not using record variables there and I don't see any "files" either. – a_horse_with_no_name Feb 12 at 6:50
  • 1
    @devilboy477: no offense, but if you lack the knowledge, then why don't you accept the opinion of people that do have the knowledge. Using bytea for JSON values is the wrong thing to do. Period. – a_horse_with_no_name Feb 12 at 8:03
  • 1
    @devilboy477: Sorry, but I am not going to help you implement a completely wrong approach that will be a nightmare to maintain and that will perform poorly. – a_horse_with_no_name Feb 12 at 8:37
  • 2
    @a_horse_with_no_name I respect your ethics and shall ask no further. – devilboy477 Feb 12 at 9:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.