CODEWITHSUNDEEP
jsonoracle-databaseplsql
Monica Augustine-Plsql Newbie
Monica Augustine-Plsql Newbie

Reputation: 11

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

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)';

Upvotes: 0

Views: 302

Answers (1)

p3consulting
p3consulting

Reputation: 4670

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.

Upvotes: 1

Related Questions