Hi All,
I have a syntax problem with a procedure in oracle. I am looking to just produce the number of rows
from each table located in the HR schema nothing complex. This procedure works great up to dbms_output.put_line(tab_var); where it lists the names of each table in the user schema.
My problem lies in the next line: select count(*) into ct_var from tab_var; which produces the
following error:
LINE/COL ERROR
-------- -----------------------------------------------------------------
19/2 PL/SQL: SQL Statement ignored
19/35 PL/SQL: ORA-00942: table or view does not exist
I am thinking with a fresh pair of eyes someone can see where the sytax error is. This is just
for practice.
Any suggestions would be appreciated.
Created as the HR user in the HR schema. Not sys or system
CREATE OR REPLACE PROCEDURE TAB_ROW_COUNT
AS
tab_var varchar2(4000);
ct_var number;
cursor c1 is select table_name from user_tables ;
begin
open c1;
for i in 1..7 loop
fetch c1 into tab_var;
dbms_output.put_line(tab_var);
--select count(*) into ct_var from tab_var;
--dbms_output.put_line('There are ' || ct_var || 'rows in' || tab_var 'table');
end loop;
end;
/
I am an Oracle DBA who has been writing PL/SQL for over 15 years. You should not need to learn explicit cursors. Perhaps your instructor wants you to understand explicit cursors but you should use implicit cursors instead. You can't select from an explicit cursor so that line won't work. You can use the c1%ROWCOUNT attribute. You may want to look at PL/SQL collections.
Morning
This output is not what I was looking for. The correct answer can be found in the user_ tables.
connect as HR
SQL> col table_name format a30
select table_name, num_rows counter
from user_tables
order by table_name;SQL> 2 3
TABLE_NAME COUNTER
------------------------------ ----------
COUNTRIES 25
DEPARTMENTS 27
EMPLOYEES 107
JOBS 19
JOB_HISTORY 10
LOCATIONS 23
REGIONS 4
7 rows selected.
Sorry if you misunderstood what I was asking but you made a good point about explicit and implicit cursors which I will read up on. I was writing this procedure just to get this same answer but at a different way.
Here is the updated code with the implicit cursor. You can take out the filter "WHERE rownum < 8". Let me know what your grade is. I should get some credit.
sys@test> CREATE OR REPLACE PROCEDURE TAB_ROW_COUNT
2 AS
3 tab_var VARCHAR2(4000);
4 ct_var NUMBER;
5 CURSOR c1 IS
6 SELECT table_name
7 FROM user_tables
8 WHERE rownum < 8;
9 BEGIN
10 FOR tab_var IN c1
11 LOOP
12 EXECUTE IMMEDIATE 'SELECT COUNT(*) cnt FROM '||tab_var.table_name INTO ct_var;
13 DBMS_OUTPUT.PUT_LINE('There are ' || TO_CHAR(ct_var, '999,999') || ' rows in ' || tab_var.table_name || ' table');
14 END LOOP;
15 END;
16 /
Procedure created.
Elapsed: 00:00:00.02
sys@test>
sys@test> EXEC TAB_ROW_COUNT;
There are 4,119 rows in TAB$ table
There are 10 rows in CLU$ table
There are 0 rows in FET$ table
There are 0 rows in UET$ table
There are 12,042 rows in SEG$ table
There are 29 rows in UNDO$ table
There are 13 rows in TS$ table
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
Your goal is to loop through all tables in schema HR, count the records and display the results? The reason your procedure shows the error is because tab_var and not the content of the variable with that name is treated as the tablename for the SELECT statement.
You'll have to use dynamic SQL for your task:
CREATE OR REPLACE PROCEDURE tab_row_count
AS
v_row_count NUMBER;
BEGIN
FOR c_tables IN (SELECT table_name
FROM user_tables)
LOOP
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || c_tables.table_name INTO v_row_count;
dbms_output.put_line('There are ' || v_row_count || ' rows in table ' || c_tables.table_name);
END LOOP;
END;
/
EDIT: seems like Gandof989 was a few minutes faster but came to the same conclusion
Thanks for your help on this. You were in the ball park and I used your execute immediate statement. Here is the final solution that I was looking for.
CREATE OR REPLACE PROCEDURE TAB_ROW_COUNT
AS
ct_var number;
tab_rec varchar2(4000);
cursor c1 is
select table_name from user_tables ;
c1_rec c1%ROWTYPE;
BEGIN
OPEN c1;
loop
fetch c1 into c1_rec;
exit when c1%NOTFOUND;
execute immediate 'select count(*) from '||c1_rec.table_name into ct_var;
dbms_output.put_line('There are '||TO_CHAR(ct_var,'999,999')||' rows in '||c1_rec.table_name||' table.');
end loop;
close c1;
END;
/
output:
SQL> execute tab_row_count;
There are 4 rows in REGIONS table.
There are 25 rows in COUNTRIES table.
There are 23 rows in LOCATIONS table.
There are 27 rows in DEPARTMENTS table.
There are 19 rows in JOBS table.
There are 107 rows in EMPLOYEES table.
There are 10 rows in JOB_HISTORY table.
PL/SQL procedure successfully completed.
This output would have been the same return as the select statement:
select table_name, num_rows from user_tables;
Yes I am a OCP for the last 15 years and don't get to do any of this and am learning this on my own...again.
