CODEWITHSUNDEEP
oracle-databasedynamicplsqloracle-apexoracle-apex-5
Darren Evan Clark
Darren Evan Clark

Reputation: 21

PL/SQL Dynamic action [Set Value] not recognizing apex item value

I have a problem setting the value of an apex item (P13_3) using a pl/sql defined dynamic action. At the moment the dynamic action is triggered using a button. For example if "530000000019" is entered into the item (P13_3), after the clicking the button it should return a value (Product Code) and set the item with that value.

This is the pl/sql code that runs when the button is clicked:

DECLARE
p_code    products.prod_code%TYPE;
p_id      products.prod_id%TYPE;
BEGIN
    p_id := :P13_2;
    SELECT prod_code INTO p_code FROM products WHERE prod_id = p_id; 
    RETURN p_code; 

END;

This is the error that appears:

Ajax call returned server error ORA-01403: no data found for Set Value.

This means that no data was returned when the SELECT INTO clause ran. I then altered the code and ran this code to see if there were any faults in the code:

DECLARE
    p_code    products.prod_code%TYPE;
    p_id      products.prod_id%TYPE;
    BEGIN
        p_id := 530000000019;
        SELECT prod_code INTO p_code FROM products WHERE prod_id = p_id; 
        RETURN p_code; 

    END;

This code returned a value and the set value dynamic action was successful. This therefore means, that in apex was not picking up the value in the P13_3 item.

I have a apex process that has similar syntax that calls the P13_3 apex item and it runs successfully. Here is the code of the apex process:

DECLARE
b_code    products.prod_id%TYPE := :P13_3;
p_quant    products.prod_qnty%TYPE := :P13_2;
BEGIN

    UPDATE products
    SET prod_qnty = prod_qnty - p_quant
    WHERE prod_id = b_code;

END;

If I am not mistaken, I would say that this proves that the problem lies with dynamic action and not the pl/sql code. I am currently using apex 5.1 (The 16 Dec release). Please Help. Thank you in advance :)

Upvotes: 2

Views: 6329

Answers (2)

user27280750
user27280750

Reputation: 1

You need to specify "item to submit" with the input item.

Upvotes: 0

Amol
Amol

Reputation: 428

I have tried similar work ,I have created two text boxes one for input & another for output & I am getting expected result .Please check following screen shots of result & dynamic action settings enter image description here And dynamic action settings is as follows : enter image description here Edit view of True action is as follows : enter image description here Hope this will help you.

Upvotes: 0

Related Questions