PLSQL archiving LONG datatype, error:

Question

Im using Oracle 11g, attempting to move anything older than 90days to the History table using PL/SQL..BUT i have one of the columns using datatype of LONG. So i have found the SQL that i thought should work but it gives errors:

    BEGIN
FOR ROW IN
       (SELECT  MESSSAGE_KEY,
          DISTRIBUTION_ID,
          MESSAGE,
          SYSTEM_NAME,
          MESSAGE_TYPE,
          MESSAGE_NAME,
          MESSAGE_STATUS,
          LATEST_INBOUND,
          CREATETS,
          MODIFYTS,
          CREATEUSERID,
          MODIFYUSERID,
          CREATEPROGID,
          MODIFYPROGID,
          LOCKID,
          ENTITY_KEY,
          ENTITY_NAME,
          ENTITY_VALUE
        FROM    NWCG_INBOUND_MESSAGE
        WHERE   TO_CHAR (createts, 'YYYYMMDD') >= TO_CHAR ((sysdate-90), 'YYYYMMDD')
    )
    LOOP
    INSERT INTO NWCG_INBOUND_MESSAGE_H
    VALUES (    
                ROW.MESSSAGE_KEY,
          ROW.DISTRIBUTION_ID,
          ROW.MESSAGE,
          ROW.SYSTEM_NAME,
          ROW.MESSAGE_TYPE,
          ROW.MESSAGE_NAME,
          ROW.MESSAGE_STATUS,
          ROW.LATEST_INBOUND,
          ROW.CREATETS,
          ROW.MODIFYTS,
          ROW.CREATEUSERID,
          ROW.MODIFYUSERID,
          ROW.CREATEPROGID,
          ROW.MODIFYPROGID,
          ROW.LOCKID,
          ROW.ENTITY_KEY,
          ROW.ENTITY_NAME,
          ROW.ENTITY_VALUE
           );
END LOOP;
END;

This is the error i am getting:

Error report:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 2
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    
*Action:

From my research it looks like this error has been about a lot, but i cant find any of peoples solutions to work.... any ideas?

Answer 1

The long datatype has been one of the reasons why I've always advised against storing documents or long string in an Oracle database. Without reverting to C and OCI, it is hard to use.

Now we have clob and blob which are reasonable usable in PL/SQL and SQL. But there are still many occurrences of the LONG datatype to be found of it, also in the Oracle data dictionary. Especially in XXX_VIEWS (user_views, all_views, dba_views) it is a real problem. Maybe the original developer should have named it UNUSABLE :-).

There is a workaround when the LONG contents are smaller than 32 KB; for full functionality I would recommend migrating to CLOB or using C. Good luck!

--
-- This sample code works when the long is smaller than 32 KB.
-- It is known to work on 9i, 10g, 11g r1, 11g r2, but it assumes
-- that a LONG smaller than 32 KB can be put in a PL/SQL variable.
-- And then cast.
--
-- You might want to add an exception handler to handle exceptions
-- when the size is larger than 32 KB. In this sample, this situation
-- can not occur; the where clause with text_length ensures that.
--
declare
  l_text_as_long  long;
  l_text_as_clob  clob;
  l_text_length   user_views.text_length%type;
begin
  select viw.text
  ,      viw.text_length
  into   l_text_as_long
  ,      l_text_length
  from   user_views viw
  where  viw.view_name = upper(l_object_name)
  and    viw.text_length <= 32767 /* To fix a problem when accessing a view that is larger than 32K, we have this condition. */
  ;
  l_text_as_clob := cast(l_text_as_long as clob);
  ... do something interesting ...
end;