can we create variable name as table name in plsql

by Gopi
(Hyderabad,India)

can we create variable name as table name in plsql?

example

declare
tab_name varchar2(100);
a number;
begin
tab_name='emp';
select rownum into a from tab_name;
end;


tab_name is a variable;

Comments for can we create variable name as table name in plsql

Average Rating starstarstarstar

Click here to add your own comments

Jun 01, 2016
Rating
starstarstarstar
Native Dynamic SQL
by: John

Yes you can do that - you just need to use dynamic sql, either by using the pl/sql Native Dynamic SQL functionality or by using the DBMS_SQL package which allows for more complex SQL statements.

In this case the requirements are straightforward so we can just use the pl/sql Native Dynamic SQL functionality like so:

declare
tab_name varchar2(30) := 'EMPLOYEES';
type num_tab is table of number index by pls_integer;
a num_tab;
begin
execute immediate 'select rownum from '||tab_name bulk collect into a;
dbms_output.put_line(' a(1) = '||a(1));
end;


Using EXECUTE IMMEDIATE enables us to build the SQL statement at run time. In this case we know what the statement will be because tab_name is defined locally but it could be passed in as a parameter or we could have logic in our PL/SQL code to determine which table to access.

As this statement returns more than 1 row we have to use the BULK COLLECT INTO clause and, of course, a PL/SQL collection. If we know that there will only ever be 1 row returned (for example if we were using an aggregate function such as count or max) then we would just use the INTO clause and a normal PL/SQL variable of thE appropriate type.

See http://www.asktheoracle.net/plsql-collections-associative-arrays.html for more about PL/SQL collections.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.