I'd like to create a view in a block using dynamic sql (execute immediate statement, so I can catch error and do other house keeping in case an error occurs. But the definition of the view is so long that it's over 5000 characters -- far more than the 2000 limit for char/varchar data types in PL/SQL. So whenever I tried to create the view using 'execute immediate', it throws out error, but if I simply run the create view statement itself, it worked great.
Is there anyway for me to overcome this problem?
FYI -- when I create the view using dynamic sql, the error message it threw out was 'PLS-00382: expression is of wrong type'. I was thinking it should say something like 'over the limit' etc. So did I miss anything here?
In case you are still wondering this, I got this one solved by removing all the unnecessary parenthesis from the view definition. Those parenthesis don't seem to be a problem when I run the query from sql+ directly, but it kept giving me that weird error when I run it from the store procedure (crView) I wrote.
Don't know how to explain this, but hey, it's now working like a charm