Syntax problem Oracle

 
Thread Tools Search this Thread
Special Forums UNIX and Linux Applications Syntax problem Oracle
# 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 08: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 10: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 Smilie

Last edited by cero; 07-20-2017 at 11:17 AM..
This User Gave Thanks to cero For This Post:
# 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 12:32 PM.. Reason: Please use code tags for output, too. Thanks.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk problem with syntax

awk -v sw="lemons|dogs" 'NR>100 && NR<200 BEGIN { c=split(sw,a,""); } { for (w in a) { if ($0 ~ a) d]++; } } END { for (i in a) { o=o (a"="(d]?d]:0)","); } sub(",*$","",o); print o; }' /home/jahitt/data.txt what am i doing wrong with the above code? im pretty sure the issue is in the... (6 Replies)
Discussion started by: SkySmart
6 Replies

2. Shell Programming and Scripting

awk syntax problem

Hi, I am using this awk command in my shell script : find . -name "*" -ctime -6 | xargs cat | grep -E -v ^fileName\|^\(\) | awk -v DATE="${CURR_DATE}" -v DATE_LOG=$DATE_SYS 'BEGIN {FS=";";OFS=";";CONVFMT="%.9g";OFMT="%.9g"}... (4 Replies)
Discussion started by: abhi1988sri
4 Replies

3. Shell Programming and Scripting

Problem with if-else syntax

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"... (2 Replies)
Discussion started by: wbrunc
2 Replies

4. Shell Programming and Scripting

Syntax Problem with awk

Hello, I have perl script,which take some part of data in the file. the below command works fine in normal cmd prompt. `awk '/CDI/ && // && !/Result for/ {print $3 $5 > "final.txt"}' datalist.txt`; `nawk -F"" '{print $2}' finalcdi.txt`; But not working. Please use code tags, thanks. (5 Replies)
Discussion started by: rasingraj
5 Replies

5. Shell Programming and Scripting

CShell Syntax Problem

Hi guys, Basically I'm trying to write a CShell script that calls an awk script on a given directory (given in command-line). I keep getting a syntax error with my code though: #!/bin/csh set dir = $ARGV foreach file ( $dir/* ) set output = 'awk -f /Desktop/aal $file' echo... (3 Replies)
Discussion started by: ROFL
3 Replies

6. Shell Programming and Scripting

Help for Sed Syntax problem

I have one File named "txt_file" # cat txt_file <DBType>RT</DBType> <AppType>RT</AppType> -------------------------------------------------- I want replace "<AppType>RT</AppType>" to <AppType>XY</AppType> in txt_file and output redirect to Newfile ... (2 Replies)
Discussion started by: SanjayLinux
2 Replies

7. Shell Programming and Scripting

syntax problem grepping?

I am calculating a time and appending a space in front of it to get only certain records in a file because the times are represented in HH:II:SS format and I don't want to see anything other than the actual hour and minute combination (hence appending the space to the front of the time). My... (9 Replies)
Discussion started by: dsimpg1
9 Replies

8. UNIX for Dummies Questions & Answers

Oracle like syntax required

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... (0 Replies)
Discussion started by: ShellBoy
0 Replies

9. Shell Programming and Scripting

syntax problem

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. (3 Replies)
Discussion started by: rajan_ka1
3 Replies

10. Shell Programming and Scripting

syntax problem

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... (3 Replies)
Discussion started by: rajan_ka1
3 Replies
Login or Register to Ask a Question