Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> procedure syntax problem
bdby
post Jul 19 2017, 08:49 AM
Post #1


Newbie
*

Group: Members
Posts: 1
Joined: 19-July 17
Member No.: 52,085



Hi All,

I am using Solaris 10 on a virtual machine windows 10. Oracle 12c. I log in as the user HR in the
example schema HR provided by Oracle.

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 upto
dbms_output.put_line(tab_var); where it lists the names of each table in the user schema. Just doing
this as downtime permits on the job reviewing my technology.

My problem lies in the next line: select count(*) into ct_var from tab_var; where I get the counts for each
table for each iteration 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.


My 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;
/

Thanks in advance.

Go to the top of the page
 
+Quote Post
burleson
post Jul 19 2017, 01:29 PM
Post #2


Advanced Member
***

Group: Members
Posts: 13,492
Joined: 26-January 04
Member No.: 13



Hi bdby and welcome to the forum!

I would use dynamic SQL with "execute immediate" to get the counts!

See working example here:

http://www.dba-oracle.com/t_count_rows_all...s_in_schema.htm

In PL/SQL, try this:

http://www.dba-oracle.com/t_oracle_execute_immediate.htm

Good luck!


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
boobal_ganesan
post Jul 19 2017, 01:39 PM
Post #3


Advanced Member
***

Group: Members
Posts: 65
Joined: 6-June 16
From: India
Member No.: 51,370



Hello Mate,

Welcome to the forum!

In your below SQL, the TAB_VAR is a variable which holds the name of the tables from the USER_TABLES data dictionary.

CODE
select count(*) into ct_var from tab_var;


So, you must make it into a dynamic statement like below so that Oracle will build your SQL properly during the compilation.

CODE
execute immediate 'Select count(*) from '||tab_var into ct_var;


Now, the procedure code after the above modification would be as below,

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);

execute immediate 'Select count(*) from '||tab_var into ct_var;

dbms_output.put_line('There are ' || ct_var || 'rows in' || tab_var 'table');
end loop;
end;
/



Cheers,
Boobal Ganesan
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 22nd August 2017 - 02:23 AM