How would I store the result of a select statement so that I can reuse the results to join to different tables? This will also be inside a cursor.
Below is some pseudo code, in this example I have kept the Select statement simple but in real life it is a long query with multiple joins, I have to use the identical SQL twice to join to 2 different tables and as it is quite long and can be changed in the future hence I want to be able reuse it.
I have tried creating a view and storing the results of the select statement in it but it seems I can't create a view inside the cursor loop, when I tried I am getting "Encountered the symbol "CREATE"" error.
DECLARE TYPE cur_type IS REF CURSOR; CURSOR PT_Cursor IS SELECT * FROM Table1 PT_Cursor_Row PT_Cursor%ROWTYPE; BEGIN OPEN PT_Cursor; LOOP FETCH PT_Cursor INTO PT_Cursor_Row; EXIT WHEN PT_Cursor%NOTFOUND; Select ID From Table2 --this is actually a long complext query INNER JOIN Table3 ON Table2.ID = Table3.ID WHERE Table2.ID = PT_Cursor_Row.ID Select * From Table2 --this is actually a long complext query LEFT JOIN Table4 ON Table2.ID = Table4.ID WHERE Table2.ID = PT_Cursor_Row.ID END LOOP; CLOSE PT_Cursor; END;