Unix/Linux Go Back    


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

Syntax problem Oracle

UNIX and Linux Applications


Closed    
 
Thread Tools Search this Thread Display Modes
    #1  
Old Unix and Linux 07-18-2017   -   Original Discussion by bdby
bdby's Unix or Linux Image
bdby bdby is offline
Registered User
 
Join Date: Sep 2012
Last Activity: 25 October 2017, 12:58 PM EDT
Posts: 28
Thanks: 2
Thanked 1 Time in 1 Post
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
Sponsored Links
    #2  
Old Unix and Linux 07-19-2017   -   Original Discussion by bdby
gandolf989's Unix or Linux Image
gandolf989 gandolf989 is offline
Registered User
 
Join Date: Sep 2006
Last Activity: 17 April 2018, 4:24 PM EDT
Location: Pittsburgh
Posts: 263
Thanks: 4
Thanked 48 Times in 47 Posts
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

Sponsored Links
    #3  
Old Unix and Linux 07-20-2017   -   Original Discussion by bdby
bdby's Unix or Linux Image
bdby bdby is offline
Registered User
 
Join Date: Sep 2012
Last Activity: 25 October 2017, 12:58 PM EDT
Posts: 28
Thanks: 2
Thanked 1 Time in 1 Post
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 Unix and Linux 07-20-2017   -   Original Discussion by bdby
gandolf989's Unix or Linux Image
gandolf989 gandolf989 is offline
Registered User
 
Join Date: Sep 2006
Last Activity: 17 April 2018, 4:24 PM EDT
Location: Pittsburgh
Posts: 263
Thanks: 4
Thanked 48 Times in 47 Posts
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

Sponsored Links
    #5  
Old Unix and Linux 07-20-2017   -   Original Discussion by bdby
cero's Unix or Linux Image
cero cero is offline
Registered User
 
Join Date: Aug 2006
Last Activity: 22 May 2018, 9:56 AM EDT
Posts: 466
Thanks: 4
Thanked 102 Times in 94 Posts
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 Linux

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)
Sponsored Links
    #6  
Old Unix and Linux 07-20-2017   -   Original Discussion by bdby
bdby's Unix or Linux Image
bdby bdby is offline
Registered User
 
Join Date: Sep 2012
Last Activity: 25 October 2017, 12:58 PM EDT
Posts: 28
Thanks: 2
Thanked 1 Time in 1 Post
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.
Sponsored Links
Closed

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Linux 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
Problem with if-else syntax wbrunc Shell Programming and Scripting 2 04-29-2013 04:18 PM
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 01:19 AM.