Home Man
Search
Today's Posts
Register

Discuss UNIX and Linux software applications. This includes SQL, Databases, Middleware, MOM, SOA, EDA, CEP, BI, BPM and similar topics.

Syntax problem Oracle

Login to Reply

 
Thread Tools Search this Thread
# 1  
Old 07-18-2017
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  
Old 07-19-2017
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  
Old 07-20-2017
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  
Old 07-20-2017
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  
Old 07-20-2017
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

Last edited by cero; 07-20-2017 at 10:17 AM..
The Following User Says Thank You to cero For This Useful Post:
gandolf989 (07-20-2017)
# 6  
Old 07-20-2017
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 to Reply

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
awk problem with syntax SkySmart Shell Programming and Scripting 6 10-29-2013 02:56 PM
awk syntax problem abhi1988sri Shell Programming and Scripting 4 06-19-2013 03:22 PM
Problem with if-else syntax wbrunc Shell Programming and Scripting 2 04-29-2013 04:18 PM
Syntax Problem with awk rasingraj Shell Programming and Scripting 5 08-25-2011 08:12 AM
Problem with awk syntax sudvishw Shell Programming and Scripting 7 05-19-2011 02:32 AM
Problem with syntax using awk ajincoep Shell Programming and Scripting 6 08-19-2010 01:45 AM
Help for Sed Syntax problem SanjayLinux Shell Programming and Scripting 2 10-16-2007 01:52 AM
Oracle like syntax required ShellBoy UNIX for Dummies Questions & Answers 0 07-17-2007 02:07 AM
syntax problem rajan_ka1 Shell Programming and Scripting 3 03-16-2006 09:13 AM
syntax problem rajan_ka1 Shell Programming and Scripting 3 03-08-2006 12:04 PM


All times are GMT -4. The time now is 05:57 AM.

Unix & Linux Forums Content Copyrightę1993-2018. All Rights Reserved.
UNIX.COM Login
Username:
Password:  
Show Password