Re: PL-SQL Problem

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Sat, 2 Apr 2011 10:45:39 -0700 (PDT)
Message-ID: <52da17e8-56ac-4d0d-a1e0-084978536c5b_at_q36g2000yqn.googlegroups.com>

On Apr 2, 12:44 pm, Joydeep Chakrabarty <c.joyd..._at_gmail.com> 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- 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 -- Received on Sat Apr 02 2011 - 12:45:39 CDT