Guys,
I'm pretty new to PL/SQL and I'm haveing trouble with a package I'm trying to write. It should just be a simple package that counts the rows in all tables and inserts the results into a stats table. The error I returns is:-
ERROR at line 20:
ORA-06550: line 20, column 37:
PLS-00201: identifier 'TBLNAME' must be declared
ORA-06550: line 20, column 3:
PL/SQL: SQL Statement ignored

I've tested the variables and they all work its just seems to be looping the count(*) query that gives me the problem. Its worth pointing out that if I define the ROW_CNT variable its populate the table with this value - i just cant get it to dynamialy do it :(

The table DDL......

CREATE TABLE CAD_STATS_COUNT (
DBNAME VARCHAR2(10) NOT NULL,
TABLE_NAME VARCHAR2(30) NOT NULL,
ROW_COUNT NUMBER NOT NULL,
TIMESTAMP VARCHAR2(8) NOT NULL);

The package is......

/* Start of PL/SQL procedure */
/* set serveroutput on size 100000 */

declare
/* define variables */
DBNAME varchar(50); /*cad_stats_count.dbname%TYPE;*/
TBLNAME varchar(100); /*cad_stats_count.table_name%TYPE;*/
ROW_CNT number; /*(unt.row_count%TYPE;*/
TODAY varchar(100); /*cad_stats_count.timestamp%TYPE;*/
/* cursor declaration */
CURSOR C1 is select table_name from dba_tables;
begin
/* set variables */
Select name into DBNAME from v$database;
Select to_char((sysdate),'yyyymmdd') into TODAY from sys.dual;
/* start cursor loop */
open C1;
loop
fetch C1 into TBLNAME;
exit when C1%NOTFOUND;
/* count tables */
dbms_output.put_line /*to test variables*/(DBNAME||' '||TBLNAME||' '||TODAY);
Select count(*) into ROW_CNT from TBLNAME;
insert into cad_stats_count values (DBNAME,TBLNAME,ROW_CNT,TODAY);
end loop;
end;
.