Re: PL-SQL Problem

From: Luuk <Luuk_at_invalid.lan>
Date: Sat, 02 Apr 2011 19:31:02 +0200
Message-ID: <4d975d52$0$41114$e4fe514c_at_news.xs4all.nl>

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/B12037_01/server.101/b10771/scripts003.htm

-- 
Luuk

Received on Sat Apr 02 2011 - 12:31:02 CDT