PLSQL archiving LONG datatype, error:

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?

Answers


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;

Need Your Help

Create (not read) field values into a new view in C# MVC

c# asp.net-mvc asp.net-mvc-5

I've looked, tried several different solutions and haven't found anything that works (at least, not something with an example close enough to what I want for me to follow). I'm sure I'm missing som...

How is ownership of mutex (lock) implemented?

c multithreading locking mutex

I am a little bit confused trying to implement a very simple mutex (lock) in C. I understand that a mutex is similar to a binary semaphore, except that the mutex also enforces the constraint that the