Oracle database 12c sql worshop 2 student guide vol 2 1. All rights reserved. Disclaimer This document contains proprietary information and is protected by copyright and other intellectual property laws. You may copy and print this document solely for your own use in an Oracle training course.
The document may not be modified or altered in any way. Except where your use constitutes "fair use" under copyright law, you may not use, share, download, upload, copy, print, display, perform, reproduce, publish, license, post, transmit, or distribute this document in whole or in part without the express authorization of Oracle.
The information contained in this document is subject to change without notice. This document is not warranted to be error-free. Restricted Rights Notice If this documentation is delivered to the United States Government or anyone using the documentation on behalf of the United States Government, the following notice is applicable: U. Government contract. Other names may be trademarks of their respective owners.
In this lesson, you learn how to use data types similar to DATE that store fractional seconds and track time zones. This lesson addresses some of the datetime functions available in the Oracle database. The hours of the day are measured by the turning of the earth. The time of day at any particular moment depends on where you are. The earth is divided into 24 time zones, one for each hour of the day. UTC is the time standard against which all other time zones in the world are referenced.
It is the same all year round and is not affected by summer time or daylight saving time. The meridian line is an imaginary line that runs from the North Pole to the South Pole.
It is known as zero longitude and it is the line from which all other lines of longitude are measured. All time is measured relative to UTC and all places have a latitude their distance north or south of the equator and a longitude their distance east or west of the Greenwich meridian. Time Zones The image represents the time for each time zone when Greenwich time is The Oracle database supports storing the time zone in your date and time data, as well as fractional seconds.
The time zone values can be set to an absolute offset, a named time zone, a database time zone, or the local time zone. The values returned display the time zone displacement of the SQL session executing the functions.
The time zone displacement is the difference in hours and minutes between local time and UTC. The default is 6. These functions are national language support NLS —sensitive—that is, the results will be in the current NLS calendar and datetime formats. Note: The code example output may vary depending on when the command is run. If omitted, the default database time zone is the operating system time zone.
The example in the slide shows that the session time zone is offset to UTC by —5 hours. Each datetime data type is composed of several of these fields. Datetimes are mutually comparable and assignable only if they have the same datetime fields. The output shows the differences in display. You can specify the fractional seconds precision for time stamp.
If none is specified, as in this example, it defaults to 6. However, the date data type also contains additional information such as hours, minutes, seconds, AM, and PM.
To obtain the date in this format, you can apply a format mask or a function to the date value. When a web-based company guarantees shipping, it can estimate its delivery time based on the time zone of the person placing the order.
Note: The code example output may vary as per the time of run of the command. There are two classes of intervals: year-month intervals and day-time intervals. The actual subset of fields that constitute an interval is called the precision of the interval and is specified in the interval qualifier. Because the number of days in a year is calendar-dependent, the year-month interval is NLS-dependent, whereas day-time interval is NLS-independent. The interval qualifier may also specify the leading field precision, which is the number of digits in the leading or only field, and in case the trailing field is SECOND, it may also specify the fractional seconds precision, which is the number of digits in the fractional part of the SECOND value.
If not specified, the default value for leading field precision is 2 digits, and the default value for fractional seconds precision is 6 digits. The accepted values are 0 through 9. The actual subset of fields that constitute an item of either type of interval is defined by an interval qualifier, and this subset is known as the precision of the item.
Year-month intervals are mutually comparable and assignable only with other year-month intervals, and day-time intervals are mutually comparable and assignable only with other day- time intervals. In these cases, each interval is converted to one of the two supported types. Different values are inserted into it to indicate years and months for various products. When these rows are retrieved from the table, you see a year value separated from the month value by a -. When you extract any of the other values, the value returned is a date in the Gregorian calendar.
When extracting from a datetime with a time zone value, the value returned is in UTC. The return value is dependent on the date when the statement is executed.
The company president, Mr. Fold wants to find out the time in each of these places to make sure that his senior management will be available to attend the meeting. His secretary, Mr. TZD is an abbreviated time zone string with daylight saving information.
TZR represents the time zone region in datetime input strings. The optional nlsparam specifies the language in which month and day names, and abbreviations, are returned. In the example in the slide, the date days and 10 hours after the hire date is obtained.
Most western nations advance the clock ahead one hour during the summer months. This period is called daylight saving time. The nations of the European Union observe daylight saving time, but they call it the summer time period. The Oracle database automatically determines, for any given time zone region, whether daylight saving time is in effect and returns local time values accordingly.
The datetime value is sufficient for the Oracle database to determine whether daylight saving time is in effect for a given region in all cases except boundary cases. A boundary case occurs during the period when daylight saving time goes into or out of effect.
The one-hour interval between AM and AM. When daylight saving time goes out of effect, the time changes from AM back to AM, and the one-hour interval between AM and AM is repeated. For example, daylight saving time ends on October 31, at AM.
Otherwise, an error is raised. A relative offset b. Database time zone c. OS local time zone d. This lesson addressed some of the datetime functions available in the Oracle database. Practice Overview This practice covers using the datetime functions. Schema Description Overall Description The Oracle Database sample schemas portray a sample company that operates worldwide to fill orders for several different products. In this course, you have access to the objects in all the schemas.
However, the emphasis of the examples, demonstrations, and practices is on the Human Resources HR schema. Human Resources HR This is the schema that is used in this course. In the Human Resource HR records, each employee has an identification number, email address, job identification code, salary, and manager. Some employees earn commissions in addition to their salary. The company also tracks information about jobs within the organization.
Each job has an identification code, job title, and a minimum and maximum salary range for the job. Some employees have been with the company for a long time and have held different positions within the company. When an employee resigns, the duration the employee was working for, the job identification number, and the department are recorded. The sample company is regionally diverse, so it tracks the locations of its warehouses and departments. Each employee is assigned to a department, and each department is identified either by a unique department number or a short name.
Each department is associated with one location, and each location has a full address that includes the street name, postal code, city, state or province, and the country code. In places where the departments and warehouses are located, the company records details such as the country name, currency symbol, currency name, and the region where the country is located geographically. In this appendix, you are introduced to the graphical tool called SQL Developer. You learn how to use SQL Developer for your database development tasks.
Oracle SQL Developer is a free graphical tool designed to improve your productivity and simplify the development of everyday database tasks. With just a few clicks, you can easily create and debug stored procedures, test SQL statements, and view optimizer plans. When connected, you can perform operations on objects in the database. The new interface enables you to specify global settings and multiple database settings with different database connections for the Application Express Listener.
SQL Developer provides the option to drag and drop objects by table or column name onto the worksheet. Therefore, it is a cross-platform tool. SQL Developer does not require an installer and you need to simply unzip the downloaded file. General Navigation and Use SQL Developer uses the left side for navigation to find and select objects, and the right side to display information about selected objects. You can customize many aspects of the appearance and behavior of SQL Developer by setting preferences.
Note: You need to define at least one connection to be able to connect to a database schema and issue SQL queries or run procedures and functions. SQL Developer 3. It also contains options related to migrating third-party databases to Oracle. Note: The Run menu also contains options that are relevant when a function or procedure is selected for debugging. A connection is a SQL Developer object that specifies the necessary information for connecting to a specific database as a specific user of that database.
Hi, I read your whole blog. This is very nice. Good to know about the career in qa automation is broad in future. Contact us. About me. DataGuard DataGuard. Exadata Exadata. Automation Shell Scripting Powershell Scripting. Others Infomratica Others. Course Key Notes: 1. Weekdays Classes:. Weekdays batch will be of daily 2 hours. Course duration will be of 30 to 45 days Appx 70 to 90 hours 4. Course contents and details can be found under "Trainings" Tab above.
Course Fees:. Email This BlogThis! Nurez October 4, at PM. Subscribe to: Posts Atom. GI Installation 2. How to connect to PDB? Other names may be trademarks of their respective owners. Open navigation menu. Close suggestions Search Search. User Settings. Skip carousel. Carousel Previous.
Carousel Next. What is Scribd? Oracle Database 11g: Administration Workshop I. Uploaded by Filipe Santos. Document Information click to expand document information Description: This document contains proprietary information and is protected by copyright and other intellectual property laws.
Did you find this document useful? Is this content inappropriate? Report this Document. Description: This document contains proprietary information and is protected by copyright and other intellectual property laws.
Flag for inappropriate content. Download now. Related titles. Carousel Previous Carousel Next. Jump to Page. Search inside document. John Chaves. Allan Naranjo Rojas.
0コメント