Insert Json data to Oracle Table Dynamically. Stuck in Execute Immediate

Question

I have Json file and need to insert the data to Oracle table dynamically. For that, table columns and corresponding jason data columns are keeping in a lookup table. Lookup table has the below structure, Json_Column | Table_Column

While receiving Json file, I select the table columns and corresponding json columns from lookup table and put into variable A and B respectively. Here B used to define the values we have to select from Json file and A refers to which columns we have insert these values in the table.

For that, I used below query,

execute immediate ' insert into oracle_tbl('||A||') values ('||B||')';

But, I got below err. ORA-00984: column not allowed here

Finally, realised that the error is due to it is not fetching the data from Json file. Bcos, the B returns the column names only not the corresponding values in the Json file. Actually, I needed the values inside the json columns mentioned in the variable B.

Please advise how can I fix this issue ?

Thanks in advance. Monica

Reading the Json data using below,

FOR ALL_REC1 IN (SELECT * FROM JSON_TABLE (PJSON_DATA,'$.ExcelData[*]'
                                            COLUMNS ( Column1 NUMBER PATH '$.col1',
                                                      Column2 NUMBER PATH '$.col1'
                                                    )))

Column1 and Column2 are defined in variable B. But values of Column1 and Column2 are not picking in execute immediate.

What I'm getting after executing "execute immediate ' insert into oracle_tbl('||A||') values ('||B||')';" is,

execute immediate ' insert into oracle_tbl(tbl_clo1, tbl_col2) values (column1, column2)';

But column1 and column2 has value in Json file. It is not getting. I'm expecting below.

execute immediate ' insert into oracle_tbl(tbl_clo1, tbl_col2) values (value of column1, value of column2)';

p3consulting · Accepted Answer · 2022-11-20 10:17:43Z

1

May be this example will give you some ideas:

WITH colnames(column1, column2) AS
(
    SELECT 'col1', 'col2' FROM DUAL
)
select 
    REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(
                    q'~INSERT INTO ora_table( {col1name}, {col2name} ) VALUES( '{col1value}', '{col2value}')~',
                    '{col1name}', c.column1
                ),
                '{col2name}', c.column2
            ),
            '{col1value}', t.c1
        ),
        '{col2value}', t.c2
    ) AS sql
FROM colnames c,
    JSON_TABLE(
        q'~[ { "col1" : "v11", "col2": "v12" }, { "col1" : "v21", "col2": "v22" } ]~'
        , '$[*]'
        columns (
            c1 VARCHAR2 PATH '$.col1',
            c2 VARCHAR2 PATH '$.col2'
        )
    ) t
;

Then "FOR rec IN ..." loop on the query and "execute immediate" the rec.sql returned.

answered Nov 20, 2022 at 10:17

p3consulting

5,4212 gold badges14 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Monica Augustine-Plsql Newbie Over a year ago

Thanks for the help. I tried q' and it is not worked. I think, rest of the code not able to apply in this case, because the Columns may vary.

p3consulting Over a year ago

How so the q' ???? What did you tried exactly ?

Monica Augustine-Plsql Newbie Over a year ago

It is fixed by passing Json data directly to the insert statement. We used below method. stackoverflow.com/questions/64702928/…. I learn a new thing form your suggestion. Thank you for your help.

Collectives™ on Stack Overflow

Insert Json data to Oracle Table Dynamically. Stuck in Execute Immediate

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related