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

2
votes

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 :)

1
oracledynamicplsqloracle-apexoracle-apex-5
For me, it looks like a problem with a type cast. What is the type of products.prod_id and what is the type of P13_3 item? I suppose, that products.prod_id is a number, so try to check the type of the item, and if it is not a NUMBER, change it (item properties -> Identification -> Display As -> Number Field). – Dmitriy
Got it to work. Thanks Dmitry. There was a conflict was column types. The data belonging to the column captured in P13_3 is not the same type as the data that has to be retrieved. – Darren Evan Clark
Hey guys so I just ran the page again to do some testing and all of a sudden it no longer works again. Absolutely no change to the code. It simply does not want to work. Please help. – Darren Evan Clark

1 Answers

0
votes

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.