Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: PL/SQL naming a table

Re: PL/SQL naming a table

From: Clemens Hoffmann <choffmann_at_steinmayr.de>
Date: Mon, 23 Aug 1999 09:40:23 +0200
Message-ID: <7pqtp1$n5m$1@newsread.f.de.uu.net>


Hi there,

>Just i would like to know how to make a simple procedure to count the rows
>of any table..
>
>something like this:(but well-formed)
>
>create procedure contar (tabla IN varchar2)
>AS
> BEGIN
> select count(*) from tabla;
> END contar;

To use table names, column names and other Oracle related stuff you have to use dynamic sql.

The following code will do what you want. Be aware that it does not have any error hendling. Passing a wrong table name will cause a standard Oracle error stack.

PROCEDURE count_in_table(

     table_name in varchar2,
     rows_in_table out number)
AS
     v_cid       number;
     v_rows    number;
     v_stmt     varchar2(512);
     v_res       number;
BEGIN
     v_stmt := 'select count(*) from ' || table_name;

     v_cid := DBMS_SQL.OPEN_CURSOR;
     DBMS_SQL.PARSE(v_cid, v_stmt, dbms_sql.native);
     DBMS_SQL.DEFINE_COLUMN(v_cid, 1, v_res);
     v_rows := DBMS_SQL.EXECUTE(v_cid);

     IF DBMS_SQL.FETCH_ROWS(v_cid) = 0 THEN
          rows_in_table := 0;
     END IF;

     DBMS_SQL.COLUMN_VALUE(v_cid, 1, v_res);
        rows_in_table := v_res;

END; You can invoce this procedure using a skript like this

DECLARE
       v_res number;
BEGIN

       count_in_table('rufnummer', v_res);
       DBMS_OUTPUT.PUT_LINE('Rows in table rufnummer: ' || v_res);
END; Received on Mon Aug 23 1999 - 02:40:23 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US