Skip to Main Content

Breadcrumb

Question and Answer

Sergiusz Wolicki

Thanks for the question, Tekle .

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

Answered by: Sergiusz Wolicki - Last updated: May 07, 2020 - 4:24 am UTC

Category: SQL - Version: 12c

Viewed 100+ 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.


and you rated our response

  (13 ratings)

Reviews

Pl/sql

March 29, 2020 - 4:41 am UTC

Reviewer: Tekle from Ethiopia

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

Followup  

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

March 29, 2020 - 4:57 am UTC

Reviewer: Tekle from Ethiopia

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

Followup  

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 :-)

April 27, 2020 - 8:07 am UTC

Reviewer: A reader

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

Followup  

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

April 28, 2020 - 7:47 am UTC

Reviewer: Tekle from Ethiopia

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

April 28, 2020 - 8:23 am UTC

Reviewer: Tekle from Ethiopia

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

Followup  

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

April 29, 2020 - 10:18 am UTC

Reviewer: A reader

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

Followup  

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

April 29, 2020 - 1:44 pm UTC

Reviewer: J. Laurindo Chiappa from Sao Paulo, SP, Brazil

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...

April 29, 2020 - 1:48 pm UTC

Reviewer: J. Laurindo Chiappa from Sao Paulo, SP, Brazil

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

April 30, 2020 - 4:37 pm UTC

Reviewer: Tekle

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

April 30, 2020 - 4:47 pm UTC

Reviewer: Tekle

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...

May 03, 2020 - 12:00 am UTC

Reviewer: J. Laurindo Chiappa from Sao Paulo, SP, Brazil

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...

May 03, 2020 - 12:16 am UTC

Reviewer: J. Laurindo Chiappa from Sao Paulo, SP, Brazil

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

Followup  

May 04, 2020 - 3:32 am UTC

nice stuff

Pl/sql

May 06, 2020 - 11:42 am UTC

Reviewer: Tekle from Ethiopia

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

Followup  

May 07, 2020 - 4:24 am UTC

maybe try this

https://medium.com/skillhive/spring-boot-spring-data-jpa-and-oracle-database-c4af89f727e0

We aren't spring experts here

More to Explore

Administration

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