Toad for Oracle Parameters

Toad for Oracle
1

I know Toad supports run-time params to accomplish things like:

select count(x) from temp_table_&&suffix;

But is there a way to concatenate them to form more complex table names?

select count(x) from &&prefix_temp_table_&&suffix;

2

Not that I know of. Have you considered private synonyms?

Create synonym ABC for A_long_table_name;

then you can "select * from ABC"

3

Are you referring to a WITH statement where WITH ABC as (select * from XYZ) select * from ABC?

I have a situation where we have a variety of tables with similar functions but different names and I'm trying to consolidate them into one script that accepts parameters that will change the names dynamically. It works with suffixes, but I haven't found a way to use it as a substring of a table name. I tried back-ticks, quotes, pipe concats, but nothing is working. Thought perhaps Quest would have a lesser known way to handle string concatenations since it's rendering before the SQL executes.

4

No, that might perform badly and you'd still have to type the full table name.

I am suggesting making private synonyms in your schema.

5

That sounds like a viable option. I'll have to run a few tests. Thanks.

6

I suppose another option, if you want to use variables, is make the whole tablename a variable.

7

Can you declare variables when running sql scripts? I haven't tried that, but that would work as well.

8

Synonym might not be a bad solution since I can define everything at the top of the script. The alternative would be defining local variables before running it, but I've only done that with Toad Data Point so I don't know if Toad for Oracle behaves the same.

9

This works, but then you have to run as a script, which is fine if you were going to run as a script anyway. If you were only going to run a single statement, you should run with F9 (execute/compile) instead of F5 (execute as script). F9 is far more efficient.

DEFINE tbl_name = "EMP" (CHAR)

select * from &&tbl_name;

10

Nice, I'll try that as well.

I was thinking Oracle would complain if I tried to predefine a table before it was created, but it appears to create a pointer to name without needing an object behind it. Surprisingly this worked.

CREATE SYNONYM moo_&&suffix FOR foo_&&suffix;

create table foo_&&suffix as select sysdate as theDate from dual;

select * from foo_&&suffix;

Two good ideas, thank you.

11

You can change that to "Create or replace synonym ....." so that it won't give you an "object already exists" error when you run it the 2nd time. That or add "drop synonym ..." at the bottom.

12

Is there a concat function that can be used for something like this?

DEFINE tbl_name = "tbl_&&substring_var" || "tablename&&suffix" (CHAR);

13

Nice, I'll try that.

14

Is there a concat function that can be used for something like this?
DEFINE tbl_name = "tbl_&&substring_var" || "tablename &&suffix" (CHAR);

Maybe? Not sure. I've never tried to do anything like this before. I played around with it briefly just now but couldn't make it work.

15

If it supported something like DEFINE tbl_name = concat(string1,string2) (char); I'd probably be set.

16

Looks like the '.' is used for concatenation.

DEFINE tbl_prefix = "E" (CHAR);
DEFINE tbl_middle = "M" (CHAR);
DEFINE tbl_suffix = "P" (CHAR);

DEFINE tbl_name = &&tbl_prefix.&&tbl_middle.&&tbl_suffix;

select * from &&tbl_name;

1 Like
17

Brilliant, just made my day. Thanks for all the suggestions.

1 Like