Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Tekle .

Asked: March 14, 2020 - 4:40 am UTC

Last updated: June 12, 2020 - 1:07 pm UTC

Version: 12c

Viewed 1000+ times

You Asked

In oracle databse 12c it says it suppirt ethiopian calander.in my database I have one employee table in that I have one column hiredate which date data type until i want to insert ethiopian calander date type data how it is possible?

and we said...

First, please note that all datetime values are stored in an Oracle Database in the Gregorian form. The support for user (i.e. non-Gregorian) calendars is for text-to-datetime and text-from-datetime conversion (explicit and implicit) and in a small number of SQL functions, such as ADD_MONTHS.

You specify the user calendar with the parameter NLS_CALENDAR. This parameter can be set at the session level with ALTER SESSION or it can be passed to the explicit conversion functions TO_CHAR, TO_DATE, TO_TIMESTAMP, and TO_TIMESTAMP_TZ in their third parameter. The implicit conversion is affected by the session-level NLS_CALENDAR, the explicit conversion (the mentioned functions) is affected either by the third parameter, if specified, or by the session-level parameter.

Example:

CREATE TABLE t1(c1 DATE);
INSERT INTO t1(c1) VALUES (TO_DATE('2012-07-10','YYYY-MM-DD'));
SELECT TO_CHAR(c1,'YYYY-MM-DD','NLS_CALENDAR=GREGORIAN') "Date" from t1;


The result of the query is:

Date
----------
2020-03-19


The string '2012-07-10' is interpreted in the Ethiopian calendar set with ALTER SESSION, because no third parameter has been specified in the TO_DATE call. The inserted value, when converted back to string in the following query, is interpreted in the Gregorian calendar, as the calendar has been set through the third parameter of TO_CHAR.

Please note while SQL*Plus has full support for user calendars, including Ethiopian (version must be 12.1+), SQL Developer does not have this support. Therefore, you have to use explicit calls to TO_CHAR in queries to have the database do the conversion. If relying on implicit conversion, as in SELECT c1 FROM t1, you will always see Gregorian dates - the session NLS_CALENDAR will be ignored. This problem is also visible in the Data tab of a table editor in SQL Developer - any data entry and display is in Gregorian calendar.


Rating

  (14 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Pl/sql

Tekle, March 29, 2020 - 4:41 am UTC

How to retrieve single record from a table using oracle 12c stored procedure for example in mysql
create procedure so
As
Select * from emo where eid=100
Is there any simple way like in oracle without using variables
Connor McDonald
March 30, 2020 - 12:17 am UTC

Probably ref cursors are closest to what you are after, either as a parameter, eg

procedure P (rc in out sys_refcursor) is
begin
  open rc for select * from emp where eid = 100;
end;


or implicitly

procedure P  is
 rc sys_refcursor;
begin
  open rc for select * from emp where eid = 100;
  dbms_sql.return_result(rc);
end;


Pl/sql

Tekle, March 29, 2020 - 4:57 am UTC

oracle support for biometrics (eye and fingerprint) of it supports which devices for both eye and fingerprint best suitable for oracle?And how can we store them ?
Connor McDonald
March 30, 2020 - 12:19 am UTC

The really depends totally on the client technology you intend to use. Most of these will have an API including what data will be outputted by the reader. Sometimes its a number, sometimes raw data etc.

That will define how to chose to store it and match against it.

And how this relates to the original question is .... well....dubious :-)

A reader, April 27, 2020 - 8:07 am UTC

In oracle 12c in documentation it says it support Ethiopia calendar and ethiopia has 13 months for 12month it works fine but the last month it says not valid month.can you please show me with simple example?
Create table month
(
From date
)

Connor McDonald
April 28, 2020 - 3:19 am UTC

SQL> insert into t1 values ( to_date('2020-13-01','YYYY-MM-DD','NLS_CALENDAR=ETHIOPIAN')) ;

1 row created.


Pl/sql

Tekle, April 28, 2020 - 7:47 am UTC

Can you please show me how oracle supports Ethiopian 13th month it says not valid month can you please show me with example I tried like you told me but it doesn't work thanks.

Create table t
(
D date;
)

Pl/sql

Tekle, April 28, 2020 - 8:23 am UTC

If I insert 2012-13-5 into the table when I s lect I should see same shouldn't be converted into Gregorian.
Connor McDonald
April 29, 2020 - 2:47 am UTC

That depends on your session settings.

Your client sets the language/date format etc etc.

Set NLS_CALENDAR in your environment/registry etc

A reader, April 29, 2020 - 10:18 am UTC

For all Ethiopian calendar it works only cor the last 13th month it doesn't work. Can you please show me step by step how to fix it it says not a valid month for example I'd I insert 2012-13-4 I should see that in all month it works but when I insert this it says not valid month.
Chris Saxon
April 29, 2020 - 4:24 pm UTC

Can you show us step-by-step exactly what you're doing?

It's likely there's an implicit conversion going on somewhere. But without seeing your code, it's kinda hard to know where ;)

Also include your session's NLS settings. i.e. the output from:
select * from nls_session_parameters;



To Tekle

J. Laurindo Chiappa, April 29, 2020 - 1:44 pm UTC

Hello : as said in https://www.oracle.com/technetwork/database/database-technologies/globalization/globalization12c-datasheet-1968798.pdf , since 12c the Oracle database (and of course the Oracle client software and the Oracle native front-end tools, such as sql*plus) supports Ethiopian calendar - so, IF you are testing in a 12c (or higher) database, connecting with Oracle client software 12c (or higher) via the native front-end tool (sql*plus 12c or higher), an INSERT such:

INSERT INTO t1 (columndate) values(to_date('05/13/2012','DD/MM/YYYY','nls_calendar = ''ETHIOPIAN''');

should work... IF this don´t work for you AND you are using 12c or higher Oracle softwares, you Must open a ticket in Oracle support....

Regards,

Chiappa

Ops...

J. Laurindo Chiappa, April 29, 2020 - 1:48 pm UTC

Sorry, I made a mistake : the correct argument for the CALENDAR portion inside the TO_DATE function should be : 'NLS_CALENDAR=ETHIOPIAN'
I put some extra chars...

Regards,

Chiappa

Pl/sql

Tekle, April 30, 2020 - 4:37 pm UTC

First of all thanks for your reply.And my doubt is to_date(2012-3-11.'YYYY-MM-DD') works but when I save ti_date('2012-13-4') it says not valid month I am confused here

Pl/sql

Tekle, April 30, 2020 - 4:47 pm UTC

Without using nls_calendar parameter is it possible to say like this
to_date(2012-13-1 ) it gave me not valid month but with nls-calendar it works why ?

It doesn't matter...

J. Laurindo Chiappa, May 03, 2020 - 12:00 am UTC

In the SQL or PL/SQL languages, the Concepts are the same : each and all session connected to the Oracle database have some NLS settings, such as :

SYSTEM@xepdb1::CONTAINER=XEPDB1> select * from nls_session_parameters;

PARAMETER VALUE
------------------------- --------------------------------------------
NLS_LANGUAGE BRAZILIAN PORTUGUESE
NLS_TERRITORY BRAZIL
NLS_CURRENCY R$
NLS_ISO_CURRENCY BRAZIL
NLS_NUMERIC_CHARACTERS ,.
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT dd/mm/yyyy
NLS_DATE_LANGUAGE BRAZILIAN PORTUGUESE
NLS_SORT WEST_EUROPEAN
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD/MM/RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD/MM/RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY Cr$
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

17 linhas selecionadas.

SYSTEM@xepdb1::CONTAINER=XEPDB1>

IF you ask for a date convertion without to indicate the desired format mask, the session defaults WILL be used... As you see above, IN MY CASE I have NLS_CALENDAR=GREGORIAN and NLS_DATE_FORMAT=DD/MM/YYY , so IF I don´t inform anything the calendar (as you said), the database will assume GREGORIAN calendar (where we have ONLY 12 months) and IF I don´t inform a date format, dd/mm/yyyy will be used....
Certainly THIS is the reason why your TO_DATE without the CALENDAR parameter give an erro to you BUT WITH the CALENDAR=ETHIOPIAN worked...

Best regards,

Chiappa

A demonstration...

J. Laurindo Chiappa, May 03, 2020 - 12:16 am UTC

Before the example, I must repeat : the knowledge about Ethipian calendar was added in the Oracle database (and thus in the Oracle client sofwtare and Oracle native tools) in version 12c - in my case I´m using Oracle XE 18c and sql*plus 18.4.x ...
Now the example :

SYSTEM@xepdb1::CONTAINER=XEPDB1> create table TAB_ETHIOP_DATE(c1 date);

Table created.

SYSTEM@xepdb1::CONTAINER=XEPDB1> insert into TAB_ETHIOP_DATE (c1) values (to_date('2012-3-11','YYYY-MM-DD', 'NLS_CALENDAR=ETHIOPIAN') ) ;

1 row created.

SYSTEM@xepdb1::CONTAINER=XEPDB1> select * from TAB_ETHIOP_DATE;

C1
----------
21/11/2019

1 row selected.

SYSTEM@xepdb1::CONTAINER=XEPDB1>

==> Not only TO_DATE accepted the 13th month BUT if you look at https://melaku.ml/ correctly Thursday, Hidar 11, 2012 was converted by TO_DATE to Thu, 21 Nov 2019 in the Gregorian/Western calendar.... And it serves as a proof for your FIRST answer : "First, please note that all datetime values are stored in an Oracle Database in the Gregorian form. The support for user (i.e. non-Gregorian) calendars is for text-to-datetime and text-from-datetime conversion (explicit and implicit)".... all right ?

Regards,

Chiappa
Connor McDonald
May 04, 2020 - 3:32 am UTC

nice stuff

Pl/sql

Tekle, May 06, 2020 - 11:42 am UTC

I am using oracle and spring and in spring boot ups I do not know how to accept the data which pl/Sql procedure or function returns to spring boot jpa can you please show me the code first for ll/sql function to return all table data then spring boot jpa code to accept return data.
Thanks.
Connor McDonald
May 07, 2020 - 4:24 am UTC

Pl/sql

A reader, June 12, 2020 - 4:46 am UTC

I want to buy licensed oracle 12c database I just want to know the cost.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database