How to Overcome Limitation of long in Oracle

I have faced a issue while writing a Oracle procedure, I had a long type Oracle variable to store the dynamic query, And the long variable was not able to store the dynamic query because of large size of query.

v_sql long := 'Some thing very large';

Because long variable have limitation to store only 4000 characters while concatenating, you can not store more than 4000 char.

We have an alternate method.

 

Use of Global Temporary Table.

Step 1: Break The queries into 4000 bunch and execute the separate queries and insert into a global temporary table.

Step 2: Select all data from global temporary table.

All done.

 

Please note that this situation happened when i was using number of union blocks to combine a query and this method will increase the procedure execution time.

There may be other alternatives but this can be one.

 

execute immediate 'INSERT INTO GT_TEMP(ID,NAME)
  SELECT ID, Name FROM ( ' || v_sql || ' )';