PL-SQL Problem

#1

Hi,

I am using PL-SQL code to find out the number of rows in the tables in
the user XYZ.

set serveroutput on;
DECLARE
sqlstr VARCHAR2(1000);
cnt NUMBER;
BEGIN
FOR v_rec IN (SELECT table_name FROM all_tables WHERE OWNER =
'XYZ') loop
sqlstr := 'select count(*) into cnt from ' || v_rec.table_name
;
-- DBMS_OUTPUT.PUT_LINE(sqlstr);
EXECUTE IMMEDIATE sqlstr;
DBMS_OUTPUT.PUT_LINE(v_rec.table_name ||',' || cnt);
END LOOP;
END;

I am getting ORA-00905 "Missing Keyword" error.
Please help. Thanks in advance,

Jaydip

#2

On 02-04-2011 16:03, Joydeep Chakrabarty wrote:

Quote:

set serveroutput on;
DECLARE
sqlstr VARCHAR2(1000);
cnt NUMBER;
BEGIN
FOR v_rec IN (SELECT table_name FROM all_tables WHERE OWNER = 'XYZ')
loop
sqlstr := 'select count(*) into cnt from ' || v_rec.table_name ;
-- DBMS_OUTPUT.PUT_LINE(sqlstr);
EXECUTE IMMEDIATE sqlstr;
DBMS_OUTPUT.PUT_LINE(v_rec.table_name ||',' || cnt);
END LOOP;
END;

set serveroutput on;
DECLARE
cursor c1 is
SELECT table_name FROM all_tables WHERE OWNER = 'XYZ';
cnt NUMBER := 0;
BEGIN
FOR v_rec IN c1 loop
EXECUTE IMMEDIATE 'select count(*) from ' || v_rec.table_name
INTO cnt;
DBMS_OUTPUT.PUT_LINE(v_rec.table_name ||',' || cnt);
END LOOP;
END;

--
Luuk

#3

Luuk wrote :

Quote:

On 02-04-2011 16:03, Joydeep Chakrabarty wrote:
set serveroutput on;
DECLARE
sqlstr VARCHAR2(1000);
cnt NUMBER;
BEGIN
FOR v_rec IN (SELECT table_name FROM all_tables WHERE OWNER = 'XYZ')
loop
sqlstr := 'select count(*) into cnt from ' || v_rec.table_name ;
-- DBMS_OUTPUT.PUT_LINE(sqlstr);
EXECUTE IMMEDIATE sqlstr;
DBMS_OUTPUT.PUT_LINE(v_rec.table_name ||',' || cnt);
END LOOP;
END;

set serveroutput on;
DECLARE
cursor c1 is
SELECT table_name FROM all_tables WHERE OWNER = 'XYZ';
cnt NUMBER := 0;
BEGIN
FOR v_rec IN c1 loop
EXECUTE IMMEDIATE 'select count(*) from ' || v_rec.table_name
INTO cnt;
DBMS_OUTPUT.PUT_LINE(v_rec.table_name ||',' || cnt);
END LOOP;
END;

It's not displaying any output.

Thanks,
Jaydip

#4

On 02-04-2011 18:44, Joydeep Chakrabarty wrote:

Quote:

Luuk wrote :
On 02-04-2011 16:03, Joydeep Chakrabarty wrote:
set serveroutput on;
DECLARE
sqlstr VARCHAR2(1000);
cnt NUMBER;
BEGIN
FOR v_rec IN (SELECT table_name FROM all_tables WHERE OWNER = 'XYZ')
loop
sqlstr := 'select count(*) into cnt from ' || v_rec.table_name ;
-- DBMS_OUTPUT.PUT_LINE(sqlstr);
EXECUTE IMMEDIATE sqlstr;
DBMS_OUTPUT.PUT_LINE(v_rec.table_name ||',' || cnt);
END LOOP;
END;

set serveroutput on;
DECLARE
cursor c1 is
SELECT table_name FROM all_tables WHERE OWNER = 'XYZ';
cnt NUMBER := 0;
BEGIN
FOR v_rec IN c1 loop
EXECUTE IMMEDIATE 'select count(*) from ' || v_rec.table_name
INTO cnt;
DBMS_OUTPUT.PUT_LINE(v_rec.table_name ||',' || cnt);
END LOOP;
END;

It's not displaying any output.

Thanks,
Jaydip

And did it give any errors?

If not, than probably you forgot to change line 3
"WHERE OWNER = 'XYZ';"
to the correct owner?

I did test this on the HR [1] database (10g XE) using Oracle SQL
Developer 2.1.1.64

[1]:
http://download.oracle.com/docs/cd/B...scripts003.htm
--
Luuk

#5

On Apr 2, 12:44*pm, Joydeep Chakrabarty <c.joyd... (AT) gmail (DOT) com> wrote:

Quote:

Luuk wrote :

On 02-04-2011 16:03, Joydeep Chakrabarty wrote:
set serveroutput on;
DECLARE
* *sqlstr * *VARCHAR2(1000);
* *cnt * * * *NUMBER;
BEGIN
* *FOR v_rec IN (SELECT table_name FROM all_tables WHERE OWNER ='XYZ')
loop
* * * *sqlstr := 'select count(*) into cnt from ' || v_rec.table_name ;
-- * * *DBMS_OUTPUT.PUT_LINE(sqlstr);
* * * *EXECUTE IMMEDIATE sqlstr;
* * * *DBMS_OUTPUT.PUT_LINE(v_rec.table_name ||',' || cnt);
* *END LOOP;
END;

set serveroutput on;
DECLARE
* *cursor c1 is
* * * SELECT table_name FROM all_tables WHERE OWNER = 'XYZ';
* *cnt * * * *NUMBER := 0;
BEGIN
* *FOR v_rec IN c1 loop
* * * * EXECUTE IMMEDIATE 'select count(*) from ' || v_rec.table_name
INTO cnt;
* * * *DBMS_OUTPUT.PUT_LINE(v_rec.table_name ||',' || cnt);
* *END LOOP;
END;

It's not displaying any output.

Thanks,
Jaydip- Hide quoted text -

- Show quoted text -

Jaydip, by any chance did you miss having the "serveroutput on"
statement when you tested Luuk's code?

Here is another example of using execute into:

Is there a simple way to produce a report of all tables in the
database with current number of rows ?
http://www.jlcomp.demon.co.uk/faq/count_all_rows.html

HTH -- Mark D Powell --

#6

After serious thinking Luuk wrote :

Quote:

On 02-04-2011 18:44, Joydeep Chakrabarty wrote:
Luuk wrote :
On 02-04-2011 16:03, Joydeep Chakrabarty wrote:
set serveroutput on;
DECLARE
sqlstr VARCHAR2(1000);
cnt NUMBER;
BEGIN
FOR v_rec IN (SELECT table_name FROM all_tables WHERE OWNER = 'XYZ')
loop
sqlstr := 'select count(*) into cnt from ' || v_rec.table_name ;
-- DBMS_OUTPUT.PUT_LINE(sqlstr);
EXECUTE IMMEDIATE sqlstr;
DBMS_OUTPUT.PUT_LINE(v_rec.table_name ||',' || cnt);
END LOOP;
END;

set serveroutput on;
DECLARE
cursor c1 is
SELECT table_name FROM all_tables WHERE OWNER = 'XYZ';
cnt NUMBER := 0;
BEGIN
FOR v_rec IN c1 loop
EXECUTE IMMEDIATE 'select count(*) from ' || v_rec.table_name
INTO cnt;
DBMS_OUTPUT.PUT_LINE(v_rec.table_name ||',' || cnt);
END LOOP;
END;

It's not displaying any output.

Thanks,
Jaydip

And did it give any errors?

If not, than probably you forgot to change line 3
"WHERE OWNER = 'XYZ';"
to the correct owner?

I did test this on the HR [1] database (10g XE) using Oracle SQL
Developer 2.1.1.64

[1]:
http://download.oracle.com/docs/cd/B...scripts003.htm

Thanks ! It worked.

Thanks,
Jaydip

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

PL-SQL Problem

comp.databases.oracle.misc comp.databases.oracle.misc

PL-SQL Problem - 04-02-2011 , 09:03 AM

Re: PL-SQL Problem - 04-02-2011 , 10:21 AM

Re: PL-SQL Problem - 04-02-2011 , 11:44 AM

Re: PL-SQL Problem - 04-02-2011 , 12:31 PM

Re: PL-SQL Problem - 04-02-2011 , 12:45 PM

Re: PL-SQL Problem - 04-02-2011 , 01:08 PM