you set variables in your package in order to store in a custom parameter table the date of last beginning and last ending
from the repository
you retrieve the value in the work repository : in the SNP_SCEN_REPORT table. You have the columns SESS_BEG and SESS_END in date format.
With SCEN_NO, do an inner join with SNP_SCEN to get the name of the scenario.
Juste retrieve the last row which SCEN_NAME = your scenario.
This is normal.
You have created a variable that retrieves the ACTUAL session begin date.
So, when you're launching your package at 11/12/2012 09:38:56, it will retrieve the begin date of this session, so 11/12/2012 09:38:56.
If you want the previous session, you cannot use a filter like SESS_NO = <%=odiRef.getSession("SESS_NO")%>
==> You must filter on the previous session of the same scenario. Or the previous session of the same session name.
Use WHERE clause to filter on finished / not running session
But don't use <%=odiRef.getSession("SESS_NO")%>
Other solution : use the SESS_END in order to retrieve the END date. Since your actuel scenario is not finish, you will get the last session.
If you really want the BEGIN date of the last session, you must adapt your SQL Statement in order to get the session previously executed.
Now my filter should be like this..SELECT * FROM SOURCE_TABLE S WHERE S.TRANS_DATE BETWEEN last_execution_time and SYSDATE.For example my previous last_time is 11/12/2012 09:30:56
sysdate is 11/12/2012 09:30:56
I will insert just these datas.Is there any solution to insert datas from previous last execution to current?
Last execution time is :previous end_time .Now What should I do?
1) Either you store the execution date in a custom parameter table (if you want to use this solution, I will give you more details).
2) OR you retrieve the date in the SNP_SESSION table, or the SNP_SCEN_REPORT table with the SQL statement we give you, Jerome and me.
Moreover, your previous execution should NOT be the last END execution time, but the last BEGINNING execution time. Otherwise, you won't extract the data that have been created in source table between the first extract and the end of your last execution.
3) You can also use CDC to journalize new/modified rows in source and select only them.