Hello Dear Tom
As you know in oracle 12c Iran territory is supported.
col PARAMETER format a30;
col VALUE format a30;
select * from v$nls_valid_values where lower(VALUE) in ('iran','persian');
PARAMETER VALUE ISDEP CON_ID
------------------------------ ------------------------------ ----- ----------
LANGUAGE PERSIAN FALSE 0
TERRITORY IRAN FALSE 0
I intend to create a partitioned table based on Persian months interval with DDL below:
create table
pos_data (
start_date DATE
)
PARTITION BY RANGE (start_date)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('01-12-1395', 'DD-MM-YYYY', 'nls_calendar=persian'))
);
Table created.
and I inset 1000 rows in the table
begin
for i in 0..1000 loop
insert into pos_data values (sysdate + i);
end loop;
commit;
end;
/
and when I select high_value, I expect the output is
nls_calndar=persian but the real output is:
select high_value from USER_TAB_PARTITIONS;
TO_DATE(' 1395-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TO_DATE(' 2017-03-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TO_DATE(' 2017-04-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TO_DATE(' 2017-05-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TO_DATE(' 2017-06-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TO_DATE(' 2017-07-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TO_DATE(' 2017-08-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TO_DATE(' 2017-09-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
.
.
.
and current nls_database_parameters and nls_instance_parameters is set to:
select * from NLS_DATABASE_PARAMETERS;
PARAMETER VALUE
------------------------------ ------------------------------
NLS_RDBMS_VERSION 12.2.0.1.0
NLS_NCHAR_CONV_EXCP FALSE
NLS_LENGTH_SEMANTICS BYTE
NLS_COMP BINARY
NLS_DUAL_CURRENCY $
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_SORT BINARY
NLS_DATE_LANGUAGE AMERICAN
NLS_DATE_FORMAT DD-MON-RR
NLS_CALENDAR GREGORIAN
NLS_NUMERIC_CHARACTERS .,
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET AL32UTF8
NLS_ISO_CURRENCY AMERICA
NLS_CURRENCY $
NLS_TERRITORY AMERICA
NLS_LANGUAGE AMERICAN
20 rows selected.
select * from NLS_instance_parameters;
PARAMETER VALUE
------------------------------ ------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY IRAN
NLS_SORT
NLS_DATE_LANGUAGE PERSIAN
NLS_DATE_FORMAT
NLS_CURRENCY
NLS_NUMERIC_CHARACTERS
NLS_ISO_CURRENCY
NLS_CALENDAR PERSIAN
NLS_TIME_FORMAT
NLS_TIMESTAMP_FORMAT
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_DUAL_CURRENCY
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
17 rows selected.
I would like to know, where is the problem ?
best regards,
Masoud
The key thing there is your NLS_DATABASE_PARAMETERS
NLS_CALENDAR GREGORIAN
This is the calendar that was set when your database was *created*.
Your instance level parameters will always override this, but since you can *change* the instance parameters whenever you want, it would appear that the internal dictionary structures are going to store/represent their values in the base database calendar so that they always remain consistent.
As far as I can tell (ie, my persian calendar knowledge is not great), the definitions look correct, interval or not
SQL> create table pos_data (
2 start_date DATE
3 )
4 PARTITION BY RANGE (start_date)
5 (
6 PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('01-12-1395', 'DD-MM-YYYY', 'nls_calendar=persian'))
7 );
Table created.
SQL>
SQL> col high_value format a90
SQL> set lines 200
SQL>
SQL> select partition_name, high_value
2 from user_tab_partitions where table_name = 'POS_DATA';
PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------------------------------------------------------------------
POS_DATA_P2 TO_DATE(' 2017-02-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
1 row selected.
SQL>
SQL> drop table pos_Data purge;
Table dropped.
SQL>
SQL> create table pos_data (
2 start_date DATE
3 )
4 PARTITION BY RANGE (start_date)
5 INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
6 (
7 PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('01-12-1395', 'DD-MM-YYYY', 'nls_calendar=persian'))
8 );
Table created.
SQL>
SQL> select partition_name, high_value
2 from user_tab_partitions where table_name = 'POS_DATA';
PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------------------------------------------------------------------
POS_DATA_P2 TO_DATE(' 2017-02-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
1 row selected.
SQL>