Question 283981 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

extracting and inserting data from JSON in Oracle

I have one JSON file and I´m trying to insert in database.

Inserts are not working, not sure if I am looping correctly json

I´ve done a very easy example:

This is the table:

CREATE TABLE TEST (
  remote_claimid VARCHAR2(20) PRIMARY KEY
);

and the procedure:

    PROCEDURE TEST (
        p_json CLOB
    ) AS
    BEGIN
        INSERT INTO TEST (
            remote_claimid
        )
        SELECT
            remote_claimid
        FROM JSON_TABLE(
                 p_json,
                 '$.claim[*]'
                 COLUMNS(
                   remote_claimid VARCHAR2(20) PATH '$.remote_claimid'
                 )
        );
               
        COMMIT;
        exception when others then 
            log_api.fatal(log_ctx, 'ERROR INSERT_TEST');
            raise;
    END TEST;

And this is the JSON

{claim=[{remote_claimid=5680}, {remote_claimid=7654}]}

This is the call:

            json = '{"claim"=[{"remote_claimid"="5680"}, {"remote_claimid"="7654"}]}'
            try {
                sql.call('{call BILLING_API.TEST(?)}', [json])
                output = [SUCCESS: true, msg: "inserts added."]
                sql.commit()
            } catch(SQLException se) {
                sql.rollback()
                log.error("Exception: ${se}; Procedure: BILLING_API.TEST; Params: Clob ${responseList}")
                output = commonDBService.processException(se)
            } finally {
                sql.close()
            }

Nothing is inserted, no errors… nothing
Any idea what´s happening?

Thanks!

Tags:

2

Answers


  1. 0

    With correct JSON your path works:

    SELECT
    remote_claimid
        FROM JSON_TABLE(
                 q'~{"claim" : [{"remote_claimid": 5680}, {"remote_claimid" : 7654}]}~',
                 '$.claim[*]'
                 COLUMNS(
                   remote_claimid VARCHAR2(20) PATH '$.remote_claimid'
                 )
        );


5680
7654
    Login or Signup to reply.
  2. 0

    I could not find any issue in your code. But i will suggest , Can you try like this. I think this is the better way to insert json into the oracle db.

    import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

public class YourClassName {
    public static void main(String[] args) {
        String json = "{"claim":[{"remote_claimid":"5680"}, {"remote_claimid":"7654"}]}";
        
        try (Connection connection = YourDatabaseConnectionProvider.getConnection()) {
            // Assuming YourDatabaseConnectionProvider is a class providing a connection to your Oracle database
            try (CallableStatement callStmt = connection.prepareCall("{call BILLING_API.TEST(?)}")) {
                callStmt.setString(1, json);
                callStmt.execute();

                // If you need to retrieve output parameters, you can do so here
                connection.commit();

                System.out.println("Inserts added.");
            } catch (SQLException se) {
                // Rollback the transaction in case of an exception
                connection.rollback();
                log.error("Exception: " + se + "; Procedure: BILLING_API.TEST; Params: Clob " + json);
                // Handle the exception or delegate it to a method like commonDBService.processException(se)
            }
        } catch (SQLException e) {
            // Handle connection-related exceptions
            e.printStackTrace();
        }
    }
}

    Hope this code helps you.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search