IBM webMethods Hybrid Integration

 View Only

 Custom SQL Adapter returning no output for INSERT statement — webMethods IS 10.15

Ayushman Bokde's profile image
Ayushman Bokde posted 06/27/26 02:45 PM

Hi Everyone,
I am using webMethods Integration Server 10.15 and facing an issue with a JDBC Adapter custom query service.

Background:
I have a complex INSERT ... SELECT query that cannot be configured using the standard Table INSERT adapter, so I am using the Custom SQL adapter instead.

Problem:
The query executes successfully and data is inserted into the database. However, the Custom SQL adapter returns no output — not even rowsAffected or a success indicator.
Without a response, I cannot determine whether:
  • The insert was successful.
  • Or the value is not inserted due to non-fulfilling condition.

Question:
Is there a way to get a response from a Custom SQL adapter for an INSERT statement? Or is there an alternative approach to handle this in webMethods?
Screen shot is also attached. Any help is appreciated!

My SQL Query [Just for reference in case you are interested]

INSERT INTO INVENTORY_RESERVATIONS (
    RESERVATION_ID,
    ORDER_ID,
    PRODUCT_ID,
    QUANTITY,
    STATUS
)
SELECT 
    'RES-' || seq_reservation_id.NEXTVAL,
    ?,
    PRODUCT_ID,
    ?,
    'RESERVED'
FROM VW_AVAILABLE_INVENTORY
WHERE PRODUCT_ID = ? 
AND AVAILABLE_QUANTITY >= ?
Attachment  View in library
adapter_service_sc.png 100 KB
Kailash Kumar Mishra's profile image
Kailash Kumar Mishra posted 06/29/26 01:40 AM

Hi @Ayushman Bokde,

Can you also share the screenshot of the Result Field and Result Field Type parameters defined in the SQL Section of your Custom SQL Service.

I belief that is how the CustomSQL template behaves as such and you must use the standard INSERT template which is not possible in your case.

Perhaps you must get the Affected rows data from your DB itself.. Something like below if using Oracle

Use a PL/SQL Block in your CustomSQL Service
You can wrap your exact INSERT query inside an anonymous PL/SQL block. By adding an output bind parameter, you can capture Oracle's native SQL%ROWCOUNT variable.
  1. Open your CustomSQL adapter service in Designer.
  2. Replace your query with the following PL/SQL block:
sql
DECLARE
    v_order_id VARCHAR2(100) := ?;
    v_req_qty1 NUMBER := ?;
    v_prod_id  VARCHAR2(100) := ?;
    v_req_qty2 NUMBER := ?;
BEGIN
    INSERT INTO INVENTORY_RESERVATIONS (
        RESERVATION_ID,
        ORDER_ID,
        PRODUCT_ID,
        QUANTITY,
        STATUS
    )
    SELECT 
        'RES-' || seq_reservation_id.NEXTVAL,
        v_order_id,
        PRODUCT_ID,
        v_req_qty1,
        'RESERVED'
    FROM VW_AVAILABLE_INVENTORY
    WHERE PRODUCT_ID = v_prod_id 
    AND AVAILABLE_QUANTITY >= v_req_qty2;

    ? := SQL%ROWCOUNT;
END;
  1. Go to the Input Parameters tab and define your inputs in order:
    • ORDER_ID
    • QUANTITY
    • PRODUCT_ID
    • QUANTITY_CHECK (Note: Since ? is used twice for quantity in your original query, you must pass it twice or use PL/SQL variables like the block above to map them efficiently)
  2. Go to the Output Parameters tab.
  3. Add a new field (e.g., rowsAffected), set its data type to Integer, and map it to the very last ? bind parameter in the PL/SQL block.
Maximum Row The maximum number of records to retrieve from the database. The default value of 0 (no limit) retrieves all records. Use this field only with SQL statements that return a result set.
Result Field Name of the output field that contains the total number of rows affected by the SQL statement.
Result Field Type The data type of the Result Field.
Ayushman Bokde's profile image
Ayushman Bokde posted 06/29/26 02:14 PM
Hi Kailash,
Thank you for the suggestion! Your query gave me the right idea to find the solution.
Hi Everyone,
Providing my observation and solution below.
Root Cause:
When using a Custom SQL Adapter in webMethods with an INSERT statement, there is no option to map an output variable — so you cannot get rowsAffected or any response back.
 
My Solution — Use a Stored Procedure:
Instead of Custom SQL Adapter, create an Oracle Stored Procedure and use the Stored Procedure Adapter in webMethods. This allows you to define OUT parameters and get a response back.
Stored Procedure (I used) [In ORACLE DB]:
CREATE OR REPLACE PROCEDURE update_inventory_reservation (
    p_order_id      IN VARCHAR2,
    p_quantity      IN NUMBER,
    p_product_id    IN VARCHAR2,
    p_qty_check     IN NUMBER,
    p_rows_affected OUT NUMBER
) AS
BEGIN
    INSERT INTO inventory_reservations (
        reservation_id,
        order_id,
        product_id,
        quantity,
        status
    )
        SELECT
            'RES-' || seq_reservation_id.NEXTVAL,
            p_order_id,
            product_id,
            p_quantity,
            'RESERVED'
        FROM
            vw_available_inventory
        WHERE
                product_id = p_product_id
            AND available_quantity >= p_qty_check;

    p_rows_affected := SQL%rowcount;
    COMMIT;
END;

NOTE: I have provided all the necessary screenshots that can help you guys to understand this solution. If any doubt feel free to drop a comment.

Attachments  View in library
stored_proceedure_oracle_db.png 494 KB
sc2.png 118 KB
sc1.png 88 KB
Kailash Kumar Mishra's profile image
Kailash Kumar Mishra posted 07/06/26 02:09 AM

Thanks Ayushman for sharing the exact way of achieving your requirement . Glad to know I was able to assist to some extent on this.

Related Content