Syntax problem Oracle

Login or Register to Reply

 
Thread Tools Search this Thread
# 1  
Syntax problem Oracle

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:
Code:
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


Code:
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;
/


Last edited by rbatte1; 07-19-2017 at 07:00 AM.. Reason: Added ICODE tags
# 2  
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.

Using PL/SQL Collections and Records

Code:
SYS@test AS SYSDBA> 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 FROM user_tables ;
  7  BEGIN
  8     OPEN c1;
  9     FOR I IN 1..7
 10     LOOP
 11        FETCH c1 INTO tab_var;
 12     DBMS_OUTPUT.PUT_LINE('c1%ROWCOUNT: '||c1%ROWCOUNT );
 13        DBMS_OUTPUT.PUT_LINE(tab_var);
 14  -- select count(*) into ct_var from tab_var;
 15  -- dbms_output.put_line('There are ' || ct_var || 'rows in' || tab_var 'table');
 16     END LOOP;
 17  END;
 18  /

Procedure created.

Elapsed: 00:00:00.01
SYS@test AS SYSDBA>
SYS@test AS SYSDBA> exec TAB_ROW_COUNT;
c1%ROWCOUNT: 1
TAB$
c1%ROWCOUNT: 2
CLU$
c1%ROWCOUNT: 3
IND$
c1%ROWCOUNT: 4
ICOL$
c1%ROWCOUNT: 5
COL$
c1%ROWCOUNT: 6
LOB$
c1%ROWCOUNT: 7
COLTYPE$

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05

# 3  
Morning
This output is not what I was looking for. The correct answer can be found in the user_ tables.

connect as HR
Code:
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.

Regards

Last edited by Scott; 07-20-2017 at 09:01 AM.. Reason: Code tags, please..
# 4  
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.

Code:
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

# 5  
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:
Code:
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 Smilie

Last edited by cero; 07-20-2017 at 10:17 AM..
This User Gave Thanks to cero For This Post:
# 6  
Hi Cero,

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.

Code:
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:
Code:
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.

It was fun.

Last edited by Scott; 07-20-2017 at 11:32 AM.. Reason: Please use code tags for output, too. Thanks.
Login or Register to Reply

|
Thread Tools Search this Thread
Search this Thread:
Advanced Search

More UNIX and Linux Forum Topics You Might Find Helpful
Problem with if-else syntax
wbrunc
I'm calling the following if-else from nawk. But I keep getting an error at the "else". I've tried putting more brackets and ; but still I get complaints about the "else". Any ideas ? Thanks, wbrunc BEGIN { FS = "," ; OFS = "," } { if ( $8 ~ /A/ && $9 == B ) $1="4/29/2013" ; $2="J.Doe"...... Shell Programming and Scripting
2
Shell Programming and Scripting
Problem with awk syntax
sudvishw
Hi, Below is the code I am using. I am trying to list only those numbers which has a + symbol in it cat num | awk -F"+" '{if (/^$/) { } else {if ( $0 ~ egrep "^+$" ) { if ( $0 ~ grep "+" ) {print $0} } }}' I am getting the following error: awk: 0602-521 There is a...... Shell Programming and Scripting
7
Shell Programming and Scripting
Oracle like syntax required
ShellBoy
m kinda new to unix. i have been trying to write a script where i am trying to switch between users. but the problem is that the syntax like USERNAME/PASSWORD (like oracle SCOT/TIGER) is not working. if i write su USERNAME then the script goes to the command prompt and asks for user to enter...... UNIX for Dummies Questions & Answers
0
UNIX for Dummies Questions & Answers
syntax problem
rajan_ka1
Dear friends, I am writing shell script in csh . i want to make arthimatic operation in csh. i wrote sysntax like this. set val = 230 set tmp = `0.1 * $val + 300` echo $tmp but it is not working . anyone please give me syntax.... Shell Programming and Scripting
3
Shell Programming and Scripting
syntax problem
rajan_ka1
dear friends, I have a large size file containg two fields data like this *** **** 122 222 ***** ***** ***** ***** 232 233 i have file like this. i want to remove blank lines from file . i think awk is servive this problem i wrote a awk command but the error is...... Shell Programming and Scripting
3
Shell Programming and Scripting