Re: Variables in SELECT

In article <3iktmo$8i1_at_usenet.INS.CWRU.Edu> fn649_at_cleveland.Freenet.Edu (Jeff Houston) writes:

> Is it possible to use a parameter input to a procedure to
> provide a table name for a PL/QSL select statement? The
> following statement does not substitute for the table name,
> but prompts the user for input during compilation:
>
> SELECT COUNT(*) FROM '&1'
> WHERE deptno IS NOT NULL
>
> Assigning the parameter to a variable, and using the variable
> in the above statement did not work either. Any thoughts?

This looks like SQL*Plus variable substitution to me. This would work (after you take out the single quotes) in an anonymous PL/SQL block called from SQL*Plus since SQL*Plus substitutes all the variables before passing the complete block to the PL/SQL engine.

If you need dynamic SQL capability within stored procedures, you should take a look at the package DBMS_SQL available in 7.1.

--
"And this is all I have to say about that..."   - F. Gump
      ___
     (o o)
+-oo0-\_/-0oo---------------------------------------------------------------+
|  Alvin W. Law ..... Oracle Corporation ....... Email: alaw_at_us.oracle.com  |
+---------------------------------------------------------------------------+

ORA-03113: end-of-file on communication channel