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
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 ?
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
)
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.
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.
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
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.
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.
June 12, 2020 - 1:07 pm UTC