CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
mu shaikh
mu shaikh

Reputation: 99

PL/SQL JSON_OBJECT_T error Code: -40573 - ORA-40573: Invalid use of PL/SQL JSON object type

I'm trying to build one PL/SQL stored procedure to insert update delete data into the table using below procedure but it is failing with the error Error Code:

-40573 - ORA-40573: Invalid use of PL/SQL JSON object type.

Please help me

CREATE OR REPLACE PACKAGE XX_MESSAGE_PKG1
IS

PROCEDURE generic_service( p_request IN json_object_t,
                           p_response OUT json_object_t );
END XX_MESSAGE_PKG1;



CREATE OR REPLACE PACKAGE BODY XX_MESSAGE_PKG1
IS
    PROCEDURE generic_service ( p_request IN json_object_t,
                           p_response OUT json_object_t )
    AS
        l_po_header_id NUMBER;
        l_resp_obj JSON_OBJECT_T := JSON_OBJECT_T();
    BEGIN
        -- Processing Header based on operation
        CASE LOWER(p_request.get_string('operation'))
            WHEN 'add' THEN
                INSERT INTO PO_HEADER (PO_NUMBER, VENDOR_NAME, ORDER_DATE, TOTAL_AMOUNT, STATUS, CREATED_BY, CREATED_DATE)
                VALUES (
                    p_request.get_string('poNumber'),
                    p_request.get_string('vendorName'),
                    TO_DATE(p_request.get_string('orderDate'), 'YYYY-MM-DD'),
                    p_request.get_number('totalAmount'),
                    p_request.get_string('status'),
                    p_request.get_string('createdBy'),
                    SYSDATE
                )
                RETURNING PO_HEADER_ID INTO l_po_header_id;

            WHEN 'update' THEN
                UPDATE PO_HEADER
                SET VENDOR_NAME = p_request.get_string('vendorName'),
                    ORDER_DATE = TO_DATE(p_request.get_string('orderDate'), 'YYYY-MM-DD'),
                    TOTAL_AMOUNT = p_request.get_number('totalAmount'),
                    STATUS = p_request.get_string('status'),
                    UPDATED_BY = p_request.get_string('createdBy'),
                    UPDATED_DATE = SYSDATE
                WHERE PO_NUMBER = p_request.get_string('poNumber')
                RETURNING PO_HEADER_ID INTO l_po_header_id;

            WHEN 'remove' THEN
                DELETE FROM PO_HEADER WHERE PO_NUMBER = p_request.get_string('poNumber');
                l_resp_obj.put('status', 'SUCCESS');
                l_resp_obj.put('message', 'PO_HEADER deleted');
                p_response := l_resp_obj;
                RETURN;

            ELSE
                RAISE_APPLICATION_ERROR(-20001, 'Invalid operation for PO_HEADER');
        END CASE;

        -- Processing PO Lines
        FOR i IN 0 .. p_request.get_array('poLines').get_size - 1 LOOP
            DECLARE
                l_line_obj JSON_OBJECT_T := TREAT(p_request.get_array('poLines').get(i) AS JSON_OBJECT_T);
            BEGIN
                CASE LOWER(l_line_obj.get_string('operation'))
                    WHEN 'add' THEN
                        INSERT INTO PO_LINES (PO_HEADER_ID, LINE_NUMBER, ITEM_CODE, ITEM_DESCRIPTION, 
                                              QUANTITY, UNIT_PRICE, CREATED_BY, CREATED_DATE)
                        VALUES (
                            l_po_header_id,
                            l_line_obj.get_number('lineNumber'),
                            l_line_obj.get_string('itemCode'),
                            l_line_obj.get_string('itemDescription'),
                            l_line_obj.get_number('quantity'),
                            l_line_obj.get_number('unitPrice'),
                            l_line_obj.get_string('createdBy'),
                            SYSDATE
                        );

                    WHEN 'update' THEN
                        UPDATE PO_LINES
                        SET ITEM_CODE = l_line_obj.get_string('itemCode'),
                            ITEM_DESCRIPTION = l_line_obj.get_string('itemDescription'),
                            QUANTITY = l_line_obj.get_number('quantity'),
                            UNIT_PRICE = l_line_obj.get_number('unitPrice'),
                            UPDATED_BY = l_line_obj.get_string('createdBy'),
                            UPDATED_DATE = SYSDATE
                        WHERE PO_HEADER_ID = l_po_header_id
                          AND LINE_NUMBER = l_line_obj.get_number('lineNumber');

                    WHEN 'remove' THEN
                        DELETE FROM PO_LINES
                        WHERE PO_HEADER_ID = l_po_header_id
                          AND LINE_NUMBER = l_line_obj.get_number('lineNumber');

                    ELSE
                        RAISE_APPLICATION_ERROR(-20002, 'Invalid operation for PO_LINES');
                END CASE;
            END;
        END LOOP;

        -- Success response
        l_resp_obj.put('status', 'SUCCESS');
        l_resp_obj.put('po_header_id', l_po_header_id);
        p_response := l_resp_obj;

    EXCEPTION
        WHEN OTHERS THEN
            l_resp_obj.put('status', 'ERROR');
            l_resp_obj.put('description', 'Error Code: ' || SQLCODE || ' - ' || SQLERRM);
            p_response := l_resp_obj;
    END generic_service;
END XX_MESSAGE_PKG1;

input will be like below which is passed to to this procedure:

{
    "poNumber": "PO1004",
    "vendorName": "Tech Solutions",
    "orderDate": "2025-03-01",
    "totalAmount": 800.75,
    "status": "APPROVED",
    "createdBy": "User3",
    "operation": "add",
    "poLines": [
        {
            "operation": "Add",
            "lineNumber": 1,
            "itemCode": "ITEM005",
            "itemDescription": "Keyboard",
            "quantity": 4,
            "unitPrice": 50.00,
            "createdBy": "User3"
        },
        {
            "operation": "Remove",
            "lineNumber": 2,
            "itemCode": "ITEM006",
            "itemDescription": "Mouse Pad",
            "quantity": 10,
            "unitPrice": 8.50,
            "createdBy": "User3"
        },
        {
            "operation": "update",
            "lineNumber": 2,
            "itemCode": "ITEM006",
            "itemDescription": "Mouse Pad",
            "quantity": 10,
            "unitPrice": 8.50,
            "createdBy": "User3"
        }
    ]
}

my tables as below:

-- Creating the PO_HEADER table
CREATE TABLE PO_HEADER (
    PO_HEADER_ID     NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 
    PO_NUMBER        VARCHAR2(50) UNIQUE, 
    VENDOR_NAME      VARCHAR2(100),
    ORDER_DATE       DATE DEFAULT SYSDATE,
    TOTAL_AMOUNT     NUMBER(15,2) DEFAULT 0,
    STATUS           VARCHAR2(20) DEFAULT 'DRAFT',
    CREATED_BY       VARCHAR2(50),
    CREATED_DATE     DATE DEFAULT SYSDATE,
    UPDATED_BY       VARCHAR2(50),
    UPDATED_DATE     DATE
);

-- Creating the PO_LINES table without NOT NULL constraints
CREATE TABLE PO_LINES (
    PO_LINE_ID       NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 
    PO_HEADER_ID     NUMBER,  -- No FK constraint
    LINE_NUMBER      NUMBER,  -- Sequential line number
    ITEM_CODE        VARCHAR2(50),
    ITEM_DESCRIPTION VARCHAR2(200),
    QUANTITY         NUMBER(10,2) DEFAULT 1 CHECK (QUANTITY > 0),
    UNIT_PRICE       NUMBER(10,2) DEFAULT 0 CHECK (UNIT_PRICE >= 0),
    LINE_TOTAL       NUMBER(15,2) GENERATED ALWAYS AS (QUANTITY * UNIT_PRICE) VIRTUAL, 
    CREATED_BY       VARCHAR2(50),
    CREATED_DATE     DATE DEFAULT SYSDATE,
    UPDATED_BY       VARCHAR2(50),
    UPDATED_DATE     DATE
);

-- Creating indexes for performance
CREATE INDEX IDX_PO_HEADER_DATE ON PO_HEADER (ORDER_DATE);
CREATE INDEX IDX_PO_LINES_HEADER_ID ON PO_LINES (PO_HEADER_ID);

Upvotes: 0

Views: 39

Answers (1)

MT0
MT0

Reputation: 168450

  • NEVER catch the OTHERS exception as it hides the stack trace. Instead, catch specific named exceptions that you are expecting and, if there are unexpected exceptions, let the code fail and use the stack trace to help you to debug the code.
  • JSON_OBJECT_T is a PL/SQL object and cannot be used in an SQL scope.

To solve it, either:

  1. Extract the values into PL/SQL variables and pass those into SQL statements; or
  2. Don't parse the JSON in PL/SQL and, instead, use JSON_TABLE in SQL to parse the JSON string.

For example:

CREATE OR REPLACE PACKAGE BODY XX_MESSAGE_PKG1
IS
  PROCEDURE generic_service (
    p_request IN json_object_t,
    p_response OUT json_object_t
  )
  AS
    l_po_header_id  PO_HEADER.PO_HEADER_ID%TYPE;
    l_po_number     PO_HEADER.PO_NUMBER%TYPE;
    l_po_vendorName PO_HEADER.VENDOR_NAME%TYPE;
  BEGIN
    -- Processing Header based on operation
    p_response := JSON_OBJECT_T();
    l_po_number := p_request.get_string('poNumber');
    l_po_vendorName := p_request.get_string('vendorName');
    CASE LOWER(p_request.get_string('operation'))
    WHEN 'add' THEN
      INSERT INTO PO_HEADER (
        PO_NUMBER,
        VENDOR_NAME
        -- etc.
      ) VALUES (
        l_po_number,
        l_po_vendorName
        -- etc.
      )
      RETURNING PO_HEADER_ID INTO l_po_header_id;
    END CASE;

    -- Success response
    p_response.put('status', 'SUCCESS');
    p_response.put('po_header_id', l_po_header_id);
  END generic_service;
END XX_MESSAGE_PKG1;
/

or

CREATE OR REPLACE PACKAGE XX_MESSAGE_PKG1
IS
  PROCEDURE generic_service(
    p_request  IN  CLOB,
    p_response OUT CLOB
  );
END XX_MESSAGE_PKG1;
/

CREATE OR REPLACE PACKAGE BODY XX_MESSAGE_PKG1
IS
  PROCEDURE generic_service (
    p_request IN CLOB,
    p_response OUT CLOB
  )
  AS
    l_operation     VARCHAR2(10);
    l_po_header_id  PO_HEADER.PO_HEADER_ID%TYPE;
    l_po_number     PO_HEADER.PO_NUMBER%TYPE;
    l_po_vendorName PO_HEADER.VENDOR_NAME%TYPE;
  BEGIN
    -- Processing Header based on operation
    SELECT operation,
           poNumber,
           vendorName
    INTO   l_operation,
           l_po_number,
           l_po_vendorName
    FROM   JSON_TABLE(
             p_request,
             '$'
             COLUMNS (
               operation  VARCHAR2(10)  PATH '$.operation',
               poNumber   VARCHAR2(50)  PATH '$.poNumber',
               vendorName VARCHAR2(100) PATH '$.vendorName'
             )
           );

    CASE LOWER(l_operation)
    WHEN 'add' THEN
      INSERT INTO PO_HEADER (
        PO_NUMBER,
        VENDOR_NAME
        -- etc.
      ) VALUES (
        l_po_number,
        l_po_vendorName
        -- etc.
      )
      RETURNING PO_HEADER_ID INTO l_po_header_id;
    END CASE;

    -- Success response
    SELECT JSON_SERIALIZE(
             JSON_OBJECT(
               KEY 'status' VALUE 'SUCCESS',
               KEY 'po_header_id' VALUE l_po_header_id
             )
           )
    INTO   p_response
    FROM   DUAL;
  END generic_service;
END XX_MESSAGE_PKG1;
/

fiddle

Upvotes: 0

Related Questions