Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to Bring one particular SQL to AWR report

Raj AruNov 26 2019 — edited Nov 27 2019

I am looking to bring the select statement  sql which I am running in the database to AWR report Is there any way to pin a sql to awr report

This post has been answered by AndrewSayer on Nov 26 2019
Jump to Answer

Comments

unknown-7404

+1 - the entire set of functions should be standardized so they work consistently.

Sven W.

Nls param is missing. However the other functions like to_char or to-date also do not support all the nls_settings. For example nls_territory is missing and this is one that would be needed for NEXT_DAY.

I agree the docs are misleading. Although one could argue, that "day of the week" means '1' to '7'.

https://docs.oracle.com/database/121/SQLRF/functions118.htm#SQLRF00672

Purpose

NEXT_DAY returns the date of the first weekday named by char that is later than the date date. The return type is always DATE, regardless of the data type of date. The argument char must be a day of the week in the date language of your session, either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version. Any characters immediately following the valid abbreviation are ignored. The return value has the same hours, minutes, and seconds component as the argument date.

Format models: https://docs.oracle.com/database/121/SQLRF/sql_elements004.htm#SQLRF00212

D 

Yes

Day of week (1-7). This element depends on the NLS territory of the session.

DAY

Yes

Name of day.

DD 

Yes

Day of month (1-31).

DDD 

Yes

Day of year (1-366).

...
DY 

Yes

Abbreviated name of day.

I added some feedback to the documentation page.

User_5IN9J

I think thi is indipendent of territory:
SELECT TRUNC(NEXT_DAY(SYSDATE, TO_CHAR(TRUNC(SYSDATE, 'IW'), 'DAY'))) FROM dual; -- next monday
SELECT TRUNC(NEXT_DAY(SYSDATE, TO_CHAR(TRUNC(SYSDATE, 'IW') + 1, 'DAY'))) FROM dual; -- next tuesday
SELECT TRUNC(NEXT_DAY(SYSDATE, TO_CHAR(TRUNC(SYSDATE, 'IW') + 2, 'DAY'))) FROM dual; -- next wednesday
is it?

mathguy

NEXT_DAY doesn't truncate the time-of-day to midnight; so you shouldn't truncate in your formula, either.
Other than that, the computation is correct - but that's not the point. There are other ways to get the same result. The request is to have an nls_date_language parameter so that we don't have to use such formulas in our code. Since that is already supported for almost all other date functions, this addition can't be too complex for Oracle.

1 - 4

Post Details

Added on Nov 26 2019
5 comments
1,150 views