Automation Designer, Compare prompt value with maximum id of a table?

Toad for Oracle
1

Hi all,
I am a newbie using Automation Designer of TOAD 12.6.

I want to make an app :

  1. prompt for a value
  2. query a database table to check that the prompted value is
    the last id
  3. Then I will execute some functions.

My steps are

  • Create a variable (let it be PAYMENT_ID).
  • Make a prompt action for the above variable.
  • Create a file with contents with the following code

DECLARE
v_db_id NUMBER;
v_user_id NUMBER;
BEGIN
v_user_id := %PAYMENT_ID%;

SELECT ID
INTO v_db_id
FROM ( SELECT ID
FROM TABLE1
ORDER BY DESC)
WHERE ROWNUM < 2;

IF v_db_id = v_user_id THEN
RAISE_APPLICATION_ERROR(-20000, 'Missmatch');
ELSIF
NULL;
END;

END;

  • Execute the above script

The problem I have is that RAISE_APPLICATION_ERROR is not recognized.

Moreover, I would like if the script fails to create an error message action.

Any ideas please ?
Thanks in advance

PS :
I tried also actionable query as follow :

(SELECT ID
FROM ( SELECT ID
FROM TABLE1
ORDER BY DESC)
WHERE ROWNUM < 2) = %PAYMENT_ID%

But the variable is not recognized..

2

Sorry for the delay.

In Toad 12.6 you can't pass a variable into a script file. This was added in a later version (not sure offhand but I want to say around 12.10 or 12.11) and it works as shown below:

image
image.jpg1148×730 255 KB

3

Thanks a lot for your answer.
Finally, I created a file with contents with
v_param_id := %PAYMENT_ID%;

and then I execute the generated script.

Can I somehow make the script to be failed as the raise_application_error is not recognized?

4

The script should fail on RAISE_APPLICATION_ERROR. But your version of Toad won't put any a value in %PAYMENT_ID%. Also, I noticed that you need a '/' on its own line after your pl/sql block.

There is a "Fail action on script error" checkbox. If the script has an error then Toad will report "failed" when that box is checked.