A reader, October 17, 2001 - 12:42 am UTC
Tom
If we have to use sysdate numerous number of times in our program, how do you suggest we do it.
Why is it expensive to select it from dual, every time we need it.
Thank you
October 17, 2001 - 7:54 am UTC
Well it depends (how you use it in your app). In my web based stuff (plsql htp pages, like here on asktom), we typically code:
create package my_pkg
as
g_sysdate date default sysdate;
....
end;
/
and use g_sysdate everywhere so the date/timestamp we use is ALWAYS the same for all inserts/updates and such. So, rather then:
insert into questions .... values ( sysdate, ..... )
we
insert into questions .... values ( g_sysdate, ..... )
and when we reference the current date in the code, we reference g_sysdate. This provides two things for us:
1) its a little faster (more on that in a minute)
2) we get the SAME date on all inserts. We make each page fast, but they can execute across 2 seconds (from 12:01:01.85 to 12:01:02.15 for example) and I'd like all timestamps related to that page to be the same 12:01:01.
On a client server app -- this does not work as well since you might be connected for hours or days even. A static date doesn't fit. So, it depends on your needs.
As for the expense...
Every time you go from PLSQL to SQL there is a context switch. You go from the PLSQL runtime to the database. No network or anything, just a context switch from one part of code to another. It is just a relatively expensive operation -- something to be avoid if and when you can.
Some options for faster SYSDATE
Connor, October 17, 2001 - 4:13 am UTC
1) Replace DUAL with an IOT or a view on X$DUAL - you should get approval from Oracle support before doing this. This makes the select-from-dual slightly faster.
2) See </code>
http://www.oracledba.co.uk/tips/quick_date.htm <code>for a "sysdate" that does not use the SQL engine
3) Probably most effective is to have a look at why you are getting sysdate so frequently.
October 17, 2001 - 8:26 am UTC
Not to be a contrarian HOWEVER (i'm going to be...)
1) you'll never get that approval. DUAL is too embedded all over the place. Strange things could happen (no, i have no examples -- just past experience tells me making a seemingly innocent change like this can lead to all kinds of bad things).
2) that sysdate, in addition to having the wrap problem every 248 days (you don't need a LONG session, you just need to start your appliation on that 248'th day -- once a year, you have a terrible BUG in your application as the number will wrap and your dates will be WAY..... off. Thats like having a Y2k bug once or twice a year every year for the rest of your life!) has a problem in that the dbms_utility.get_time clock is NOT syncronized with the system clock.
Hence, using the sysda.te function we'll find:
ops$tkyte@ORA717DEV.US.ORACLE.COM> begin
2 for i in 1 .. 10000
3 loop
4 if ( sysdate <> sysda.te )
5 then
6 dbms_output.put_line( 'found one ' || i );
7 exit;
8 end if;
9 end loop;
10 end;
11 /
found one 3479
PL/SQL procedure successfully completed.
ops$tkyte@ORA717DEV.US.ORACLE.COM> /
found one 4077
PL/SQL procedure successfully completed.
The sysda.te and sysdate function will differ by upto 1 second from eachother as the get_time function rolls seconds on a different beat then sysdate does. This could cause issues for some applications that rely on sysdate increasing (if they use a mix of sysda.te and sysdate as most will)
3) best answser. I ran the tests at the above referenced page and found:
a) sysdate: 12.14 seconds
b) sysda.te: 10.74 seconds
c) NO TRIGGER, use insert into stamp (x,date_col) select rownum, sysdate....
<b>0.64 seconds</b>
If performance were the overreaching goal here, you would not have SQL call PLSQL in the form of a trigger to do something that is more easily done in the SQL in the first place.
Reader
Reader, October 17, 2001 - 10:15 am UTC
Tom,
What is the theory behind the following occurrance
SQL> insert into dual select * from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dual;
D
-
X
SQL> select count(*) from dual;
COUNT(*)
----------
2
Thanks
October 17, 2001 - 11:17 am UTC
Better in 9i
Connor, October 17, 2001 - 11:54 am UTC
In 8.1.7
SQL> select * from dual;
Statistics
----------------------------------------------------------
4 db block gets
1 consistent gets
In 9.0.1
SQL> select * from dual;
Statistics
----------------------------------------------------------
2 db block gets
1 consistent gets
Always nice to see those good folks at Oracle doing things for us :-)
Difference between function and select
jan van mourik, March 04, 2002 - 11:18 am UTC
Tom,
To see if there's a performance difference, I ran these pl/sqls:
declare
t_date date;
t_n number;
begin
t_n := dbms_utility.get_time;
for i in 1..10000
loop
select sysdate into t_date from dual;
end loop;
dbms_output.put_line(dbms_utility.get_time - t_n);
end;
(Output between 73 and 81)
call count cpu elapsed
------- ------ -------- ----------
Parse 1 0.00 0.01
Execute 1 1.07 1.30
Fetch 0 0.00 0.00
------- ------ -------- ----------
total 2 1.07 1.31
declare
t_date date;
t_n number;
begin
t_n := dbms_utility.get_time;
--
for i in 1..10000
loop
t_date := sysdate;
end loop;
dbms_output.put_line(dbms_utility.get_time - t_n);
--
end;
(Output between 21 and 24)
call count cpu elapsed disk
------- ------ -------- ---------- ----------
Parse 1 0.00 0.00 0
Execute 1 0.22 0.21 0
Fetch 0 0.00 0.00 0
------- ------ -------- ---------- ----------
total 2 0.22 0.21 0
Using "t_date := sysdate;" is consistently faster. And I don't see the "SELECT SYSDATE FROM DUAL" in the tkprof output. What exactly is Oracle doing?
March 04, 2002 - 12:39 pm UTC
It is just using internalized, optimized code. It knows you want sysdate (it cannot see that from the first query really) and just gets it that much faster. As demonstrated above, it is running a query against dual -- just more "optimallly" (more internalized)
things changed in 8.1.7.4?
Richard Ji, December 27, 2002 - 7:11 am UTC
It seems that starting from 8.1.7.4, assigning sysdate to
a variable no longer does a "select sysdate into ...".
In stdbody.sql, sysdate will only do "select sysdate into.."
if function pessdt returns null. And that's a C call.
Sys.Standard
Som, April 10, 2003 - 2:03 am UTC
Hi,
This is Som from India.
I was just going through the Standard Package provided by Oracle.
The last function is quit interesting and not understandable to me.
Could you please explain this to me, by giving an example?
The function is :
FUNCTION || RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LEFT VARCHAR2 IN
RIGHT VARCHAR2 IN
I will be waiting for your positive answer.
Thanks a lot.
April 10, 2003 - 7:55 am UTC
it lets you code:
x := a || b;
it is the concatentation operator.
Interesting...
Jon, April 11, 2003 - 12:59 am UTC
If || were a "standard" function call, we would use it like:
x := ||(a,b);
but a||b doesn't look much like a function.
1) Why is it declared in Standard at all - doesn't the parser know what to do with it without reference to a package?
2) Or is a||b some obscure valid syntax for a function?
Thanks
Jon
April 11, 2003 - 8:49 am UTC
it just "is". don't have a really good answer for this beyond "it just is"....
All operators are functions.
Richard, April 11, 2003 - 1:13 pm UTC
|| is a function just like +, -, *, etc. etc.
Take a closer look at standard. They are all there.
In fact some languages (C++ and Ada to name two) will let you overload these operators. For example if you have defined an object of widgets and you want to be able to add them.
back to SYSDATE
A reader, April 11, 2003 - 1:18 pm UTC
Oracle sysdate is completely dependent on the operating system date. They are the same in value. Tested with Oracle 9i on linux: if setting the OS date using "date -s" to an arbitrary date, the oracle sysdate will have that value, and ticking alone with it...
SYSDATE faster from your own DUAL
Peter, April 12, 2003 - 8:07 am UTC
A bit of a long-winded example to prove that getting the date from your own table is consistently much faster than getting it from dual.
SQL> create table mydual (c1 number) storage (initial 8192) pctfree 0 pctused 99;
Table created.
SQL> insert into mydual values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> alter table mydual add constraint pk_mydual primary key (c1) using index tablespace user_indexes;
Table altered.
SQL> analyze table mydual compute statistics;
Table analyzed.
SQL> set autot on
SQL> select sysdate from dual;
SYSDATE
---------
12-APR-03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
1 consistent gets
0 physical reads
0 redo size
207 bytes sent via SQL*Net to client
122 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select sysdate from mydual;
SYSDATE
---------
12-APR-03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
1 0 INDEX (FULL SCAN) OF 'PK_MYDUAL' (UNIQUE) (Cost=1 Card=1)
Statistics
----------------------------------------------------------
39 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
207 bytes sent via SQL*Net to client
124 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
(Plenty of recursive calls first time)
SQL> /
SYSDATE
---------
12-APR-03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
1 0 INDEX (FULL SCAN) OF 'PK_MYDUAL' (UNIQUE) (Cost=1 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
207 bytes sent via SQL*Net to client
124 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
(No recursive calls or block gets)
SQL> /
SYSDATE
---------
12-APR-03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
1 0 INDEX (FULL SCAN) OF 'PK_MYDUAL' (UNIQUE) (Cost=1 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
207 bytes sent via SQL*Net to client
124 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set timing on
SQL> declare a date;
2 begin
3 for i in 1..100000 loop
4 select sysdate into a from mydual;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:11.08
SQL> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:11.08
SQL> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:11.09
SQL> declare a date;
2 begin
3 for i in 1..100000 loop
4 select sysdate into a from dual;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:15.05
SQL> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:16.00
SQL> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:15.05
SQL>
mydual is always faster - much.
Is sysdate the same value for each row
Sam To, June 11, 2003 - 2:25 pm UTC
Tom,
If I have the following SQL:
SELECT to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS'), ... FROM my_view a WHERE a.val = :1
Suppose this returns many rows and takes several minutes to run. Will the sysdate value selected be the same for every row? My tests indicates it does return the same value. I just want to find out if it is documented behavior.
Thank you very much.
June 11, 2003 - 7:55 pm UTC
sysdate will be the same within a query like that, yes.
not sure of the documentation explicity "says that" -- but sysdate will behave that way
Conor's follow up
umesh, June 11, 2003 - 11:50 pm UTC
Tom
I could not understand what Connor wanted to convey here. Please explain
June 12, 2003 - 8:43 am UTC
you don't say which item -- he said 2 things, assuming you meant:
In 8.1.7
SQL> select * from dual;
Statistics
----------------------------------------------------------
4 db block gets
1 consistent gets
In 9.0.1
SQL> select * from dual;
Statistics
----------------------------------------------------------
2 db block gets
1 consistent gets
Always nice to see those good folks at Oracle doing things for us :-)
....
Look at the db block gets -- they went down. In fact, on my machines
817, 4 db block gets, 1 consistent gets (5 logical IO's)
901, 0 db block gets, 3 consistent gets (3 logical IO's)
meaning a significant reduction in the amount of work it takes to process dual
9ir2 gives the same result 0 db block gets, 3 consistent gets (3 logical IO's)
A reader, June 12, 2003 - 9:05 am UTC
How to get the current time from the client machine using web forms?
A reader, September 24, 2003 - 4:23 pm UTC
I do not know if this question fits in here, but how can I get the current time from the client machine through a web form?
SYSDATE gives the current date/time from the database. What gies the current date/time from the client?
Thanks!
September 25, 2003 - 5:00 am UTC
i don't know that you can. i haven't myself used forms since 1995 -- the current time on the client seems sort of "not relevant in the least" as it is almost certainly "not right"
you can try the discussion forums on otn.oracle.com -- there is a developer forum, someone there might be able to tell you if you can or not.
Getting the Client machine time in WebForms
Joris Struyve, October 10, 2003 - 1:10 pm UTC
> I do not know if this question fits in here, but how can I
> get the current time
> from the client machine through a web form?
A JavaBean will do this for you. You can download the web util package from otn. It contains heaps of examples.
Sysdate in PLSQL and sysdate in SQL
Sandeep M Deokar, January 15, 2004 - 2:42 am UTC
hi tom
i want to ask you a simple question from your view but a bit difficult from mine
we use one PLSQL code here
declare
sysdate date ;
begin
..
..
end;
it works fine
that means we can define sysdate as a name of a variable in PLSQL block
where as
SQL> create table te(sysdate number);
create table te(sysdate number)
*
ERROR at line 1:
ORA-00904: : invalid identifier
and we cant define sysdate as column name of table name
why this diffrence is there
January 15, 2004 - 3:18 am UTC
well, sometimes it is not ours to ask why -- but just to look at it and say "really bad idea, we shouldn't do that". would you really define a variable as sysdate?
1 declare
2 sysdate date;
3 begin
4 for x in ( select sysdate y from dual ) loop
5 dbms_output.put_line( '----' || x.y || '----' );
6 end loop;
7* end;
big_table@ORA9IR2> /
--------
PL/SQL procedure successfully completed.
that would be a really utterly bad "idea".
Current_Date
Anindya, February 11, 2004 - 4:22 am UTC
Hi Tom,
Faced a problem quite similar to "sysdate" variable declaration here. One table having column name "CURRENT_DATE" started giving problem under 9i (table was probably migrated from 8i to 9i). Had to rename that column. A select from that column would always return system date rather than data from table.
But this is not the output in case of user defined functions.
What is the preference given for table columns, sql and user defined functions?
Since a column name may match a supplied function with some future release, what are the precautions we can take?
Thanks
February 11, 2004 - 9:11 am UTC
SQL takes precedence (keywords, reserved words)
the truly paranoid could use quoted identifiers -- eg:
select "CURRENT_DATE" from t
would have solved the problem as well. -- it would use your column.
short of that, you could use a prefix -- C$ or something (c = column)
current_date and sysdate
john, March 04, 2004 - 9:38 pm UTC
Tom,
can you please tell us how different is this new current_date from sysdate?
March 05, 2004 - 7:45 am UTC
current_date is timezone aware, sysdate is not.
help about altering sysdate
Fernando Sanchez, April 23, 2004 - 5:12 pm UTC
I don't know if it is possible to alter the value of sysdate only for a session, in other words how can I trick the dates of a session without altering the server system date?
Thanks in advance.
April 23, 2004 - 7:34 pm UTC
you cannot.
question about my_pkg
A reader, April 24, 2004 - 4:07 pm UTC
Hi
you have this package
create package my_pkg
as
g_sysdate date default sysdate;
....
end;
/
and use g_sysdate everywhere so the date/timestamp we use is ALWAYS the same for all inserts/updates and such. So, rather then:
I dont understand why the sysdate is always the same because even you use this in a package everytime you call
my_pkg.g_sysdate doesnt this cause a function call (sysdate) everytime you run it? how can this be a constant value?
April 26, 2004 - 5:32 am UTC
g_sysdate is a global.
globals are set once per session, not once per "use". g_sysdate is initialized upon the package being used for the first time and never again.
CURRENT_DATE returns the wrong day during the last half second of each day
Fred Schulz, May 25, 2006 - 7:24 pm UTC
Apparently, current_date rounds whereas sysdate truncs the seconds(Oracle 9.2.0.5)
select 'sysdate: ' || sysdate from dual union
select 'current_date: ' || current_date from dual union
select 'systimestamp: ' || systimestamp from dual union
select '(sysdate - current_date)*24*3600: ' || (sysdate - current_date)*24*3600 from dual;
Looks fine when below 0.5 seconds:
(sysdate - current_date)*24*3600: 0
current_date: 2006-05-26 07:18:51
sysdate: 2006-05-26 07:18:51
systimestamp: 26-MAY-06 07.18.51.460000 AM +08:00
However, when we pass 0.5 seconds, the difference is 1 second between them.
(sysdate - current_date)*24*3600: -1
current_date: 2006-05-26 07:19:11
sysdate: 2006-05-26 07:19:10
systimestamp: 26-MAY-06 07.19.10.898000 AM +08:00
Safe to use default values for created/updated?
Patrick, June 08, 2006 - 3:48 am UTC
I have a follow-query I hope is relevant to this and can't find you covering this elsewhere.
If I want to maintain created and updated date on a table, is it OK to use default values for both on creation?
CREATE TABLE defaulttable (created DATE DEFAULT SYSDATE, updated DATE DEFAULT SYSDATE, dummy VARCHAR(1));
CREATE OR REPLACE TRIGGER defaulttable_tg BEFORE UPDATE
ON defaulttable
FOR EACH ROW
BEGIN
:NEW.updated := SYSDATE;
END;
INSERT INTO defaulttable (dummy) values (1);
SELECT count(*) FROM defaulttable where created != updated;
Or is it possible that sometimes created and updated will not be the same? If so I have to use something like the following:
CREATE TABLE triggertable (created DATE DEFAULT SYSDATE, updated DATE);
CREATE OR REPLACE TRIGGER triggertable_tg BEFORE INSERT OR UPDATE
ON triggertable
FOR EACH ROW
BEGIN
IF INSERTING
THEN
:NEW.updated := :NEW.created;
ELSIF UPDATING
THEN
:NEW.updated := SYSDATE;
END IF;
END;
I find the default values neater, and it seems to work when testing, but I am worried that it will not always work.
June 08, 2006 - 9:13 am UTC
If you use defaults, they will be the same.
Why?
Patrick, June 08, 2006 - 9:29 pm UTC
Sorry, but no idea why that should be true. Is this just from observation, or is this guaranteed to be the case? Is this documented anywhere?
June 09, 2006 - 6:35 am UTC
sysdate is "bound" in the start of the statement.
Else:
create one_billion_row_table
as
select sysdate, t.* from really_big_table t;
would end up with rows all having different dates.
truth be told, last_updated should be NULL upon insert, the row was not updated. That is the convention most people would use.
sysdate in sql
Steve Read, June 22, 2006 - 5:24 pm UTC
Hi, Tom
I recently participated in a code walkthrough of a sql statement that contained a where clause like this:
nvl(my_date_column,sysdate) >= sysdate
I expressed concern that the sysdates might not be equal in extremely rare cases. Having read through this entire thread, I am reasonably sure that my concern is unfounded, but I just want to clarify it.
My understanding now is that the sysdate value will always be the same within a single sql statement, regardless of how complex or long-running that sql statement is. Is this a fundamental part of the way Oracle works and is guaranteed to be true now and in all foreseeable future releases?
I know that 10g made significant changes to the way that special columns like sysdate and user are handled. Did this have any effect on the consistency of the sysdate value?
Also, I assume that this is not true within pl/sql. If you have an insert using sysdate inside a cursor, the dates would change, but an "insert ... select ..., sysdate, ... from ..." would be guaranteed to be all the same.
Thanks for providing this very valuable service to us for so many years and thanks in advance for your response.
June 23, 2006 - 10:01 am UTC
I'm going to have a bug filed to get the documentation to clarify this.
sysdate, like currval, nextval - is constant for a give sql statement.
in your last case - the plsql "cursor for loop", you don't have to assume anything - you have multiple invocations of a sql statement (cursor, no cursor, whatever). every invocation gets its own sysdate value
sysdate in sql
Steve Read, June 23, 2006 - 4:01 pm UTC
Thanks for the clarification, Tom.
About 3 posts before this in the trigger question, you responded that the default values (in the create table statement) would be consistent. Does that imply it would not be consistent for the assignment in the body of the trigger? I would assume that value would be independently generated. Is that right?
Similarly, for functions called within the sql statement, are the sysdate, etc. values independently generated in the assignments and sql statements in the function?
Or is the "bound" value carried forward into all functions and triggers initiated by the sql statement?
Thanks again for your help.
June 24, 2006 - 11:35 am UTC
if you do:
:new.d1 := sysdate;
:new.d2 := sysdate;
d1 and d2 could be "different"
if you PASS sysdate to the function called from SQL, it'll remain constant. If your function REFERENCES sysdate itself, it'll change:
ops$tkyte@ORA10GR2> create or replace context my_ctx1 using f1
2 /
Context created.
ops$tkyte@ORA10GR2> create or replace context my_ctx2 using f2
2 /
Context created.
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create or replace function f1( p_date in date ) return date
2 as
3 begin
4 dbms_session.set_context( 'my_ctx1', 'cnt', nvl(sys_context( 'my_ctx1', 'cnt' ),0) +1 );
5 dbms_lock.sleep( 0.25 );
6 return p_date;
7 end;
8 /
Function created.
ops$tkyte@ORA10GR2> create or replace function f2( p_date in date ) return date
2 as
3 begin
4 dbms_session.set_context( 'my_ctx2', 'cnt', nvl(sys_context( 'my_ctx2', 'cnt' ),0) +1 );
5 dbms_lock.sleep( 0.25 );
6 return SYSDATE;
7 end;
8 /
Function created.
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
Session altered.
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select f1( sysdate ), f2( sysdate )
2 from all_objects
3 where rownum <= 5;
F1(SYSDATE) F2(SYSDATE)
-------------------- --------------------
24-jun-2006 11:30:43 24-jun-2006 11:30:44
24-jun-2006 11:30:43 24-jun-2006 11:30:44
24-jun-2006 11:30:43 24-jun-2006 11:30:45
24-jun-2006 11:30:43 24-jun-2006 11:30:45
24-jun-2006 11:30:43 24-jun-2006 11:30:46
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select sys_context( 'my_ctx1', 'cnt' ),
2 sys_context( 'my_ctx1', 'cnt' )
3 from dual;
SYS_CONTEXT('MY_CTX1','CNT')
-------------------------------------------------------------------------------
SYS_CONTEXT('MY_CTX1','CNT')
-------------------------------------------------------------------------------
5
5
I added documentation bug #5357959
A reader, June 26, 2006 - 3:07 pm UTC
in regards to getting sysdate/systimestamp and others documented as to how they behave when referenced multiple times in a sql statement...
That was me by the way....
Thomas Kyte, June 26, 2006 - 3:08 pm UTC
in the comment above.
Great explanation
Steve Read, June 27, 2006 - 10:00 pm UTC
Thanks, Tom.
That makes it very clear. My concern about the sysdates not matching is definitely not necessary.
Thanks for taking the time to explain it.
current_date, sysdate
A reader, January 18, 2008 - 11:06 am UTC
what is the difference between current_date and sysdate ?
Sometimes there is a difference of 1 second between these 2
January 19, 2008 - 10:39 pm UTC
whenever you call N different functions that evaluate something at a point in time, you might expect something different:
ops$tkyte%ORA10GR2> select current_date, sysdate, systimestamp from dual;
CURRENT_DATE SYSDATE
-------------------- --------------------
SYSTIMESTAMP
---------------------------------------------------------------------------
19-jan-2008 22:42:43 19-jan-2008 22:42:42
19-JAN-08 10.42.42.727207 PM -05:00
At the time we executed that query - time is marching on, the query evaluates things - maybe it did systimestamp first, then sysdate, then current_date.
And as time marched on, current_date flipped over a second.
A reader, January 23, 2008 - 11:27 am UTC
thanks
Then what is the difference between current_date and sysdate ?
If they are the same, why have both instead of just one ?
January 23, 2008 - 2:20 pm UTC
quick glance at documentation:
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/functions036.htm#i999792 I see
CURRENT_DATE returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE.whereas
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/functions172.htm#i79216 states
SYSDATE returns the current date and time set for the operating system on which the database resides.so, now it is easy to see a difference:
ops$tkyte%ORA10GR2> select sysdate, current_date from dual;
SYSDATE CURRENT_DATE
-------------------- --------------------
23-jan-2008 14:23:32 23-jan-2008 14:23:32
ops$tkyte%ORA10GR2> alter session set time_zone = 'PST';
Session altered.
ops$tkyte%ORA10GR2> select sysdate, current_date from dual;
SYSDATE CURRENT_DATE
-------------------- --------------------
23-jan-2008 14:23:46 23-jan-2008 11:23:47
A reader, January 23, 2008 - 4:24 pm UTC
should have checked docs first
current_date behaviour
Ragesh, November 15, 2008 - 10:17 am UTC
Hi Tom,
When selecting the date value which was inserted using the current_date function , I am getting a no rows found message. But when the session time zone is changed to dbtimezone, it works fine. Is it because the current_date has the session timezone information ? But the date column will not store the timzone information, right? It would be great if you could explain the usage of current_date. In our application the records are inserted using current_date rather than sysdate.
SQL> create table dt (cu_dt DATE);
Table created.
SQL> alter session set nls_date_format='DD-MON-YYYY HH:MI:SS';
Session altered.
SQL> insert into dt values(current_date);
1 row created.
SQL> select * from dt;
CU_DT
--------------------
15-NOV-2008 06:59:41
SQL> select * from dt where cu_dt=to_date('15-NOV-2008 06:59:41','DD-MON-YYYY HH:MI:SS');
no rows selected
SQL> insert into dt values(sysdate);
1 row created.
SQL> select * from dt;
CU_DT
--------------------
15-NOV-2008 06:59:41
15-NOV-2008 08:30:44
SQL> select * from dt where cu_dt=to_date('15-NOV-2008 08:30:44','DD-MON-YYYY HH:MI:SS');
CU_DT
--------------------
15-NOV-2008 08:30:44
SQL> select dbtimezone from dual;
DBTIME
------
-04:00
SQL> alter session set time_zone='-04:00';
Session altered.
SQL> insert into dt values(current_date);
1 row created.
SQL> select * from dt;
CU_DT
--------------------
15-NOV-2008 06:59:41
15-NOV-2008 08:30:44
15-NOV-2008 09:32:51
SQL> select * from dt where cu_dt=to_date('15-NOV-2008 09:32:51','DD-MON-YYYY HH:MI:SS');
CU_DT
--------------------
15-NOV-2008 09:32:51
November 18, 2008 - 6:42 pm UTC
ops$tkyte%ORA10GR2> create table t ( x date, y date );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values ( current_date, sysdate );
1 row created.
ops$tkyte%ORA10GR2> select t.*, dump(x), dump(y) from t;
X Y
-------------------- --------------------
DUMP(X)
-------------------------------------------------------------------------------
DUMP(Y)
-------------------------------------------------------------------------------
18-nov-2008 17:26:47 18-nov-2008 17:26:47
Typ=12 Len=7: 120,108,11,18,18,27,48
Typ=12 Len=7: 120,108,11,18,18,27,48
ops$tkyte%ORA10GR2> @bigdate
ops$tkyte%ORA10GR2> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
Session altered.
ops$tkyte%ORA10GR2> column x_str new_val X
ops$tkyte%ORA10GR2> select x x_str from t;
X_STR
--------------------
18-nov-2008 17:26:47
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from t where x = to_date( '&X', 'dd-mon-yyyy hh24:mi:ss' );
old 1: select * from t where x = to_date( '&X', 'dd-mon-yyyy hh24:mi:ss' )
new 1: select * from t where x = to_date( '18-nov-2008 17:26:47', 'dd-mon-yyyy hh24:mi:ss' )
X Y
-------------------- --------------------
18-nov-2008 17:26:47 18-nov-2008 17:26:47
I cannot reproduce, once you put a date into a date, it is 7 bytes of binary data (no timezone, nothing), can you do what I did and see what you see?
current_date behaviour
Ragesh, November 21, 2008 - 10:22 am UTC
Thanks Tom,
I have executed the steps you mentioned and it gives the expected result. But when I changed the nls_date_format to 12hr format, I am not getting the result.
SQL> select banner from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio
NLSRTL Version 10.2.0.3.0 - Production
SQL> create table t ( x date, y date );
Table created.
SQL> insert into t values ( current_date, sysdate );
1 row created.
SQL> select t.*, dump(x), dump(y) from t;
X Y
--------- ---------
DUMP(X)
--------------------------------------------------------------------------------
DUMP(Y)
--------------------------------------------------------------------------------
21-NOV-08 21-NOV-08
Typ=12 Len=7: 120,108,11,21,20,24,51
Typ=12 Len=7: 120,108,11,21,9,54,51
SQL> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
Session altered.
SQL> column x_str new_val X
SQL> select x x_str from t;
X_STR
--------------------
21-nov-2008 19:23:50
SQL> select * from t where x = to_date( '&X', 'dd-mon-yyyy hh24:mi:ss' );
old 1: select * from t where x = to_date( '&X', 'dd-mon-yyyy hh24:mi:ss' )
new 1: select * from t where x = to_date( '21-nov-2008 19:23:50', 'dd-mon-yyyy hh24:mi:ss' )
X Y
-------------------- --------------------
21-nov-2008 19:23:50 21-nov-2008 08:53:50
SQL> alter session set nls_date_format = 'dd-mon-yyyy hh:mi:ss';
Session altered.
SQL> select x x_str from t;
X_STR
--------------------
21-nov-2008 07:23:50
SQL> select * from t where x = to_date( '&X', 'dd-mon-yyyy hh:mi:ss' );
old 1: select * from t where x = to_date( '&X', 'dd-mon-yyyy hh:mi:ss' )
new 1: select * from t where x = to_date( '21-nov-2008 07:23:50', 'dd-mon-yyyy hh:mi:ss' )
no rows selected
I solved the problems in our application using the bind variables. (As you always pointing out). We were having a dynamic query using date as paramenters. I changed it with the "USING" clause and it working fine.
Thanks again.
November 24, 2008 - 2:27 pm UTC
well, you did that yourself. 07 using hh is 7am, you have 7pm...
ops$tkyte%ORA10GR2> create table t ( id number, x date );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values ( 1, to_date( '01-jan-2008 19:20:30', 'dd-mon-yyyy hh24:mi:ss' ) );
1 row created.
ops$tkyte%ORA10GR2> insert into t values ( 2, to_date( '01-jan-2008 07:20:30', 'dd-mon-yyyy hh24:mi:ss' ) );
1 row created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select id, to_char( x, 'hh24:mi:ss' ), to_char( x, 'hh:mi:ss' ) from t;
ID TO_CHAR( TO_CHAR(
---------- -------- --------
1 19:20:30 07:20:30
2 07:20:30 07:20:30
you would need to use 24 hour time OR specify am/pm in the time.
current_date behaviour
Ragesh, November 25, 2008 - 9:40 am UTC
Thanks Tom. :)
like operator using sysdate
Snehasish Das, August 19, 2010 - 5:36 am UTC
Hi Tom,
While firing queries like say as below.
select * from ORDERS where created_date like SYSDATE;
-- NLS DATE FORMAT is in DD/MM/YYYY HH24:MI:SS
I am getting records. even if the created_date time doesnot match with the sysdate. Its doing a trunc on both sided.
Am i correct in assuming this.
August 19, 2010 - 2:53 pm UTC
where created_date like sysdate
is just
where to_char(created_date) like to_char(sysdate)
we should comment that - this query is "not a smart idea" I guess, it relies on implicit conversions (always - repeat ALWAYS - a bad bad idea). It uses string comparison for things that are not strings (likewise, a really bad idea). It uses LIKE when LIKE doesn't even begin to make sense.
but - I think you are mistaken in your belief that the NLS_DATE_FORMAT is set to what you say it is. Here is my evidence that you are incorrect in your belief:
ops$tkyte%ORA10GR2> create table t ( x date );
Table created.
ops$tkyte%ORA10GR2> insert into t values ( sysdate );
1 row created.
ops$tkyte%ORA10GR2> exec dbms_lock.sleep(2)
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT DD-MON-RR
ops$tkyte%ORA10GR2> select x, sysdate from t where x like sysdate;
X SYSDATE
--------- ---------
19-AUG-10 19-AUG-10
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS';
Session altered.
ops$tkyte%ORA10GR2> select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT DD/MM/YYYY HH24:MI:SS
ops$tkyte%ORA10GR2> select x, sysdate from t where x like sysdate;
no rows selected
hellow
sankar, October 01, 2010 - 10:16 am UTC
create a function to calculate sal by passing empno increment due date as paramater
1 year -- no sal increment,
2 year -- 10% increment,
3 year -- 20% increment,
4 year -- 30% increment.
October 01, 2010 - 11:13 am UTC
the blue fish swims in the night.
sorry - I didn't quite follow you there. Did you have a question. One would need a create table, inserts, and a detailed, complete explanation of what they were trying to accomplish...
SYSDATE
GMartins, June 25, 2012 - 3:04 pm UTC
Hi Tom, thanks for all your support until here! =D
So, after all of this talk, I still have a question.
We have an application with a PL/SQL with many sysdate statements as below:
--> dbms_output.put_line(to_char(sysdate,'dd/mm/yyyy hh24:mi:ss'));
--> v_date date := sysdate
--> insert into table xxx values (...,...,sysdate)
and so on...
And when I execute trace in the session that is running this PL/SQL I see many "select sysdate from dual" with the same sql_id, and also after tkprof too. However,in the code, there isn´t this statement, so, My question is:
When I issue only sysdate inside the database, is it executed "select sysdate from dual"? If not, please, could you please clarify what oracle database really do?
Thanks in advance!
Regards,
GMartins.
June 25, 2012 - 9:07 pm UTC
you are using a really old version of the database aren't you?
once upon a time ago, a long time ago,
v_date date := sysdate;
would implicitly use a select sysdate from dual to get sysdate. But that hasn't been true for many releases.
"But that hasn't been true for many releases."
Sokrates, June 26, 2012 - 2:28 am UTC
my conjecture is ( cannot verify nor falsify them ):
it wasn't true for releases >= 7.2
good or bad estimation ?
June 26, 2012 - 7:57 am UTC
it was later than that, I believe that was done up to and including at least 8.0 and maybe even beyond (I don't have my 8i database with me right now...)
SYSDATE
GMartins, June 26, 2012 - 6:56 am UTC
Actually I´m using Oracle 10.2.0.3 on HP-UX Itanium.
And the strange thing as I said, is that there isn´t any "select sysdate from dual" inside de code, but I still get this statement on trace/tkprof.
So, I´m confused now... could you please clarify something?
Thanks in advance!
Regards,
GMartins.
June 26, 2012 - 8:09 am UTC
look at the statements right before it in the trace file (or use "aggregate=no" on the tkprof command line, then the report will show each execution of each sql statement in order)
that should be enough information to figure out where in the code it is happening, then you can go to that code and create a teeny tiny, reproducible example to demonstrate the issue.
If I do this in 10.2.0.5
ops$tkyte%ORA10GR2> create table t ( x int, y date );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @trace
ops$tkyte%ORA10GR2> alter session set events '10046 trace name context forever, level 12';
Session altered.
ops$tkyte%ORA10GR2> declare
2 v_date date := sysdate;
3 begin
4 dbms_output.put_line(to_char(sysdate,'dd/mm/yyyy hh24:mi:ss'));
5 dbms_output.put_line(to_char(v_date,'dd/mm/yyyy hh24:mi:ss'));
6 insert into t (x,y) values (100,sysdate);
7 end;
8 /
26/06/2012 09:08:19
26/06/2012 09:08:19
PL/SQL procedure successfully completed.
I cannot reproduce at all.
SYSDATE
A reader, July 02, 2012 - 7:35 am UTC
Tom, yes, that´s a little strange, because I can´t reproduce too. I will investigate Forms to see if there is some kind of this statement coming from there.
Continuing this issue, to make more strange that is... When I see the event of the session which is executing "select sysdate from dual" I got "TCP Socket (KGAS)". So, as I know, this event is when you´re taking some data from another database/server, right? And how can this occur with just a "select sysdate from dual".
When I query v$sqlarea with hash_value I got the folowing:
=================================================================
ROWS_PROCESSED SQL_TEXT DISK_READS BUFFER_GETS EXECUTIONS
-------------- ----------------------------- ---------- ----------- ----------
4806902 SELECT SYSDATE FROM SYS.DUAL 0 0 4806908
=================================================================
And the session has LAST_CALL_ET more then 45 minutos executing just "select sysdate from dual". It seems to be in a loop, by as I told you, there isn´t any statement on PL/SQL code. Did you have any idea what is going on?
Thankyou very much for your attention and help!
GMartins.
July 02, 2012 - 9:40 am UTC
this is not a distributed wait event, it is a wait on some external tcp service invoked via utl_smtp, utl_tcp, utl_http and the like.
if you have the sql id, see if you can see program_id, program_line# in v$sql
SYSTEDATE
GMARTINS, July 02, 2012 - 12:05 pm UTC
You´re totally right about "TCP Socket (KGAS)"! Talking with some analysts, I discovered that there was a change on Web Services. But the issue about sysdate still.
Why there´s lots of "sysdate from dual" when I trace the session? Is it caused by "sysdate" function call, some resource from Forms, ... I´m lost with that case... =/
Thanks in advance!
July 02, 2012 - 2:13 pm UTC
I'd have to think it is a "forms'ism"
it could be that FORMS is doing a select from dual - which would make sense since forms should be using the sysdate from the database not from the OS it is running on perhaps? It has been 17 years since I've used forms - but I would suspect it as the culprit since the database hasn't done that bit of recursive sql for many versions.
GMARTINS, July 02, 2012 - 3:03 pm UTC
Yes, I will investigate this case with Forms Application Analyst! Thanks for advice, and I really think this is the case!
Answering you the question before, look what i've got!
=============================================================
SID SERIAL# SPID STATUS USERNAME EVENT SQL_HASH_VALUE SQL_ID
------- ------- ------------ -------- -------------- ---------------------------------------- -------------- -------------
3850 47337 7814 ACTIVE OPS$SSBESK latch free 1542467439 gn7q8hjdz0bvg
SID SERIAL# SPID STATUS USERNAME EVENT SQL_HASH_VALUE SQL_ID
------- ------- ------------ -------- -------------- ---------------------------------------- -------------- -------------
3850 47337 7814 ACTIVE OPS$SSBESK latch free 1542467439 gn7q8hjdz0bvg
SID SERIAL# SPID STATUS USERNAME EVENT SQL_HASH_VALUE SQL_ID
------- ------- ------------ -------- -------------- ---------------------------------------- -------------- -------------
3850 47337 7814 ACTIVE xxxxxxx latch: row cache objects 1542467439 gn7q8hjdz0bvg
SID SERIAL# SPID STATUS USERNAME EVENT SQL_HASH_VALUE SQL_ID
------- ------- ------------ -------- -------------- ---------------------------------------- -------------- -------------
3850 47337 7814 ACTIVE xxxxxxx db file sequential read 1542467439 gn7q8hjdz0bvg
SID SERIAL# SPID STATUS USERNAME EVENT SQL_HASH_VALUE SQL_ID
------- ------- ------------ -------- -------------- ---------------------------------------- -------------- -------------
3850 47337 7814 ACTIVE xxxxxxx buffer busy waits 1542467439 gn7q8hjdz0bvg
SID SERIAL# SPID STATUS USERNAME EVENT SQL_HASH_VALUE SQL_ID
------- ------- ------------ -------- -------------- ---------------------------------------- -------------- -------------
3850 47337 7814 ACTIVE xxxxxxx latch: library cache lock 1542467439 gn7q8hjdz0bvg
SID SERIAL# SPID STATUS USERNAME EVENT SQL_HASH_VALUE SQL_ID
------- ------- ------------ -------- -------------- ---------------------------------------- -------------- -------------
3850 47337 7814 ACTIVE xxxxxxx log buffer space 1542467439 gn7q8hjdz0bvg
SID SERIAL# SPID STATUS USERNAME EVENT SQL_HASH_VALUE SQL_ID
------- ------- ------------ -------- -------------- ---------------------------------------- -------------- -------------
3850 47337 7814 ACTIVE xxxxxxx latch: session allocation 1542467439 gn7q8hjdz0bvg
SID SERIAL# SPID STATUS USERNAME EVENT SQL_HASH_VALUE SQL_ID
------- ------- ------------ -------- -------------- ---------------------------------------- -------------- -------------
3850 47337 7814 ACTIVE xxxxxxx latch: cache buffers lru chain 1542467439 gn7q8hjdz0bvg
SID SERIAL# SPID STATUS USERNAME EVENT SQL_HASH_VALUE SQL_ID
------- ------- ------------ -------- -------------- ---------------------------------------- -------------- -------------
3850 47337 7814 ACTIVE xxxxxxx PL/SQL lock timer 2767397235 3zvryskkg68bm
15:54:45 system@DB> @hash 2767397235
ROWS_PROCESSED SQL_TEXT DISK_READS BUFFER_GETS EXECUTIONS
-------------- ----------------------------------------------------------------- ---------- ----------- ----------
78158 SELECT USER FROM SYS.DUAL 0 0 78158
15:55:31 system@DB> select program_id,program_line# from v$sql where sql_id='gn7q8hjdz0bvg';
PROGRAM_ID PROGRAM_LINE#
---------- -------------
0 2
0 2
2 rows selected.
SID SERIAL# SPID STATUS USERNAME EVENT SQL_HASH_VALUE SQL_ID LAST_CALL_MIN
------- ------- ------------ -------- -------------- ---------------------------------------- -------------- ------------- -------------
3850 47337 7814 ACTIVE xxxxxxx TCP Socket (KGAS) 1272729223 5pjx5pj5xsmn7 2
1 row selected.
===========================================================================================
16:19:19 system@DB> @hash 1272729223
ROWS_PROCESSED SQL_TEXT DISK_READS BUFFER_GETS EXECUTIONS
-------------- ----------------------------------------------------------------- ---------- ----------- ----------
5284719 SELECT SYSDATE FROM SYS.DUAL 0 0 5284725
1 row selected.
16:19:28 system@DB> select program_id,program_line# from v$sql where sql_id='5pjx5pj5xsmn7';
PROGRAM_ID PROGRAM_LINE#
---------- -------------
0 2
0 2
2 rows selected.
=============================================================
How can the same SQL be waiting for so many different events during the same session with "select sysdate from dual" statement? And as you can see, the are two hash_value and sql_id for it. =/
July 03, 2012 - 8:16 am UTC
are you joining multiple v$ tables together there? v$ tables are not read consistent and things are happening so so so fast - that you are not necessarily seeing the same point in time information from the two three or so tables I think you are accessing. So, you can be picking up the sql text a tiny fraction of a second before/after you pick up the wait.
plsql lock timer isn't something a select from dual would wait for, for example.
SYSDATE
GMartins, July 03, 2012 - 9:03 am UTC
Hmm that´s interesting! So, is it possible where I query
FROM v$session s, v$process p, v$session_wait sw
WHERE s.paddr = p.addr
AND s.sid = sw.sid
Let me know if a understood.
I got s.sql_hash_value and s.sql_id in a moment and sw.event in another moment in the same query because there is no read consistence?
That´s why I can see many different events from v$session_wait and the same hash_value and sql_id from v$session?!
July 03, 2012 - 10:15 am UTC
exactly, a query like "select sysdate from dual" happens so fast... Things can change between the time you go from v$session to v$session_wait.
In fact, things can change while you are reading v$session itself - the last row you select could be as of a slightly different point in time than the first row.
the v$ tables are mostly in memory data structures, no redo/undo for them - since no undo, no consistent read. And sometimes we use unlatched access to them - meaning we are reading them as others are changing them.
GMartins, July 03, 2012 - 11:44 am UTC
One more time, i'm very thankful for all attention and help!
All of this information is amazing!
To conclude, I only can think now... that there´s something else executing so fast, but so fast that I can´t got it in executing time, and it´s causing latch, sequential read and so on.
But I´m happy now because I understood the scenario and another Oracle Engineer answer me that:
"A code that contains "to_char(***sysdate***)" could generate behind something like "select sysdate from dual;" .
Which probably is my case when I started to post here.
Thank you guys and Thank you Tom for this great and helpful Job!
Thanks / Regards
GMartins.
@GMARTINS
Paul, July 04, 2012 - 9:35 am UTC
One place to search in Forms for possible frequent "SELECT sysdate FROM dual" statements: if the "Initial Value" property of a Forms Item is set to "$$DBDATE$$", there is a good chance Forms sends such a SELECT to the database each time a new record is created in that "Forms Data Block" (I haven't checked though, I don't have Forms installed right now).
Session settable sysdate
Butch Wesley, July 03, 2013 - 4:08 pm UTC
I understand that you cannot change sysdate for a session, only for the db. I was wondering exactly how foolish it would be to wrap sysdate so that you could provide a different value if you wanted to?
The thought was to use a wrapper function and a session variable (sys_context style) or a package variable (or accessor method) to hold the date for a session. If this value was set to a valid date then that date would be returned, otherwise sysdate would be returned.
So far the only downside I have found is that you must use a scalar sub-query when calling it from SQL or it will be called once for every row and be extremely slow (when used in scalar sub-query it is only called once and the value is cached). I noticed in an earlier post that you stated that sysdate will return the same values across a long running SQL statement so using a scalar sub-query to get it doesn't break anything.
I seem to come back to this problem again and again and I would really like to know that this is either a valid solution or I'm over engineering this.
Here is the code I whipped up off the top of my head
create or replace package settable_sysdate is
procedure set_date(in_date in varchar2);
function get_date return date;
end settable_sysdate;
/
create or replace package body settable_sysdate is
g_date varchar2(100);
procedure set_date(in_date in varchar2)
is
begin
g_date := in_date;
end;
function get_date return date
is
l_date date := null;
begin
if(g_date is not null)then
return to_date(g_date, 'mm/dd/yyyy');
else
return sysdate;
end if;
exception
when others then
return sysdate;
end;
end settable_sysdate;
/
create table SOME_LARGISH_THING
(
some_id NUMBER
);
begin
for i in 1..700000 loop
insert into some_largish_thing (some_id) values (i);
end loop;
commit;
end;
/
select systimestamp from dual;
--700k sysdates
select distinct the_date from (select some_id, sysdate the_date from some_largish_thing);
select systimestamp from dual;
--700k settable_sysdates
select distinct the_date from (select some_id, settable_sysdate.get_date the_date from some_largish_thing);
select systimestamp from dual;
--scalar sub-query causing settable_sysdate.get_date to only be called once, big improvement
select distinct the_date from (select some_id, (select settable_sysdate.get_date from dual) the_date from some_largish_thing);
select systimestamp from dual;
--Showing that setting the date actually works.
exec settable_sysdate.set_date('01/01/4000');
select distinct the_date from (select some_id, (select settable_sysdate.get_date from dual) the_date from some_largish_thing);
select systimestamp from dual;
drop table some_largish_thing;
July 16, 2013 - 12:44 pm UTC
exception
when others then
return sysdate;
end;i despise your code. why??? why would you do this?
so here I am - joe programmer - I use your function to set the date, not realizing someone or something has changed the default NLS_DATE format - and the date I sent you which should convert - does not - and you return me sysdate instead - corrupting my data. great, perfect.
JUST LET IT FAIL.
honestly,
JUST LET IT FAIL.
and don't use character strings in the FIRST PLACE, have the caller send a *date* to set_date - think about it, why convert it over and over and over????
You can either use the package and a scalar subquery OR you could use an application context. This is the package I would suggest (NO WHEN OTHERS!!!!!!!!!!!!)
ops$tkyte%ORA11GR2> create or replace package settable_sysdate
2 is
3 procedure set_date(in_date in DATE);
4 function get_date return date;
5 end settable_sysdate;
6 /
Package created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace package body settable_sysdate
2 is
3
4 g_date date;
5
6 procedure set_date(in_date in date)
7 is
8 begin
9 dbms_session.set_context
10 ( 'my_ctx', 'the_date', to_char(in_date, 'yyyymmddhh24miss' ) );
11 g_date := in_date;
12 end;
13
14
15 function get_date return date
16 is
17 begin
18 return nvl( g_date, sysdate );
19 end;
20
21 end settable_sysdate;
22 /
Package body created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec settable_sysdate.set_date( sysdate-1 )
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> @trace
ops$tkyte%ORA11GR2> alter session set events '10046 trace name context forever, level 12';
Session altered.
ops$tkyte%ORA11GR2> select distinct settable_sysdate.get_date()
2 from t;
SETTABLE_
---------
15-JUL-13
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select distinct (select settable_sysdate.get_date() from dual)
2 from t;
(SELECTSE
---------
15-JUL-13
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select distinct nvl( to_date( sys_context( 'my_ctx', 'the_date' ), 'yyyymmddhh24miss' ), sysdate)
2 from t;
NVL(TO_DA
---------
15-JUL-13
both the scalar subquery and the sys_context call do about the same amount of work:
select distinct settable_sysdate.get_date() from t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 4.70 5.35 14659 14663 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 4.70 5.35 14659 14664 0 1
********************************************************************************
select distinct (select settable_sysdate.get_date() from dual) from t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.23 0.23 14659 14663 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.23 0.24 14659 14665 0 1
********************************************************************************
select distinct nvl( to_date( sys_context( 'my_ctx', 'the_date' ), 'yyyymmddhh24miss' ), sysdate)
from t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.22 0.22 14659 14663 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.22 0.22 14659 14664 0 1
At least I was on the right track
Butch "shoulda used a fake name" Wesley, July 17, 2013 - 1:16 pm UTC
All right, all right...what you've never over-engineered a simple solution right out of the gate? I definitely should have thought that through a lot more before posting. That's what I get for enjoying you blasting others. Thanks for responding and letting me know I was on the right path.
July 17, 2013 - 6:09 pm UTC
whenever I see when others not followed by raise or raise_application_error - expect a lot of !!!!!!!'s :)
PL/SQL sysdate
David Aldridge, July 24, 2013 - 9:41 pm UTC
Tom, I don't think that your suggested settable_sysdate quite gets over the problem of multiple sysdate values being generated for a long running query when no pseudo-sysdate has been set, at least not in the absence of result caching.
I suppose you could probably:
i) set a sys_context to ensure that a single value of the actual sysdate is returned for such queries, or
ii) pass in sysdate to settable_sysdate.get_date as a parameter from SQL and have the function return that, or
iii) return g_date from the function, and use coalesce(settable_sysdate.get_date,sysdate) in the SQL.
Probably (ii), I'd think.
August 02, 2013 - 5:03 pm UTC
ah, you are correct - but easy enough to work around.
use a scalar subquery:
where x = (select setttable_sysdate from dual)
instead of
where x = settable_sysdate
that'll have the function get called once per query, not any more.
http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html that is a good idea when calling plsql from sql in general, use a scalar subquery to invoke it.
fetch client pc date
A reader, May 16, 2019 - 6:30 am UTC
ON my database server date is 16-05-2019 and my client pc date is 15-05-2019 so have any in-built function to fetch client PC date?
May 16, 2019 - 9:01 am UTC
You can use current_date/timestamp to get the time in the client's timezone.
For example, my DB server is in a US/Pacific timezone. But my client is Europe/London:
select current_date, sysdate ,
current_timestamp, systimestamp
from dual;
CURRENT_DATE SYSDATE CURRENT_TIMESTAMP SYSTIMESTAMP
16-MAY-2019 09:58:38 16-MAY-2019 01:58:38 16-MAY-2019 09.58.38 +01 16-MAY-2019 01.58.38 -07