Setting Variable Such as Database or Table Name

Toad Data Point & Toad Intelligence Central
1

I’m having difficulties setting a bind variable when running a query not in automation. I’d like to be able to have a query where the database name or the table name is defined by the variable. For example select * from TESTFILE.:Tbl_Name. From there it would prompt me to enter the table name. When I try to do this, it returns the error Token ? was not valid. Valid tokens . I tested the variable in a Where clause such as Where Name = :Name and place the variable and it works fine. Is there no way to set a variable on the database or table name you are selecting?

Thanks so much for any assistance

2

If you are on TDP 3.3, and are using Oracle, then you could surround those variables with #'s to make it work.

ex.

select * from database.#tableVar#

3

Table or database names do not work well in variables. Take a look at this: stackoverflow.com/…/how-to-use-a-variable-in-oracle-script-for-the-table-name

4

I’m sorry, I set up a bind variable such as [tag:dbase]# and put [tag:dbase]#.Table and it still says [tag:dbase]#.table not found. It’s like it doesn’t resolve the variable at all. It’s interesting how it’s resolved in the query when you say something like customer = :account, but it absolutely wont work when trying to involve that variable anywhere else, like on a database or table name.

Chad

5

Oh and by the way, I use toad data point and we just use a simple ODBC connection. Again though, not sure why this matters since it’s a variable I’m trying to pass through to the query.

Chad

6

You have to build the query dynamically. Try EXEC( ‘Select * from TestFile.’ || :Tbl_Name). That way you pass the variable string into an Execute statement which then translates the concatonated string into SQL and runs it. Use + instead of || for sybase connections.

7

Hey Greg, I appreciate the answer, but I’m not sure where and how to put this dynamic query together. What your saying does make perfect sense, just not sure how to do that part. Any futher info you could give would be greatly appreciated.

Chad

8

What type of database are you connecting to with ODBC. The replacement feature using the # symbol was built mainly for Oracle.

9

We have an ODBC connection to a DB2 environment. Just like you can prompt a variable by saying Account = :Account, I just can’t see why this same variable logic in Toad Data Point can’t be used to be placed anywhere, including a database or table name? I’m just not sure why the type of connection matters for variables because I would think it was resolved on the Data Point end before the query passes through to the connection. Thanks again for all the help with this. Chad

10

Chad, if you are still need this take a look at this link for DB2: stackoverflow.com/…/how-to-execute-an-sql-string-in-db2

Hope that helps. Looks like you still need to build a string for the select statement and then execute the string statment.