2 Replies Latest reply: Mar 20, 2013 10:04 AM by 998093 RSS

    Please help on PL/SQL

    997653
      Please help me to this PL/SQL command. I want to count(*) from table i got on DEVCHG.REFRESH_TBL_LIST.

      declare
      cnt NUMBER;
      begin
      for tbl in (
           select table_name from DEVCHG.REFRESH_TBL_LIST
           )
      loop
           select count(*) into cnt from tbl;
           DBMS_OUTPUT.PUT_LINE(tbl.table_name||':'||cnt);
      end loop;
      end;
      /
        • 1. Re: Please help on PL/SQL
          scott.wesley
          This is probably best asked in the [url http://forums.oracle.com/forums/forum.jspa?forumID=75]PL/SQL forum, but you are on the right track.

          Replace your select into with some dynamic SQL
          http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/dynamic.htm#CHDGJEGD

          Scott
          • 2. Re: Please help on PL/SQL
            998093
            Hi, try this PL/SQL block. I hope your problem will resolved by this. Revert back with query if any....

            set serveroutput on
            declare
            cnt NUMBER;
            begin
            select count(*) into cnt from DEVCHG.REFRESH_TBL_LIST;

            IF SQL%FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Total count of table is ' || cnt);
            ELSE
            DBMS_OUTPUT.PUT_LINE('No record found');
            END IF;
            end;
            /