Table not found at cursor%NOTFOUND Oracle

Question

I was asked to migrate this code from Sql server

It's used to count all rows with a cursor in a database and we need it to work both on sql server and oracle. The sql server part is working fine, but the oracle part i can't figure out.


DECLARE @tablename VARCHAR(500)
DECLARE @rowname VARCHAR(500)
DECLARE @sql AS NVARCHAR(1000) 


IF OBJECT_ID('tempdb..#Temp1') IS NOT NULL
    DROP TABLE #Temp1

CREATE TABLE #Temp1
        (
          tablename varchar(max) ,
          rowCounter NUMERIC(38) ,
         idColumn varchar(max),
         SumIdCol NUMERIC(38)
       );

DECLARE db_cursor CURSOR FOR 
SELECT s.name + '.' + o.name as name, c.COLUMN_NAME
FROM sys.all_objects o
join sys.schemas s on o.schema_id=s.schema_id
join INFORMATION_SCHEMA.COLUMNS c on o.name=c.TABLE_NAME
WHERE type = 'U' and s.name not like 'sys' and c.ORDINAL_POSITION=1 and c.DATA_TYPE='int'

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @tablename,@rowname

WHILE @@FETCH_STATUS = 0  
BEGIN  
      SET @sql = 
'     
      insert into #Temp1 values('+
      ''''+@tablename+''','+
      '(SELECT count(1) FROM '+@tablename+' ),'+
        ''''+@rowname+''''+','+
        '(SELECT SUM(['+@rowname+']) FROM '+@tablename+' )'+
      ')'
    EXEC sp_executesql @sql
    FETCH NEXT FROM db_cursor INTO @tablename,@rowname

END 

CLOSE db_cursor  
DEALLOCATE db_cursor 


select * from #Temp1
IF OBJECT_ID('tempdb..#Temp1') IS NOT NULL
    DROP TABLE #Temp1

Into oracle. I decided to take on PL/SQL and wrote this:

DECLARE 
v_tablename varchar2(400); 
v_rowname varchar2(400); 


cursor db_cursor is 
select ALL_OBJECTS.OWNER || '.' || USER_TABLES.TABLE_NAME AS Tablename, USER_TAB_COLS.COLUMN_NAME AS columnName
from USER_TABLES
inner join user_tab_cols ON USER_TAB_COLS.TABLE_NAME=USER_TABLES.TABLE_NAME
inner join ALL_OBJECTS ON user_tab_cols.TABLE_NAME=ALL_OBJECTS.OBJECT_NAME
WHERE ALL_OBJECTS.OBJECT_TYPE = 'TABLE' AND USER_TAB_COLS.COLUMN_ID = 1;

begin 

open db_cursor; 
loop
    fetch db_cursor into v_tablename,v_rowname; 
    EXIT WHEN db_cursor%NOTFOUND;
    execute immediate 'INSERT INTO Temp1(tablename,rowCounter,idColumn,SumIdCol) 
    SELECT ''' || v_tablename || ''' ,COUNT(*), ''' || v_rowname ||''',SUM(''' || v_rowname ||''')    FROM ' || v_tablename; 
end loop;
CLOSE db_cursor; 

END;

The issue i have is that i get Invalid table or view at line 42 (This line is EXIT WHEN db_cursor%NOTFOUND;)

I know this solution isn't the best but any help would be appreciated. Thanks

Littlefoot · Answer

I'm not quite sure what are the last two columns in the temp1 table supposed to contain; the first column (why?) and some "sum" value, but - which one?

Anyway: in Oracle, you might start with this code (that compiles and does something; could/should be improved, perhaps):

Target table:

SQL> create table temp1
  2    (tablename  varchar2(70),
  3     rowcounter number,
  4     idcolumn   varchar2(30),
  5     sumidcol   number
  6    );

Table created.

Anonymous PL/SQL block; note that you need just all_tables and all_tab_columns as they contain all info you need. If you use all_objects, you have to filter tables only (so, why use it at all?).

As all_ views contain all tables/columns you have access to, I filtered only tables owned by user SCOTT because - if I left them all, I'd get various owners (such as SYS and SYSTEM - do you need them too?) and 850 tables.

SQL> select count(distinct owner) cnt_owner, count(*) cnt_tables
  2  from all_tables;

 CNT_OWNER CNT_TABLES
---------- ----------
        19        850

SQL>

Maybe you'd actually want to use user_ views instead.

This code lists all tables, the first column and number of rows in each table:

SQL> declare
  2    l_cnt number;
  3  begin
  4    for cur_r in (select a.owner,
  5                         a.table_name,
  6                         c.column_name
  7                  from all_tables a join all_tab_columns c on c.owner = a.owner
  8                                                          and c.table_name = a.table_name
  9                  where c.column_id = 1
 10                    and a.owner in ('SCOTT')
 11                 )
 12    loop
 13      execute immediate 'select count(*) from ' || cur_r.owner ||'.'||
 14        '"' || cur_r.table_name ||'"' into l_cnt;
 15
 16      insert into temp1 (tablename, rowcounter, idcolumn, sumidcol)
 17        values (cur_r.owner ||'.'||cur_r.table_name, l_cnt, cur_r.column_name, null);
 18    end loop;
 19  end;
 20  /

PL/SQL procedure successfully completed.

Result is then:

SQL> select * from temp1 where rownum <= 10;

TABLENAME                           ROWCOUNTER IDCOLUMN          SUMIDCOL
----------------------------------- ---------- --------------- ----------
SCOTT.ACTIVE_YEAR                            1 YEAR
SCOTT.BONUS                                  0 ENAME
SCOTT.COPY_DEPARTMENTS                       1 DEPARTMENT_ID
SCOTT.DAT                                    0 DA
SCOTT.DEPARTMENTS                            1 DEPARTMENT_ID
SCOTT.DEPT                                   4 DEPTNO
SCOTT.DEPT_BACKUP                            4 DEPTNO
SCOTT.EMP                                   14 EMPNO
SCOTT.EMPLOYEE                               4 EMP_ID
SCOTT.EMPLOYEES                              9 EMPLOYEE_ID

10 rows selected.

SQL>

MT0 · Answer

You are getting the Table not found exception because you have lower-case table names and you are using unquoted identifiers, which Oracle will implicitly convert to upper-case and then because they are upper- and not lower-case they are not found.

You need to use quoted identifiers so that Oracle respects the case-sensitivity in the table names. You can also use an implicit cursor and can pass the values using bind variables (where possible) rather than using string concatenation:

BEGIN
  FOR rw IN (SELECT t.owner,
                    t.table_name,
                    c.column_name
             FROM   all_tables t
                    INNER JOIN all_tab_cols c
                    ON (t.owner = c.owner AND t.table_name = c.table_name)
             WHERE  t.owner = USER
             AND    c.column_id = 1)
  LOOP
    EXECUTE IMMEDIATE 'INSERT INTO Temp1(tablename,rowCounter,idColumn)
       SELECT :1, COUNT(*), :2 FROM "' || rw.owner || '"."' || rw.table_name || '"'
       USING rw.table_name, rw.column_name; 
  END LOOP;
END;
/

fiddle

Table not found at cursor%NOTFOUND Oracle

2 Answers