The Oracle (tm) Users' Co-Operative FAQ

How do I use a variable as a table name inside pl/sql ?


Author's name: Jonathan Lewis

Author's Email: Jonathan@jlcomp.demon.co.uk

Date written: 4th Feb 2003

Oracle version(s): 8.1 - 9.2

I have a requirement to select a list of columns from a table in a pl/sql procedure. Although the column names are always the same, the choice of table varies. Can I pass the name of the table to the procedure and use it as a bind variable in SQL statement ?

Back to index of questions


As asked, the answer to the question is no. A table name cannot be replaced by a bind variable. However, there are (at least) two ways in which the required task can be accomplished. The dbms_sql package, and the execute immediate call - also referred to as native dynamic SQL (NDS).

The dbms_sql package has been around since Oracle 7 - various examples of use are available at www.jlcomp.demon.co.uk under the PL/SQL and Miscellaneous menus if you want to read more about it. This note will restrict itself to execute immediate.

In its simplest form, if you can write a legal SQL statement, you can execute it inside pl/sql. For example:

	create or replace procedure drop_dead as
	begin
		execute immediate 'drop table dead';
	end;
	/
	execute drop_dead;

There are often details of privileges to worry about when doing this kind of thing, initially you may find that a statement that you can run from SQL*Plus will return an error about 'lack of privileges' or 'object does not exist' when embedded in pl/sql in this fashion. Remember, unless declared with invoker's rights, the procedure runs only with the privileges granted explicitly to the creator of the procedure (or to Public).

To uas a select statement with this mechanism, then, you may need to need to worry about (a) building the string dynamically, (b) passing in some values to use in a where clause, and (c) having somewhere to put the results.

	create or replace function fixed_columns(
			i_table		in 	varchar2,
			i_pk 		in 	number
	) return varchar2
	as
		m_desc varchar2(30);
	begin
		execute immediate
			'select description from ' || 
			i_table ||
			' where pk_col = :b1'
		into m_desc		-- the place to put the single value selected
		using i_pk;		-- the value to supply for bind variable b1
	
		return m_desc;
	end;
	/
	execute dbms_output.put_line(fixed_columns('TABX',99))

Of course, you should not use execute immediate casually - every single call invokes an explicit parse - and if you are too happy with concatenating values (as well as tables) into strings, then you will be generating large numbers of non-sharable SQL, resulting in massive hard-parse and latching costs. This is why I have chosen to use the bind variable construction in my example. With just the table-name concatenated I end up with one sharable SQL statement (soft-parsing) per table so the number of different SQL statements is probably quite low.

There are many more ways of using execute immediate - including array fetches, array updates with array returns of errors, and so on. Some of the more advanced tricks are only available in the later versions of Oracle, though.

Remember, however, that native dynamic SQL (NDS) is inherently more expensive than static SQL. Choose the places you use it with care.


Further reading: N/A


Back to top

Back to index of questions