Constant SYSDATE for all the records
Hi All,
I am trying to add data in oracle table with the help of sql loader. i have a column in table as REPO_DATE
i am putting REPO_DATE in control fine as SYSDATE. Example below of control file
OPTIONS (DIRECT=FALSE, ROWS=10000)
LOAD DATA
APPEND INTO TABLE RJ_SONIC_STAGING
EVALUATE CHECK_CONSTRAINTS
REENABLE DISABLED_CONSTRAINTS
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
ITEM,
STATE_ID,
REPO_DATE "SYSDATE"
)
Is there any way, i can have a constant SYSDATE for this load.
i mean table column gets updated with SYSDATE but as the file is huge, the SECOND part keeps on changing.
Like for first 1000 records it will be 21-JUN-2019 05:05:11 PM
and for next records it will be 21-JUN-2019 05:05:12 PM
So is there any way i can have a SAME SYSDATE for all the records.
By putting DEFAULT value in table property for column also results in the same.
I am trying to add data in oracle table with the help of sql loader. i have a column in table as REPO_DATE
i am putting REPO_DATE in control fine as SYSDATE. Example below of control file
OPTIONS (DIRECT=FALSE, ROWS=10000)
LOAD DATA
APPEND INTO TABLE RJ_SONIC_STAGING
EVALUATE CHECK_CONSTRAINTS
REENABLE DISABLED_CONSTRAINTS
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
ITEM,
STATE_ID,
REPO_DATE "SYSDATE"
)
Is there any way, i can have a constant SYSDATE for this load.
i mean table column gets updated with SYSDATE but as the file is huge, the SECOND part keeps on changing.
Like for first 1000 records it will be 21-JUN-2019 05:05:11 PM
and for next records it will be 21-JUN-2019 05:05:12 PM
So is there any way i can have a SAME SYSDATE for all the records.
By putting DEFAULT value in table property for column also results in the same.
Helena Marková
if you use trunc(sysdate) then time portion is set to 00:00:00.
move sysdate out of the loop and assign it to a variable the use the variable in your loop. Helena's solution works except on the edge case of starting near the end of a minute i.e. 13:00:59 and it lasts more than 1 second.. Another edge case is 12:59:59 now the hour and minute will change.
What loop? SQL*Loader is a stand alone utility with no loop.
2 ways I can think of quickly. Load to a temporary table and then insert to the final table setting a constant date. Set the FIXED_DATE parameter, but this is kind of a big hammer as it sets the date for all sessions.
2 ways I can think of quickly. Load to a temporary table and then insert to the final table setting a constant date. Set the FIXED_DATE parameter, but this is kind of a big hammer as it sets the date for all sessions.
ASKER
Hi Johnsone,
Thank you for the reply.
I cannot load into temporary table. How to do FIXED_DATE parameter?
Thank you for the reply.
I cannot load into temporary table. How to do FIXED_DATE parameter?
It is a system parameter. You set it with an ALTER SYSTEM.
You could also use an external table and and INSERT. That would skip the temporary table. But the file being loaded needs to be on the server to use external tables.
If you are using a script to run SQL*Loader, you can have the script write out the control file and put a constant value into the REPO_DATE.
You could also use an external table and and INSERT. That would skip the temporary table. But the file being loaded needs to be on the server to use external tables.
If you are using a script to run SQL*Loader, you can have the script write out the control file and put a constant value into the REPO_DATE.
You can create a package global variable and use that in the control file:
Then in the control file:
REPO_DATE "date_constant.get_date()"
The logic: sql*loader runs in the same session. On the first call to get_date in that session, var will be null because it has never been set before so it gets set. On each new call in that same session, it has the original value.
CREATE OR REPLACE PACKAGE date_constant
AS
FUNCTION get_date RETURN date;
END date_constant;
/
show errors
CREATE OR REPLACE PACKAGE BODY date_constant
AS
var date;
FUNCTION get_date
RETURN date
IS
BEGIN
if var is null then var:=sysdate; end if;
RETURN var;
END get_date;
END date_constant;
/
show errorsThen in the control file:
REPO_DATE "date_constant.get_date()"
The logic: sql*loader runs in the same session. On the first call to get_date in that session, var will be null because it has never been set before so it gets set. On each new call in that same session, it has the original value.
If you use DIRECT load, the SYSDATE value of a column default will only be evaluated once.
Same with SYSDATE included in the control file.
It's because you chose DIRECT=FALSE that the sysdate gets evaluated once for each commit point
Same with SYSDATE included in the control file.
It's because you chose DIRECT=FALSE that the sysdate gets evaluated once for each commit point
another option, read from a pipe, somewhat convoluted, but in the absence of external tables, that aren't a lot of "good" options.
Basic idea is you pipe your file into something that will append a single value, here I use awk, but you could use the scripting tool of your choice, the key is to make sure the script only evaluates the system time once. In my test data, the last column was followed by a field delimiter, if your data is not, then include a delimiter in the print command.
Then use the special data file of "-" to mean read from stdin and you should be all set.
It's essentially the same idea as using a preprocessor in an external table.
You don't really need to cat the file, you could use awk directly for that, same idea though.
These methods allow you to use DIRECT=FALSE and still maintain a consistent load date across all columns.
Basic idea is you pipe your file into something that will append a single value, here I use awk, but you could use the scripting tool of your choice, the key is to make sure the script only evaluates the system time once. In my test data, the last column was followed by a field delimiter, if your data is not, then include a delimiter in the print command.
Then use the special data file of "-" to mean read from stdin and you should be all set.
cat testdata.txt | awk 'BEGIN{load_date=strftime("%Y-%m-%d %H:%M:%S",systime())}{print $0 load_date}' |sqlldr parfile=test.par control=test.ctl2 data=\"-\"It's essentially the same idea as using a preprocessor in an external table.
You don't really need to cat the file, you could use awk directly for that, same idea though.
awk 'BEGIN{load_date=strftime("%Y-%m-%d %H:%M:%S",systime())}{print $0 load_date}' testdata.txt |sqlldr parfile=test.par control=test.ctl2 data=\"-\"These methods allow you to use DIRECT=FALSE and still maintain a consistent load date across all columns.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.