How to handle long view definition (> 32767 chars)
410740Jan 27 2006 — edited Nov 22 2006Hi to all.
I've been using a package of mine successfully for a long time, but now I'm running into trouble: I created a set of functions to modify views dynamically, i.e. retrieve the view definition (from USER_VIEWS), modify it (e.g. add new columns) and re-create the view (exec immediate). Now one of the views growed to a size larger than 32767 (!) characters, and of course I'm running into an exception when trying to retrieve it's TEXT into a Varchar2-Variable (typed VARCHAR2(32767), which is the upper limit).
What can I do to handle such long a string within PL/SQL? According to Oracle's docs, LONG variables can hold only 32760 bytes ("The LONG datatype is like the VARCHAR2 datatype, except that the maximum size of a LONG value is 32760 bytes", in Chap. 3 of "PL/SQL User's Guide"), which is even 7 less than a VARCHAR2 can hold. Or do I misinterpret this sentence?
What are my options? Could I use DBMS_LOB to do the processing of very long view texts? How can I retrieve a view text into, say, a CLOB variable, modify it, and - even more important - how can I EXECUTE IMMEDIATE the contents of a CLOB variable after modification?
TIA for any input.
Werner