Hi Gurus,
I have a piece of code similar to the one below:
I have a table in which I have to make insert as given below :
The select fetches multiple rows and my manager doesn't want me to use cursor or bulk collect to make the code usable.
insert into schema1.tableA (JOB_ID, REC_RID, ROWID, ARC_DONE)
select v_job_id, rownum, rowid, 'N'
from ods.ODS_PROCESSED_MESSAGES
where stamp_add_dtime < sysdate - v_retention_days ;
I am facing hurdle because SELECT query is a part of INSERT's VALUES clause and moreover that SELECT has a variable in its where clause. I cannot declare the cursor with that variable.
Please guide me. Thanks.
I'm going to side with your manager here and say the insert you've shown is the correct way to do this. Using a cursor loop or bulk collect is likely to make your code slower. Processing all the data in a single statement is usually the fastest way.
Unless you need to manipulate the rows the query returns in a way that's impossible using SQL (unlikely), a single "insert into ... select ... from" is the way to go.
"I cannot declare the cursor with that variable."
I not sure what the issue is here. You don't need to declare a cursor! When the statements runs it loads all the rows the select returns into the table.