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.
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?
IE:
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.
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.
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) NPC_NII_PRECHECK_BETA_GOOD.log (4.1 KB)
I am curious if there was ever a resolution to this issue? I am experiencing the same thing when trying to add variables to my automation. The automation I have consolidates a series of Toad Views, when I add the bind variables to the Toad Views bind screen they work fine. When I enter the variables in the automation script with the same datatypes I receive the ORA-01722 Invalid Number error. Just like the example provided in this article I am trying to create a string variable that is comprised of only numbers, no alpha characters.
I have tried both adding ' around the numbers and changing to a bind reference of #var# and neither has helped to correct the issue. I can still run the report I am trying to run, but I am unable to leverage the automation functionality due to this issue with an all numeric string not being processed properly. I have verified the datatype in the tables I am referencing, in all cases it is Varchar.
Hi, variable in automation is not equivalent of variable in SQL. We always convert value to specific datatype so you should be able to use/set this value also on other places then on Query.
I suggest to use convert function in your query so you are sure you will use correct datatype.
Sample: Select * from table where my_varchar_column = TO_CHAR(:BIND_VAR)
I will give that a try in my query and add that to my notes about the variables not being the same. Based on the online documentation for Toad Data Point there did not seem to be a difference noted. It would be of great if the online documentation for Automation called out this difference so other users do not have the same issue.
I have tested this workaround it is working as expected in Automation. If there is a way to get Automation to recognize the Datatype and not require the TO_CHAR it would be appreciated, but this does get us functioning.
