Re: PL-SQL Problem

From: Joydeep Chakrabarty <c.joydeep_at_gmail.com>
Date: Sat, 02 Apr 2011 23:38:37 +0530
Message-ID: <4d97662c$0$23757$14726298_at_news.sunsite.dk>

After serious thinking Luuk wrote :

> 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

Thanks ! It worked.

Thanks,
Jaydip Received on Sat Apr 02 2011 - 13:08:37 CDT