From Oracle SQL Developer's menu go to Tools -- Preferences.
From the Preferences dialog, select Database -- NLS from the left panel.
From the list of NLS parameters, enter
DD-MON-RR HH24:MI:SS
or for 24-Hour,
DD-MON-YY HH24:MI:SS
Or just for an active session use below instead.
alter SESSION set NLS_DATE_FORMAT = 'required_date_format'
i.e.
alter SESSION set NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS'
into the Date Format field.
Save and close the dialog.
Views: 711
Maruti AIR Tech
Kim Berg Hansen, senior developer with Trivadis and an Oracle ACE Director, shares a tip about the proper use of the Oracle Database NLS_LANG environment variable.
https://developer.oracle.com/
https://cloud.oracle.com/en_US/tryit
Views: 310
Oracle Developers
NLS(National Language Support)는 언어 지원과 관련된 파라미터를 지칭하며 SQL*PLUS등에서 확인 하려면 다음과 같이 조회한다.
show parameter nls
NAME TYPE VALUE
----------------------- ------ --------
nls_calendar string //sysdate등에서 보이는 캘린더 데이터 포맷
nls_comp string BINARY //where절, PL/SQL안의 NLS데이터 비교 방법
nls_currency string
nls_date_format string
nls_date_language string
nls_dual_currency string
nls_iso_currency string
nls_language string AMERICAN
nls_length_semantics string BYTE
nls_nchar_conv_excp string FALSE
nls_numeric_characters string
nls_sort string
Views: 51
이종철
https://www.databasestar.com/oracle-timezone-functions/
The Oracle DBTIMEZONE function returns the database timezone offset of the database. It returns it in the format of +/- TZH:TZM, or the time zone region name.
It’s useful to know what timezone the database is in when working with dates and different time zones, as it can impact the queries you write.
The syntax for this function is quite simple:
DBTIMEZONE
There are no parameters - just the function name.
You can’t actually change the timezone of the database using this function. It uses the timezone of the operating system of the server the database runs on.
It’s similar to the SESSIONTIMEZONE function, but that function returns the timezone of your session and not the database server.
It’s also similar to the SYSTIMESTAMP function, but that function returns the date, time, and timezone of the database, where DBTIMEZONE just returns the timezone.
For more information about the Oracle DBTIMEZONE function, including all of the SQL shown in this video and the examples, read the related article here:
https://www.databasestar.com/oracle-timezone-functions/
Views: 113
Database Star
https://www.databasestar.com/oracle-timezone-functions/
The Oracle FROM_TZ function is used to convert a value in a TIMESTAMP data type, and a specific TIME ZONE, to a TIMESTAMP WITH TIME ZONE value.
It’s a helpful conversion function if you work with times and time zones a lot.
The syntax of the FROM_TZ function is:
FROM_TZ ( timestamp_value, timezone_value )
The parameters of this function are:
- timestamp_value: the value in a TIMESTAMP format to convert.
- timezone_value: this is the timezone value that the timestamp_value will be converted in to.
If you want to know what values can be used as a timezone value, you can look in the database view here:
SELECT * FROM v$timezone_names;
For more information about the Oracle FROM_TZ function, including all of the SQL shown in this video and the examples, read the related article here:
https://www.databasestar.com/oracle-timezone-functions/
Views: 66
Database Star
Session 6:
Datatypes In Oracle
ALPHABET : A-Z , a-z
NUMBER : 0-9 (with precision and scale)
DATE / Temporal : any Date and time (Hours, Minutes, Seconds, Mili-seconds, Timestamp, Timezone etc)
Alphabet + Number = Alphanumeric Data
= String / CHARACTER Datatype Category
Number = Numeric Datatype Category
Date = Date Datatype Category
1. CHARACTER Datatype:
CHAR, VARCHAR, NCHAR:
CHAR is fixed length datatype and VARCHAR is Variable length datatype to store character data. i.e. A-Z , a-z , 0-9 , all keyboard characters etc.
The default size is 1 character and it can store maximum up to 2000 bytes.
Example : EName, EmpID, PassportNo, SSN, etc.
EName CHAR(10) := ‘TOM’; wastage of 7 space after the string
EName VARCHAR(10) := ‘TOM’; Spaces can be Reuse which left after the string
NCHAR additionally handles NLS(National Language Support).
Oracle supports a reliable Unicode datatype through NCHAR , NVARCHAR2 , and NCLOB
VARCHAR2, NVARCHAR2:
These are Variable length datatype.
VARCHAR2 handles alphanumeric character string whereas NVARCHAR2 handles alphanumeric character string with NLS(National Language Support).
The default size is 1 character and it can store maximum up to 4000 bytes.
LONG: Variable length string. (Maximum size: 2 GB - 1)
Only one LONG column is allowed per table.
RAW: Variable length binary string (Maximum size 2000 bytes)
LONG RAW: Variable length binary string (Maximum size 2GB)
2. NUMERIC Datatype:
NUMBER:
It stores Numeric values and performs numeric calculations.
NUMBER, NUMBER(n), NUMBER(p,s)
It stores Numbers up to 38 digits of precision.
SeqNo NUMBER; 1, 123, 12345678
EmpID NUMBER(4); 1, 123, 1234
Sal NUMBER(7,2); 23456.78 , 123.45 — correction in video: Sal NUMBER(a7,2); which is wrong please ignore.
1234567 can be a type of NUMBER, NUMBER(7), NUMBER(7,0)
It can store both integer and floating point numbers
NUMERIC(p,s)
FLOAT: Ex: EmpSal FLOAT; FLOAT(7) Decimal Points allowed
DEC(p,s), DECIMAL(p,s) , REAL, DOUBLE PRECISION
INTEGER: Ex: SSN INTEGER; Decimal Points are not allowed
INT, SMALLINT
3. DATE Datatype:
DATE:
It stores DATE(Date, Month, Year) and Time(Hour, Minute, Second, AM/PM) and performs calculations with such data.
Default DATE format in Oracle is “DD-MON-YY”
Based on "Gregorian calendar" where the date ranges from “JAN 1 4712 BC” to “DEC 31 9999 AD”
doj DATE; “18-MAR-2010 12:30:00 PM”
TIMESTAMP: It can store all parameters as DATE datatype and additionally it can have “Fraction of seconds” and
TIMESTAMP WITH TIMEZONE / TIMESTAMP WITHOUT TIMEZONE.
Range from 0-9 digits, the default size is 6.
4. LOB Datatype:
LOB: “Large Object” data.
It can store pictures, motion pictures, Textfiles etc.
CLOB: “Character Large Object” is used to store structured information like a text file with a specific file format.
BLOB: “Binary Large Object” is used to store Un-structured information like Image, JPEG files, MPEG files etc.
BFILE: “Binary File” is used to store the pointer to a specific file / Just store the location of a file.
Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)
Extra Information:
NCLOB : It supports all the character set supported by CLOB and additionally it handles NLS(National Language Support )
Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)
ROWID and UROWID(optional size) Datatype: contains fixed length Binary data.
BBBBBBB.RRRR.FFFFF combination of BLOCK-ROW-DATABASE FILE
Physical and Logical ROWID
Upcoming Session:
Session 7:
Populating Data into Tables(INSERT Statement):
Inserting data into all columns of a table
Inserting data into Required columns of a table
Inserting NULL value into a table
Inserting Special Values(USER / SYSDATE) into a table
Supplying data at runtime(using & and &&)
THANK YOU :)
Views: 135
Prabhat Sahu
Held on April 12 2018
Chris discusses methods for creating auto-increment primary keys and loading CSV data into Oracle Database. The CSV options SQL*Loader, external tables and using SQL Developer.
Start - slides discussing the above
17:00 - handling implicit string-to-date conversions via NLS parameters
20:12 - SQL Developer import/export options
22:57 - Dynamic CSV to columns converter using Polymorphic Table Functions
AskTOM Office Hours offers free, monthly training and tips on how to make the most of Oracle Database, from Oracle product managers, developers and evangelists.
https://asktom.oracle.com/
https://developer.oracle.com/
https://cloud.oracle.com/en_US/tryit
music: bensound.com
Views: 286
Oracle Developers
Oracle SQL Data Conversion Implicit Explicit
Online console used -- https://livesql.oracle.com
Implicit and Explicit data conversion
to_char, to_number, to_date and cast functions
Queries:
/*IMPLICIT DATA CONVERSION EXAMPLES*/
SELECT salary + '10' FROM hr.employees;
SELECT last_name FROM hr.employees WHERE employee_id = '200';
SELECT last_name FROM hr.employees WHERE employee_id = 200;
SELECT last_name FROM hr.employees WHERE hire_date = '03-MAR-97';
Create table birth(name varchar2(15), dob date);
Insert into birth values ('nids', '12/jan/58');
Insert into birth values ('panda', '14/feb/48');
Insert into birth values ('Spider', '14/feb/1958');
Insert into birth values ('agnes', '14/feb/2048');
Select * from birth;
SELECT to_date('12-jan-58') - to_date('14-feb-48') from DUAL;
'01/01/2058','MM/DD/RRRR'
01/01/2058
'01/01/2048','MM/DD/RRRR'
01/01/2048
'01/01/48','MM/DD/RRRR'
01/01/2048
'01/01/58','MM/DD/RRRR'
1/01/1958
'01/01/48','MM/DD/YYYY'
01/01/0048
'01/01/58','MM/DD/YYYY'
01/01/0058
'01/01/58','MM/DD/YY'
01/01/2058
'01/01/48','MM/DD/YY'
01/01/2048
SELECT * FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';
Alter session set nls_date_format = 'DD-MM-RRRR';
Select * from birth;
alter session set nls_date_format = 'DD-MON-YYYY';
Select * from birth;
ALTER session set nls_date_format = 'DD-MON-YY';
SELECT to_date('12-jan-58') - to_date('14-feb-48') from DUAL;
ALTER session set nls_date_format = 'DD-MON-RR';
/*EXPLICIT DATA CONVERSION EXAMPLES*/
/*TO_CHAR, TO_DATE, TO_NUMBER*/
SELECT first_name, TO_CHAR (hire_date, 'MON DD, YYYY') HIRE_DATE, TO_CHAR (salary, '$99999.99') Salary FROM hr.employees;
SELECT TO_CHAR('01110') FROM DUAL;
SELECT TO_CHAR('01110' + 1) FROM DUAL;
SELECT TO_DATE('2012-06-05', 'YYYY-MM-DD') FROM dual;
SELECT TO_DATE( '5 Jan 2017', 'DD MON YYYY' ) FROM dual;
SELECT TO_NUMBER('1210.73', '9999.99') from DUAL;
SELECT TO_NUMBER('$65.169', 'L99.999') FROM DUAL;
SELECT TO_NUMBER('123,456,789', '999,999,999') FROM DUAL;
/*This example takes a complicated string, determines that it is in certain format, and specifies the numeric characters to use*/
SELECT TO_NUMBER('$17 218,00', 'L999G999D00',' NLS_NUMERIC_CHARACTERS='', ''') FROM DUAL;
/*CAST*/
SELECT CAST( '22-Aug-2003' AS varchar2(30) ) from dual;
SELECT CAST('245.205' AS NUMBER(5,2)) FROM dual;
/* -- Result: 245.21 (note that the value is rounded, not truncated to 245.20) */
SELECT CAST(245.205 AS NUMBER(5,2)) FROM dual;
SELECT CAST ('123456' AS NUMBER) + 5 FROM DUAL;
SELECT CAST ('5.05.2017' AS DATE) +5 FROM DUAL;
/*check the default date format. That's why it give error*/
SELECT CAST ('30-APRIL-2015' AS DATE) +5 FROM DUAL;
SELECT CAST (SYSDATE AS TIMESTAMP) FROM DUAL;
SELECT SYSDATE FROM DUAL;
Views: 48
Nids Dixit