Re: PLSQL and variable Table Names

A copy of this was sent to "Annett Tempel" <annett.tempel_at_gmx.de> (if that email address didn't require changing) On Fri, 15 Jun 2001 16:47:50 +0100, you wrote:

>Hi,
>
>I have a short question.
>I have to write a SQL-Statement where I count the records.
>thats no problem.
>
>Code:
> CURSOR zaehler_cursor (tab_xx VARCHAR2:=tabelle) IS
> SELECT COUNT(*)
> FROM tab_xx;
>
>you see that I'll give the tablename with a parameter.
>But there is a mistake and I dont know where?
>Can you help me?
>
>Thanks and sorry for my english
>Annett
>
>
>

you cannot parameterize values for identifiers.

You can use dynamic sql.

Since there is no version provided, I'll assume the latest version of Oracle. You would use DBMS_SQL in releases prior to v8.1

declare

   some_plsql_variable number;
begin

   execute immediate 'select count(*) from ' || tab_xx INTO some_plsql_variable; end;

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag

Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  

Opinions are mine and do not necessarily reflect those of Oracle Corp