Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1006
  • Last Modified:

JSON to Oracle 12c

I have a json feed, that i would like to import into my oracle DB.

Is there a way of doing this natively with oracle 12c?

Thanks
0
FutureDBA-
Asked:
FutureDBA-
  • 4
  • 2
1 Solution
 
sdstuberCommented:
There is no direct json to table import method.

However, if you can convert to xml, you can use oracle's XDB functionality to read and parse it.
0
 
FutureDBA-Author Commented:
i can get the data in xml as well
0
 
FutureDBA-Author Commented:
can XDB get the xml feed from a url ?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
sdstuberCommented:
yes, simplest form would be do something like...


select httpuritype('http://some.domain.com/interesting_file.xml').getxml() from dual

which would read the file into an xmltype column

if you need authentication, or schema validation or service interaction then you'll need to use some of the packages to negotiate the communication.
0
 
FutureDBA-Author Commented:
sdstuber, yeah. before i read this i had just gotten there.

I do need to authenticate with the server.

When i used the above method. I got

ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1817
ORA-29024: Certificate validation failure
ORA-06512: at "SYS.HTTPURITYPE", line 41
29273. 00000 -  "HTTP request failed"
*Cause:    The UTL_HTTP package failed to execute the HTTP request.
*Action:   Use get_detailed_sqlerrm to check the detailed error message.
           Fix the error and retry the HTTP request.

Open in new window


Can you point me in the right direction? thanks as always
0
 
slightwv (䄆 Netminder) Commented:
I can't help with the authentication piece.

As far as parsing a JSON format into Oracle, I posted this a while back.  It's not pretty but it worked in that case:
http://www.experts-exchange.com/Database/Oracle/Q_28241626.html#a39499591
0
 
FutureDBA-Author Commented:
both feeds json or xml would require authentication, i am looking to oracle wallet manager now to get certificates installed.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now