This is just to give you a flavor of how…

Question

1
1

Asked: October 2, 20202020-10-02T04:37:52+00:00 2020-10-02T04:37:52+00:00

How to generate table from a json object stored in item varchar2

1
1

In an IG, I select records (row selector) and save them in a text item; there are several records chosen. How can I process the said item (varchar2) in PL/SQL to insert the records in an oracle table?

Report

Leave an answer

Leave an answer
Cancel reply

4 Answers

score 1 · Answer 1 · 2020-10-02T07:30:02+00:00

2020-10-02T07:30:02+00:00Added an answer on October 2, 2020 at 7:30 am

Thank you very much for the quick answer

score 0 · Answer 2 · 2020-10-02T06:28:42+00:00

2020-10-02T06:28:42+00:00Added an answer on October 2, 2020 at 6:28 am

In which format you saved on the selection of the IG?

If you have just used a field value separator like a comma or colon, you can query as following:

Select
  t.column_value As cids
From
  Table ( apex_string.split(:p1_selitems, ',') ) t
Where
  t.column_value Is Not Null;

2020-10-02T06:37:08+00:00Replied to answer on October 2, 2020 at 6:37 am

This answer was edited.

Here is an example of the generated JSON:

{"rows":[{"NroCbte":"B070255988","TipoCbte":"FC","FechaCbte":"31/08/2020"},
{"NroCbte":"B130126218","TipoCbte":"FC","FechaCbte":"31/08/2020"},
{"NroCbte":"B130126219","TipoCbte":"FC","FechaCbte":"31/08/2020"}]}

2020-10-02T07:08:16+00:00Replied to answer on October 2, 2020 at 7:08 am

You can query the above JSON data with the following SQL:

select nrocbte, tipocbte, fechacbte 
   from json_table ('{"rows":[{"NroCbte":"B070255988","TipoCbte":"FC","FechaCbte":"31/08/2020"},
                    {"NroCbte":"B130126218","TipoCbte":"FC","FechaCbte":"31/08/2020"},
                    {"NroCbte":"B130126219","TipoCbte":"FC","FechaCbte":"31/08/2020"}]}', '$.rows[*]' 
      columns (NroCbte varchar2(20) path '$.NroCbte',
               TipoCbte varchar2(20) path '$.TipoCbte',
               FechaCbte varchar2(20) path '$.FechaCbte'));

Output:

+------------+----------+------------+
| NROCBTE    | TIPOCBTE | FECHACBTE  |
+------------+----------+------------+
| B070255988 | FC       | 31/08/2020 |
+------------+----------+------------+
| B130126218 | FC       | 31/08/2020 |
+------------+----------+------------+
| B130126219 | FC       | 31/08/2020 |
+------------+----------+------------+

You can use the above SQL query for a cursor, then loop through the cursor records and insert it into the table.

Adding row as required in the Form in APEX

Alphanumeric Sequence number in apex

Reset Password in Oracle Apex

Zaccheus Alsterberg

Jaiquez Vesela

Febbie Krechel

Sign Up

Sign In

Forgot Password

vinish.ai Latest Questions