Re: How to pass a table name into procedure ?

From: Avi Abrami <aabrami_at_intersystemsww.com>
Date: Thu, 12 Oct 2000 08:03:37 +0200
Message-ID: <39E55439.10785799@intersystemsww.com>

That's not quite all you need to do! Basically, you can't use dynamic SQL in PL/SQL stored procedures - until Oracle 8i.

Here's an example of a stored function that uses dynamic SQL. The function returns the number of rows in the table whose name is supplied as a parameter.

    CREATE OR REPLACE FUNCTION fgetrcnt (p_table VARCHAR2)      RETURN NUMBER
    IS

      l_sql                           VARCHAR2(60);
      l_count                         NUMBER;
    BEGIN
      l_sql := 'SELECT COUNT(*) FROM ' || p_table;
      EXECUTE IMMEDIATE l_sql INTO l_count;
      RETURN l_count;

    END fgetrcnt;

HTH,
Avi.

Valgaeren Dirk wrote:

> try create procedure qq()
> ...
> select ...
>
> <majapu_at_poczta.onet.pl> schreef in berichtnieuws 39e42c97$1_at_news.vogel.pl...
> > I'd like to pass a table name into procedure, for example
> >
> > CREATE PROCEDURE QQ(table_name IN ...)
> > ...
> > SELECT * FROM table_name ;
> > ...
> >
> > How to do that ?
> > Thanks
> > Mariusz
> >
> >
> >
> >
  Received on Thu Oct 12 2000 - 01:03:37 CDT