Re: Using value of variable in PL/SQL

In article <30h90d$cp2_at_dcsun4.us.oracle.com> surman_at_wwsun10.us.oracle.com (Scott Urman) writes:
>In article <30e3t0$qje_at_m.ehd.hwc.ca>, lon_at_hpb.hwc.ca (LONG ON) writes:
>|> Is it possible to have pointer variable in PL/SQL?
>|>
>|> For example:
>|>
>|> Declare
>|> avar1 char(30) := 'a_table_name';
>|> Begin
>|> select * from ^avar1; /* ^avar1 => replace avar1 with 'a_table_name' */
>|> End;
>|>
>|> What are the alternatives to accomplish this?
>|>
>|> Thanks for any pointers.
>|>
>|> Long
>|> Ottawa
>|>
>
>Intriguing. Unfortunately, PL/SQL doesn't support pointers. You also can't use
>a PL/SQL variable to refer to an Oracle object, like a table name. The only tool
>that supports this is the precompilers, which have dynamic SQL capability. You
>can call a Pro*C program from PL/SQL using DBMS_PIPE, if you want to. PL/SQL 2.0
>doesn't have dynamic SQL capability. But PL/SQL 2.1 (shipped with Oracle7
>

 release 7.1) has a package called DBMS_SQL which will allow you to do this sort
>of thing.

If you are on a system that uses unix, you can use shell variables that will be substitued at shell run time.
set table 'tablename' (syntax based on shell) sqlplus login/login <<EOF

    select * from $table
    ;
EOF

-- 
Bruce Arbuckle                                    EMAIL: brucea_at_adcmail.atlas.com