Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to combine two JSON array list into single array list

Kannan SekarMar 2 2022

We are using Oracle 19C version. we have json like below,
{ "tktKey": "123456", "attlist1": [ { "attkey": "123", "attval": "abc" }, { "attkey": "456","attval": "def" } ], "attlist2": [{"attkey": "098", "attval": "xyz" } ]}
form above json i need to combine attlist1 and attlist2 and make it to single attlist.
The Excepted output should be like below,
{ "tktKey": "123456", "attlist": [ { "attkey": "123", "attval": "abc" }, { "attkey": "456", "attval": "def" }, { "attkey": "098", "attval": "xyz" } ]}

Comments

Saubhik

This thread may help you.

PhilMan2

Hi,

I looked at this thread and its links.  It doesn't help, unfortunately.  I'm still looking for an answer of how to send an embedded image in the body of an html eMail using UTL_SMTP.

Has anyone had any experience with this?

Phil

Stako Botev

Hi,

Have you considered that this could be due to your e-mail client?

I just tried your sample message body with the embedded image in it - I sent it through Oracle UTL_SMTP and received it successfully on my local e-mail client.

The image was fine and displayed as normal picture. However when I sent the same email on my gmail account, the picture was not shown.

So this is most probably due to configuration of the mail client.

Cheerz,

Stako

PhilMan2

Hi 2772777.  Thanks for looking at this.  I really appreciate it.

I get similar results when I switch to my hotmail account.  I retrieve the eMail using Outlook 2013 as well as through a FIrefox browser to outlook.com.  All attempts do NOT show the image.  I'm wondering if it's the way I'm calling UTL_SMTP.

The code that actually sends html body of the eMail is:

----------------------------------------------------
-- Send the email body in 1900 byte chunks to UTL_SMTP
l_offset  := 1;
l_ammount := 1900;
utl_smtp.open_data(l_connection);

while l_offset < dbms_lob.getlength(l_body_html) loop
    utl_smtp.write_data(l_connection,
                        dbms_lob.substr(l_body_html,l_ammount,l_offset));
    l_offset  := l_offset + l_ammount ;
    l_ammount := least(1900,dbms_lob.getlength(l_body_html) - l_ammount);
end loop;
--The following crlf is necessary after the body is written
utl_smtp.write_data(l_connection, crlf);
----------------------------------------------------

Any ideas?  Again, thanks for your help.

Phil

Billy Verreynne

If you create a valid MIME body as per RFC specifications, it will render correctly in the vast majority (if not all), of e-mail clients.

PhilMan2

Hi Billy.  Thanks for replying.  I looked at the link you provided.  I'm trying to figure out where the body I'm sending is not a valid MIME body.  Is there some sort of validation site that can tell me what's improper about my current message?

Thanks for your help.

Phil

Billy Verreynne

No. Not that I'm aware of.

When creating e-mail bodies, I prefer to letting an e-mail client do it for me. I will use my e-mail client, create the type of e-mail I want, and then mail it to myself. I'll then view the incoming e-mail in its raw format and use that as a template for my code.

Just keep in mind that not all mail clients and mail servers are equal. MS Exchange for example for some idiotic reason rips the raw e-mail apart and rewrites the entire thing - to the extent of replacing your boundary tags with its boundary tags, and base64 encoding plain/text MIME areas. So do not expect your e-mail to arrive in the actual format that you send it. Which is why it is best to ensure your e-mail is as vanilla MIME as possible. 

1 - 7

Post Details

Added on Mar 2 2022
#plsql, #sql, #sqldeveloper
11 comments
6,082 views