Select count(*) IN ORACLE

Question

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;

Answer 1

The dynamic counting logic can be written more cleanly as

declare
    tname      varchar(255);
    count_sql  varchar2(500);
begin
    for r in (
        select table_name, num_rows from user_tables -- where table_name like '%VTS'
    )
    loop
        count_sql := 'select count(*) from ' || r.table_name || ' where rownum = 1';

        execute immediate count_sql into r.num_rows;

        dbms_output.put_line('Number of rows in '||r.table_name||' = '||r.num_rows);
    end loop;
end;

In the Oracle HR sample schema, that gives output as

Number of rows in REGIONS = 1
Number of rows in COUNTRIES = 1
Number of rows in LOCATIONS = 1
Number of rows in DEPARTMENTS = 1
Number of rows in JOBS = 1
Number of rows in EMPLOYEES = 1
Number of rows in JOB_HISTORY = 1

The part about updating every varchar2 column is more complicated, because you can't just fetch the varchar2 column for each table if there is more than one varchar2 column. For example, say table locations has street_address, postal_code, city and state_province. You could loop through each row in user_tab_columns and generate four statements as

update locations set street_address = 'hello'
update locations set postal_code = 'hello'
update locations set city = 'hello'
update locations set state_province = 'hello'

but of course that would be horribly inefficient when what you really wanted was

update locations
set    street_address = 'hello'
     , postal_code = 'hello'
     , city = 'hello'
     , state_province = 'hello'

You can generate this of course, but it's more complicated. Perhaps something like this (I have replaced execute immediate with dbms_output.put_line so I can just preview the generated SQL without executing it):

declare
    tname      varchar(255);
    count_sql  varchar2(500);
    update_sql varchar2(500);
    l_separator varchar2(2);
begin
    for t in (
        select table_name, num_rows from user_tables -- where table_name like '%VTS'
    )
    loop
        count_sql := 'select count(*) from ' || t.table_name || ' where rownum = 1';

        execute immediate count_sql into t.num_rows;

        dbms_output.put_line('Number of rows in '||t.table_name||' = '||t.num_rows);

        if t.num_rows > 0 then
            update_sql := 'update '||t.table_name||' set ';

            l_separator := '';

            for c in (
                select column_name
                from   user_tab_columns
                where  table_name = t.table_name
                and    table_name not in (select view_name from user_views)
                and    data_type = 'VARCHAR2'
                and    data_length > 4
            )
            loop
                update_sql := update_sql || l_separator || c.column_name||' = ''hello''';
                l_separator := ', ';
            end loop;

            --execute immediate update_sql;
            dbms_output.put_line(update_sql);            
        end if; 

    end loop;
end;

which gives me:

Number of rows in REGIONS = 1
update REGIONS set REGION_NAME = 'hello'
Number of rows in COUNTRIES = 1
update COUNTRIES set COUNTRY_NAME = 'hello'
Number of rows in LOCATIONS = 1
update LOCATIONS set STREET_ADDRESS = 'hello', POSTAL_CODE = 'hello', CITY = 'hello', STATE_PROVINCE = 'hello'
Number of rows in DEPARTMENTS = 1
update DEPARTMENTS set DEPARTMENT_NAME = 'hello'
Number of rows in JOBS = 1
update JOBS set JOB_ID = 'hello', JOB_TITLE = 'hello'
Number of rows in EMPLOYEES = 1
update EMPLOYEES set FIRST_NAME = 'hello', LAST_NAME = 'hello', EMAIL = 'hello', PHONE_NUMBER = 'hello', JOB_ID = 'hello'
Number of rows in JOB_HISTORY = 1
update JOB_HISTORY set JOB_ID = 'hello'

Answer 2

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:

1. You don't need rownum = 1 when you select just COUNT(*).
2. You need better naming for the variables.