how to use a variable in oracle script for the table name

If you are running this script from sqlplus (which looks to be the case), you want to use the DEFINE command, which allows you to create sqlplus substition variables that are just straight string substitution, e.g.:

define tableNm = 'customers2008'select * from &tableNm;

See Using Sql*Plus for more information on how these are used. You can pass values into your script from the command line using the predefined positional substition variables, like this:

define tableNm = &1select * from &tableNm;

... and then invoke sqlplus like so:

sqlplus user/pwd@server @myscript.sql customers2008

If you don't pass in a value on the command line, the script invoker will be prompted for the value.

See Dave Costa's answer below for the differences between bind and substitution variables.

To try to add some explanation:

The method that you were trying to use is called a bind variable. A bind variable is identified in Oracle SQL by a colon followed by an identifier. The purpose of a bind variable is that its value does not need to be known when parsing the SQL statement; the statement can be parsed once and then executed multiple times with different values bound to the variable.

In order for a SQL statement to be parsed, the table and column names involved must be known. So the table name can't be represented by a bind variable, because the value would not be known at parse time.

If you are simply executing SQL and inline PL/SQl via SQLPlus, then substitution variables are an easy way to deal with this issue, as Steve explained. A substitution variable is replaced with its value when the SQLPlus client reads the command, before it even sends it to Oracle for parsing.

You have to do something like this:

EXECUTE IMMEDIATE 'select * from' || tableNm;

This is because Oracle does not allow bind variables for table (or any other object names).

There are significant security implications with the EXECUTE IMMEDIATE approach: when the tableNm value is user-supplied, you are wide open to SQL injection attacks.