Toad World® Forums

Automation Variable "Invalid Number"

Automation file where I want to prompt user for an Entity code whose value can be either Alphanumeric or Numeric.

In straight SQL when prompting for the value with a variable either type returns fine.

However, in automation the Alphanumeric works but the codes that are fully numeric fail as "Invalid Number". I've tried a number of functions in the SET VARIABLE activity, many of which work for the Alphanumeric but still fail for the fully numeric values. Variable type = String in the Set Variable set up.

TDP v5.04

Thanks for any help

Can you provide the portion of the where clause that includes the bind? If the value can be alphanumeric or numeric then the datatype of the bind column must be varchar. What is the data type of the binding column?
where col_a = :var what is datatype of col_a. And how have you defined the bind var?

The error you are getting sounds like the binding column is a numeric and you are binding a string value.


Where clause from the SQL script -


The data type of "CODE" is VARCHAR2.

I set up the variable type as STRING. Image attached.

again, I enter a value like "MN5005" it works. but "644992" gets "invalid number".

I couldn't figure out a function in the Variable Value to make it work.

I can't seem to reproduce that issue. What version of TDP are you using? In both TDP 4.3 and 5.0x I use zip code as a column to bind. It is a varchar and I can enter numbers or zips with chars without error and with correct results.

I see you are using the Prompt for Var. When the dialog pops up what is the datatype? Mine shows string which yours should also. What is the exact error you are getting? Is it an "ORA -01722 invalid number" error? Can you post your automation log? Of if you are more comfortable with opening a support ticket we can do that also.

Toad Data Point (32 bit)

I get STRING as the datatype in the popup. attaching logs for both a GOOD successful run with an alphanumeric value and the BAD unsuccessful run with Numeric value. Attached image is where i ran it a couple times with numeric.NPC_NII_PRECHECK_BETA_BAD.log (3.9 KB)