I am writing a SQL query of 1000 lines in Stored Procedure and that when I compile, it throws an error that it cannot hold the entire query in varchar2 variable. Hence rewritten the same using CLOB type.

How do I pass the CLOB objects into VARRAY and compile faster?

Can any expert help me on this issue?