Re: Using variables in FROM clause?

>In PL/SQL can you use a variable in the FROM clause of a 'select' statement
>or do you have to use the DBMS_SQL package to do this. For example, the
>following shows what I'd like to do:
>
>tab1 varchar(30) ;
>c1 number(5);
>today varchar(10) := 'monday';
>
>if today = 'monday' then
> tab1 := 'mytab' /* mytab and yourtab a different tables
 */
>else
> tab1 := 'yourtab';
>
>select count(*) into c1 from tab1;
>
>
>
>What are my options, if any? Thanks.
>
>Mike

If you are use Forms or a procedure, the DBMS_SQL package is the only option. If you are using SQL*Plus, however, there is another option.

You can use the SQL*Plus substitution character & to change parts of a query. For example,  

define tab_name=mytab
select * from &mytab

You can even populate the defined variable at runtime using the NEWVALUE command in the COLUMN statement. This will also work if you DEFINE the variable, then use them in a PL/SQL block.

Dan Received on Tue Jan 13 1998 - 00:00:00 CET