Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: PL/SQL naming a table
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;
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