Select count(*) IN ORACLE

May 19, 2022

I’m trying to execute the below code to update tables but cannot get the count(*) result in CNT variable.

How can i get the number of record in the tables before my update please ?

The error i get executing the below code :

Error report - ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 12 06502. 00000 - "PL/SQL: numeric or value error%s"

DECLARE
tname varchar(255);
sql1 VARCHAR2(2000);
CNT INTEGER;

CURSOR myCursor IS select table_name from user_tables where table_name like '%VTS';
BEGIN
    OPEN myCursor;
    LOOP
        FETCH myCursor INTO tname;
        EXIT WHEN myCursor%NOTFOUND;
        BEGIN
          CNT:= 'SELECT COUNT(*) FROM ' || tname || ' where rownum=1';
          EXECUTE IMMEDIATE 'CNT';
         DBMS_OUTPUT.put_line( 'Number of rows = : ' || CNT);           
         IF ( CNT ) > 0 THEN 
            SELECT column_name INTO sql1 FROM user_tab_cols WHERE table_name = tname AND table_name not in (select view_name from user_views) and data_type ='VARCHAR2' ;
            sql1 := 'UPDATE ' || tname || ' SET '|| sql1 || '=''hello''';     
             EXECUTE IMMEDIATE sql1;              
        END IF; 
        END;   
    END LOOP;
    CLOSE myCursor; 
END;

>Solution :

You need the execute immediate with into clause.
Here is the adjusted procedure:

DECLARE
tname varchar(255);
sql1 VARCHAR2(2000);
sql2 VARCHAR2(1000);
CNT NUMBER;

CURSOR myCursor IS select table_name from user_tables where table_name like '%VTS';
BEGIN
    OPEN myCursor;
    LOOP
        FETCH myCursor INTO tname;
        EXIT WHEN myCursor%NOTFOUND;
        BEGIN
         sql2 := 'SELECT COUNT(*) FROM ' || tname;
         EXECUTE IMMEDIATE sql2 INTO CNT;       
         DBMS_OUTPUT.put_line( 'Number of rows = : ' || CNT);           
         IF ( CNT ) > 0 THEN 

            SELECT column_name 
              INTO sql1 
              FROM user_tab_cols
             WHERE table_name = tname 
               AND table_name not in (select view_name from user_views) 
               AND data_type = 'VARCHAR2';

           sql1 := 'UPDATE ' || tname || ' SET '|| sql1 || '=''hello''';     
           DBMS_OUTPUT.put_line( 'sql');           
         END IF; 
        END;   
    END LOOP;
    CLOSE myCursor; 
END;

Supplementary remarks: