ORA-01722: invalid number error with PL/SQL dynamic SQL code

Question

In running the code below I get the error illustrated. From what I understand, the error would indicate that there is a process whereby a non-numeric value is being converted (or at least attempted to be being converted) to a number value. I can't see that going on here.

*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at "BOB.ESTIMATE_BOB01", line 72
ORA-06512: at line 1    

create or replace procedure ESTIMATE_BOB01 is

--variables

l_dblink varchar2(100) := 'db1';
file_handle UTL_FILE.file_type;
v_ts_name varchar2(30);
v_link_name varchar2(10);
v_csv_name varchar2(100);
EST_ONE_ROW_MB NUMBER := 0;
TOTAL_ROW_COUNT NUMBER := 0;
SPACE_REQUIRED NUMBER := 0;
TOT_OBJECT_SIZE_MB NUMBER := 0;
v_Mv_name varchar2(100);
v_sql1 varchar2(1500);
cur SYS_REFCURSOR;
owner varchar2(100);
table_name varchar2(100);
driver_table varchar2(100);
mandatory_join varchar2(100);
C_TOTAL_ROW_COUNT NUMBER := 0;
v_total_driver_only varchar2(100);
--

begin


SELECT tablename into v_csv_name
FROM BOB01.BOB_new_table_tracker
WHERE
CREATED_AT = (select MAX(CREATED_AT) from BOB01.BOB_new_table_tracker);

SELECT mv_name into v_Mv_name
FROM BOB01.BOB_new_table_tracker_mv
WHERE
CREATED_AT = (select MAX(CREATED_AT) from BOB01.BOB_new_table_tracker_mv);

select link_name into v_link_name from link_and_mail where mdate = (select max(mdate) from link_and_mail);
select distinct targetschema into v_ts_name from BOB01.MV_BOB_TABLE;

v_sql1 := 'SELECT /*+ monitor parallel (4)*/ a.owner,
                        a.table_name,
                        b.driver_table,
                        b.mandatory_join,
                        sum(c.sum_bytes) AS "TOT_OBJECT_SIZE_MB",
                        (sum(c.sum_bytes)) / (:C_TOTAL_ROW_COUNT) AS "EST_ONE_ROW_MB",
                        ((sum(c.sum_bytes)) / (:C_TOTAL_ROW_COUNT)) * (:TOTAL_ROW_COUNT)AS "SPACE_REQUIRED"
                 FROM dba_tables@db1 a, MV_BOB_TABLE b, '|| v_Mv_name ||' c
                 WHERE a.table_name IN ( SELECT table_name
                                       FROM MV_BOB_TABLE
                                       WHERE driver_table IS NOT NULL
                                             AND   additional_joins IS NULL
                                     )
                 AND   a.owner IN ( SELECT DISTINCT productionschema FROM MV_BOB_TABLE c  )
                 and a.table_name = b.table_name
                 and a.table_name = c.segment_name
                 group by a.owner,a.table_name,b.driver_table,b.mandatory_join
               ORDER BY table_name';

file_handle := utl_file.fopen('ESTIMATES_CSV', v_csv_name||'_EST_PROC.csv', 'w', 32767);

--
UTL_FILE.PUT_LINE(file_handle, ' ');
UTL_FILE.PUT_LINE(file_handle, 'The below report shows total row counts in PROD');
UTL_FILE.PUT_LINE(file_handle, ' for unjoined tables in the BOB document:');
UTL_FILE.PUT_LINE(file_handle, ' ');
utl_file.put_line(file_handle, 'OWNER,TABLE_NAME,MANDATORY_JOIN,TOT_OBJECT_SIZE_MB,EST_ONE_ROW_MB,TOTAL_ROW_COUNT,SPACE_REQUIRED');
--main loop
--open cur for v_sql1 using TOTAL_ROW_COUNT,C_TOTAL_ROW_COUNT;
open cur for v_sql1 using C_TOTAL_ROW_COUNT, C_TOTAL_ROW_COUNT, TOTAL_ROW_COUNT;
   loop
      fetch cur into OWNER,TABLE_NAME,MANDATORY_JOIN,TOT_OBJECT_SIZE_MB,EST_ONE_ROW_MB,TOTAL_ROW_COUNT,SPACE_REQUIRED;--,EST_ONE_ROW_MB;
      exit when cur%NOTFOUND;
      execute immediate' select /*+parallel (4)*/ count(*) from '||owner||'.'||table_name || '@' || l_dblink into TOTAL_ROW_COUNT;

execute immediate' select /*+monitor parallel (10)*/ count(*) from ' ||owner||'.'||table_name || '@' || l_dblink||' b '||','||
driver_table || '@' || l_dblink||' a ' ||' where ' ||mandatory_join into TOTAL_ROW_COUNT;

execute immediate' select /*+monitor parallel (10)*/ count(*) from ' ||owner||'.'||table_name || '@' || l_dblink into C_TOTAL_ROW_COUNT;

utl_file.put_line(file_handle,
                      OWNER || ',' ||
                      TABLE_NAME || ',' ||
                      TOT_OBJECT_SIZE_MB || ',' ||
                      EST_ONE_ROW_MB || ',' ||
                      TOTAL_ROW_COUNT || ',' ||
                      C_TOTAL_ROW_COUNT || ',' || /* This is actually FULL_TABLE_COUNT*/
                      (TOT_OBJECT_SIZE_MB / C_TOTAL_ROW_COUNT)|| ',' ||
                      (TOT_OBJECT_SIZE_MB / C_TOTAL_ROW_COUNT) * round(TOTAL_ROW_COUNT)
                      );


v_total_driver_only := v_total_driver_only + round(TOT_OBJECT_SIZE_MB / C_TOTAL_ROW_COUNT,7) * round(TOTAL_ROW_COUNT,0);

end loop;

UTL_FILE.PUT_LINE(file_handle, ' ');
utl_file.put_line(file_handle,
                     'Total Estimated Space Required '|| round(v_total_driver_only,0) ||' MB'
                     );

utl_file.fclose(file_handle);

end ESTIMATE_BOB01;

To help me understand what is happening I have defaulted all of the number values to zero, my gut and the error, seems to tell me that there's something wrong here:

fetch cur into OWNER,TABLE_NAME,MANDATORY_JOIN,TOT_OBJECT_SIZE_MB,EST_ONE_ROW_MB,TOTAL_ROW_COUNT,SPACE_REQUIRED;

But I don't think there should be any reliance here on data type so long as all the values are declared properly in the variable section at the start and the same values are passed into them?

Help appreciated. Thanks guys!

EDIT:

There are 3 tables in the main SQL (Dynamic SQL) One is DBA_TABLES, which needs no introduction. The structure of the other two is:

SQL> desc MV_BOB_TABLE;
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 TARGETSCHEMA                                       VARCHAR2(200)
 PRODUCTIONSCHEMA                                   VARCHAR2(200)
 PRODUCTIONDATABASE                                 VARCHAR2(200)
 TABLE_NAME                                         VARCHAR2(200)
 DRIVER_TABLE                                       VARCHAR2(200)
 MANDATORY_JOIN                                     VARCHAR2(200)
 ADDITIONAL_JOINS                                   VARCHAR2(200)
 TABLE_COMPRESSED                                   VARCHAR2(200)
 BOB_ID                                        NOT NULL NUMBER(38)

SQL> desc <A_TABLE_THAT_CORRESPONDS_TO_v_Mv_name>;
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 LINK                                               CHAR(4)
 OWNER                                              VARCHAR2(90)
 SEGMENT_NAME                                       VARCHAR2(243)
 FLAG                                               CHAR(1)
 SUM_BYTES                                          NUMBER

EDIT:

SUM_BYTES does contain decimal values, like 0.375... etc... not sure if that matters.

Answer 1

Compare your query with the fetch statement. In your code following columns are assigned to these variables:

a.owner, -> OWNER
a.table_name, -> TABLE_NAME
b.driver_table, -> MANDATORY_JOIN
b.mandatory_join, -> TOT_OBJECT_SIZE_MB -- << This does not match!
sum(c.sum_bytes) AS "TOT_OBJECT_SIZE_MB", -> EST_ONE_ROW_MB
(sum(c.sum_bytes)) / (:C_TOTAL_ROW_COUNT) AS "EST_ONE_ROW_MB", -> TOTAL_ROW_COUNT
((sum(c.sum_bytes)) / (:C_TOTAL_ROW_COUNT)) * (:TOTAL_ROW_COUNT)AS "SPACE_REQUIRED" -> SPACE_REQUIRED

Answer 2

You are right that the error is pointing at the line

fetch cur into OWNER,TABLE_NAME,MANDATORY_JOIN,TOT_OBJECT_SIZE_MB,EST_ONE_ROW_MB,TOTAL_ROW_COUNT,SPACE_REQUIRED;

However this is executing the query so my best guess from the limited information provided would be that one of the fields that you are using in your calculation has non numeric characters in it. So either c.sum_bytes or :C_TOTAL_ROW_COUNT.

Because you have control over the :C_TOTAL_ROW_COUNT I would think this is less likely.

First thing I would be checking is the data type of sum_bytes if this is not number (eg varchar2) then somewhere there is a non numeric character in this field maybe someone has put 2000b rather than just 2000).

Depending on the number of records you are looking at you can possibly just eyeball it and see the offending data or you may need to do a bit more digging to find it.