0

I want to have PL/SQL in Oracle for below statement I have 2 tables in oracle DB 

    <table>
      <tr>
        <th>Value1</th>
        <th>value2</th>
        <th>Value3</th>
      </tr>
      <tr>
        <td>xxx</td>
        <td>ppp</td>
        <td>hello</td>
      </tr>
      <tr>
        <td>yyy</td>
        <td>qqq</td>
        <td>hello1</td>
      </tr></table>

table2

 <tr>
    <th>Id</th>
    <th>Msg</th>
   
  </tr>
  <tr>
    <td>1</td>
    <td>  {
    "messages": [
        {
            "source": "abc",
            "messageType": "pqr"
        },
        {
            "source": "abc",
            "messageType": "pqr"
        }
    ],
    "value1": "xxx",
    "value2": "ppp"
}
</td>
    
  </tr>

I should get value "hello" or "hello1" based on value1 or value2 from table2

ND1125
  • 31
  • 2
  • 7
  • 1
    possible duplicate of [Parse JSON into Oracle table using PL/SQL](http://stackoverflow.com/questions/27122612/parse-json-into-oracle-table-using-pl-sql) – GolezTrol Aug 28 '15 at 20:02
  • 1
    You'll need a json parser (or parse the string on your own, which is far from easy). Even better: don't story this json at all, or at least store the information you need in the query also in a different format, that is, in separate tables and columns so you can actually use Oracle as it is intended to. Even if you get reading the Json working, it will become slow with larger amounts of data. If you just want to store and search Json documents, use a different data store like MongoDB. – GolezTrol Aug 28 '15 at 20:04
  • 1
    Which version of Oracle are you using? Only the latest version of 12c R1 comes with native JSON support. – APC Aug 29 '15 at 07:56
  • 1
    @GolezTrol - provided the ratio of data in JSON vs data in relational tables is mainly weighted towards structured, it will probably be better to leverage Oracle's unstructured data capabilities rather than maintaining data in two different data stores. Let's assume the technology choice is appropriate to the use case. – APC Aug 29 '15 at 08:00
  • @APC: Yes I know 12c has that feature. UNfortunately I am dealing with 11g. Could you please help here? – ND1125 Sep 01 '15 at 17:44

0 Answers0