Re: Help !!!ORA-06502: PL/SQL: numeric or value error: character to number conversion error

From: JC <joseph.chewning_at_targacept.com>
Date: 28 Mar 2006 12:39:18 -0800
Message-ID: <1143578358.886767.143500@j33g2000cwa.googlegroups.com>

here is the whole thing to give you a better idea...

lotid number;

   colcnt number;
   conc number;
   colvalue number;
   mrn rowid;
   concval number;
   mcn varchar2(100);
  cursor lot is select lot_id,conc,rowid from tc_data.lot_final_values where rownum < 2;--need to remove on run  BEGIN
  delete from lot_final_values_com;
  Select all_tab_cols.column_ID into conc FROM all_tab_cols where all_tab_cols.TABLE_NAME='LOT_FINAL_VALUES' and all_tab_cols.OWNER='TC_DATA' and all_tab_cols.COLUMN_NAME='CONC';   SELECT COUNT(all_tab_cols.COLUMN_NAME) into colcnt FROM all_tab_cols WHERE all_tab_cols.table_name='LOT_FINAL_VALUES' AND all_tab_cols.OWNER='TC_DATA';
  open lot;
    loop

      fetch lot into lotid,concval,mrn;
        exit when lot%notfound;
         for c in 2..colcnt-2
         loop -- starts as two cuz lot_id is 1
            select sys.all_tab_columns.COLUMN_NAME into mcn from
sys.all_tab_columns where
sys.all_tab_columns.table_name='LOT_FINAL_VALUES_TEMP' and

sys.all_tab_columns.COLUMN_ID=TO_NUMBER(c) and sys.all_tab_columns.OWNER='TC_DATA';

             select mcn into colvalue from lot_final_values where lot_id=lotid and rowid=mrn;

          if colvalue IS NOT NULL then
                DBMS_OUTPUT.PUT_LINE('this is the lot_id ' || lotid ||
':');
                DBMS_OUTPUT.PUT_LINE('Assay_Name ' || mcn || ':');
                DBMS_OUTPUT.PUT_LINE('CONC ' || concval || ':');
                DBMS_OUTPUT.PUT_LINE('ASSAY_VALUE ' || colvalue ||
':');
                insert into lot_final_values_com

(LOT_ID,ASSAY_NAME,CONC,ASSAY_VALUE) VALUES
(lotid,mcn,concval,colvalue);

           end if;
        end loop;

    end loop;
   close lot;
  end;

Thanks for your help!! Received on Tue Mar 28 2006 - 14:39:18 CST