-
Error(31,30): PLS-00302: component 'TABLE_NAME_V' must be declared
Hi People,
I am writing a procedure for work but I can't seem to locate the above error. I have it declared in the correct place but yet the compiler complains about the variables in the sql_stmnt below.
There are actually two errors owner_v and table_name_v in that sql_stmnt.
Can someone look this over to see where my mistake is? I would appreciate it.
thanks
Error(31,30): PLS-00302: component 'TABLE_NAME_V' must be declared
create or replace
PROCEDURE SCHEMA_CNT IS
owner_v varchar2(100);
table_name_v varchar2(100);
sql_stmnt varchar2(4000);
cnt_val number;
CURSOR c1 IS select owner, table_name into owner_v, table_name_v from dba_tables
WHERE owner NOT IN ('SYS','SYSTEM')
GROUP BY owner, table_name;
BEGIN
FOR c1_rec IN c1 LOOP
sql_stmnt := 'select count(*) from '||c1_rec.owner_v||'.'||c1_rec.table_name_v;
EXECUTE IMMEDIATE sql_stmnt INTO cnt_val;
DBMS_OUTPUT.PUT_LINE(c1_rec.table_name_v||' has '||cnt_val||' number of rows');
END LOOP;
END;
-
It helps to format the text. How does this work?
Code:
CREATE OR REPLACE PROCEDURE SCHEMA_CNT
IS
owner_v VARCHAR2(100);
table_name_v VARCHAR2(100);
sql_stmnt VARCHAR2(4000);
cnt_val NUMBER;
CURSOR c1
IS SELECT owner, table_name
INTO owner_v, table_name_v
FROM dba_tables
WHERE owner NOT IN ('SYS','SYSTEM')
GROUP BY owner, table_name;
BEGIN
FOR c1_rec IN c1
LOOP
sql_stmnt := 'select count(*) cnt INTO :into_bind from '||c1_rec.owner_v||'.'||c1_rec.table_name_v;
EXECUTE IMMEDIATE sql_stmnt RETURN cnt_val;
DBMS_OUTPUT.PUT_LINE(c1_rec.table_name_v||' has '||cnt_val||' number of rows');
END LOOP;
END;
/
-
Good evening,
It still complains. Here is the complaint.
Error(17,37): PLS-00103: Encountered the symbol "CNT_VAL" when expecting one of the following: into bulk The symbol "into" was substituted for "CNT_VAL" to continue.
The compiler is saying from the message above cnt_val is to be replaced with INTO.
Regards,
-
Hi Everyone,
This works. See below:
table row count by owner query using dynamic sql
CREATE THE PROCEDURE:
create or replace
PROCEDURE SCHEMA_CNT
IS
cursor c1 is select owner, table_name from dba_tables
where owner not in ('SYS','SYSTEM','DBSNMP','STREAMADMIN',
'OUTLN','MGMT_VIEW','MDSYS','ORDSYS','EXFSYS','WMSYS','APPQOSSYS',
'OWBSYS_AUDIT','ORDDATA','CTXSYS','ANONYMOUS','SYSMAN','XDB','ORDPLUGINS',
'OWBSYS','SI_INFORMTN_SCHEMA','OLAPSYS','ORACLE_OCM','XS','BI','PM',
'MDDATA','IX','SH','DIP','OE','REPADMIN','APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR','APEX_030200')
ORDER BY owner, table_name;
sql_stmnt varchar2(4000);
cnt_val number;
begin
for c1_rec in c1 loop
sql_stmnt := 'select count(*) from '||c1_rec.owner||'.'||c1_rec.table_name;
execute immediate sql_stmnt into cnt_val;
dbms_output.put_line(c1_rec.owner||' '||c1_rec.table_name||' '||cnt_val);
end loop;
end;
/
SET THE ENVIRONMENT:
SET SERVEROUTPUT ON;
begin
DBMS_OUTPUT.ENABLE(10000000);
end;
/
SAVE THE OUTPUT:
Spool the output to a file.
Sqlplus >spool schema_cnt.txt
The output will be found in the oracle home directory as schema_cnt.txt
Issue the command at the sql prompt:
Sql> EXECUTE SCHEMA_CNT1;
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
Stop the spooling:
Sqlplus > spool off;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|