I have the darndest issue. I have a .tdr I’m trying to automate. When I run the SQL query behind it, it runs fine. When I refresh the .tdr itself with new values for the bind variables, it runs fine.
When I put it in an automation sequence, I get an “invalid number” error. There’s something different about how the automation defines & passes variables than the Bind Variable dialogue used in refreshing a standalone .tdr.
Here’s the section of problematic code:
SELECT xxxx from yyyyy
where TR.D_LST_PRM_CNTY_TYP IN (&TCOUNTY_GRP)
I can pass a list of counties char(3) to &TCOUNTY_GRP and it will run correctly as a solo .sql query or solo .tdr, but when I set the value (as a string) in the Set Variable function in Automation, I get “Invalid Number.” I’ve tried setting it as a number, using to_char inside the code; I’ve tried with and without quotes when setting the value, quotes around the parameter name, etc. Hard coding the list of counties is possible, but it’s a lot of reports 64 x 6 to be exact.
Anyway, insight would be a great present for when I come back from the holiday tomorrow!
Here’s what’s happening in the Automation Set Variables versus the Bind Variables dialogue box:
In Bind Variables, entering &TCOUNTY_GRP as ‘01’,‘02’,‘03’ correctly fills in the SQL parameter
as IN (‘01’,‘02’,‘03’) – a list of character codes to match char(3) codes in the table.
Entering ‘01’,‘02’,‘03’ as a String in the Set Variable activity results in
IN (‘01,02,03’) which doesn’t work. Setting 01,02,03 without quotes results in an error from the commas.
I want to be able to use the set variable activity and set my parameter &TCOUNTY_GRP so that it is correctly represented in the SQL IN statement literally as (‘01’,'02,‘03’).
Is there some way to concatenate or escape characters in the Set Variable activity to make this happen?
Sorry, I got some of that wrong. Entering 01,02,03 into the Set Variable activity results in ‘01,02,03’ which doesn’t work–need a comma separated list of chars.
Entering ‘01’,‘02’,‘03’ into the Set Variable activity results in an error “cannot be converted to a string, expecting EOF.”
The Set Variable activity treats inputs differently than the Bind Variable dialogue. This is a problem, because when developing a .tdr, you’re working with the Bind Variable dialogue box, and then you find out your .tdr doesn’t work in Automation with Set Variable.
The issue is comma-separated lists of characters can be entered into the Bind Variable dialogue as a parameter inside an IN (¶m_name) statement and run correctly.
In the Set Variable activity, the same parameter entry fails.
A list entered as ‘01’,‘02’,'03 in the Set Variable activity will throw an error, “cannot be converted to string.”
We had a bug with the SQL Plus bind vars in automation. We made a patch that includes a fix for this. Please download either base or pro depending on your licesnse.
I am having the same issue and I’ve been checking the Forums and Tech Support for TDP for the last two days non-stop and finally found someone who could explaing my issue. I’m having this exact issue (BTW, I’m a newbie in SQL, just a user who want to make his life easier through automation :)).
I tried installing the package above and the app didn’t allow me doing it because I already have the package.
I’m currently using TDP 3.5.0.2926.
I hope you can help me.
(Debbie, I’m impressed how you and your team have responded so many difficult situations from us, the users :). Thanks!)
Have you tried using 2 or 3 single quotes instead of one. ‘‘01’’, ‘‘02’’,’‘03’’ or ‘’‘01’’’,’’‘02’’’,’’‘03’’’ or maybe ‘‘01’’’, ‘’‘02’’’, ‘’‘03’’ (2 on the ends with 3 in the middle). Not sure because it is going to put in single quotes of its own.
Try this one: select ‘’‘01’’,’‘02’’,’‘03’’’ as TestString (3 single quotes on the ends with 2 on the middle ones). The select statement works that way but if Toad puts in its own quotes then all 2 single quotes is may be the way to go.
Sadly this did not work. I tried all your inputs. I thought I should be more specific on what I'm doing:
My automated query calls my variable this way: CELL in (:Cell)
and the input should be a series of string values separated by comma, such as: 'ABCD','EFG','HIJ'.... (usually several tens or more, including first and last single quotes). Below a screenshot of the exact error I get.
On my regular queries I usually use: CELL in ('&&Cell') with the following input ABCD','EFG','HIJ (and works OK) . Please note that I don't use the first or last single quotes on my regular ToadDP (or Toad for Oracle)
Greg, I really apologize, I'm not that familiar with TDP and it seems to be very picky on the input box for variables on the automation scripts.
The Good news: the input box for the variable does not give an error now.
The bad news, the AUTOMATION does not run yet. Below a screenshot of the errors. As a reminder my statement is CELL in (:CELL) so I need to use the first and last single quote
Hi, I’m the originator of this thread, and it still does not work for me either. Having an escape character is fine, but I would rather the Automation behave the same as the SQL editor and the .TDR files for all inputs. It’s frustrating to write SQL, make a report file, and then have it fail in automation.
I believe Debbie Peabody told me there was a ticket for this (correctly passing lists in Automation).
Additionally, in automation, you can’t pass more than one parameter to an Excel file, but you can in the SQL editor and TDR file. Automation throws a “missing parameter to query” error from Excel, though the TDR and SQL will work for the same Excel.
