Getting ORA-00904 whle running a procedure!
446Sep 11 2012 — edited Sep 11 2012hi ,
the following procedure is meant for getting the details which has name of the user tables alone along with number of columns,but am getting -ERROR IN BLOCK:::ORA-00904: "tablename": invalid identifier.
DECLARE
v_cols NUMBER:=0;
cursor c is SELECT table_name FROM user_tables ORDER BY table_name;
BEGIN
FOR rec IN c
LOOP
v_cols:=0;
EXECUTE IMMEDIATE 'select count(COLUMN_NAME) from all_col_comments where TABLE_NAME in ('||rec.table_name||') group by TABLE_NAME' INTO v_num_rows;
DBMS_OUTPUT.PUT_LINE (RPAD (rec.table_name, 32) || LPAD (v_num_rows, 9));
END LOOP;
DBMS_OUTPUT.PUT_LINE ('-------------------End_of_Script-------------------');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR IN BLOCK:::'||SQLERRM);
END;
if there anything needs to change in procedure ,let me know.