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?
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;