Book Image

Developing Robust Date and Time Oriented Applications in Oracle Cloud

By : Michal Kvet

Book Image

Developing Robust Date and Time Oriented Applications in Oracle Cloud

By: Michal Kvet

Overview of this book

Proper date and time management is critical for the development and reliability of Oracle Databases and cloud environments, which are among the most rapidly expanding technologies today. This knowledge can be applied to cloud technology, on premises, application development, and integration to emphasize regional settings, UTC coordination, or different time zones. This practical book focuses on code snippets and discusses the existing functionalities and limitations, along with covering data migration to the cloud by emphasizing the importance of proper date and time management. This book helps you understand the historical background and evolution of ANSI standards. You’ll get to grips with data types, constructor principles, and existing functionalities, and focus on the limitations of regional parameters and time zones, which help in expanding business to other parts of the world. You’ll also explore SQL injection threats, temporal database architecture, using Flashback Technology to reconstruct valid database images from the past, time zone management, and UTC synchronization across regions. By the end of this book, you’ll be able to create and manage temporal systems, prevent SQL injection attacks, use existing functionalities and define your own robust solutions for date management, and apply time zone and region rules.

Preface

Who this book is for

What this book covers

To get the most out of this book

Download the example code files

Download the color images

Conventions used

Share Your Thoughts

Download a free PDF copy of this book

Part 1: Discovering Oracle Cloud

Part 1: Discovering Oracle Cloud

Free Chapter

Chapter 1: Oracle Cloud Fundamentals

Chapter 1: Oracle Cloud Fundamentals

Oracle Cloud concepts

Understanding the deployment models

Shared versus dedicated architecture

Oracle Cloud terminology

Database provisioning

Connecting to the ADB

Database system architecture overview

Further reading

Chapter 2: Data Loading and Migration Perspectives

Chapter 2: Data Loading and Migration Perspectives

Understanding SQL Loader

Getting to know client-side import/export

Examining server-side import/export using dump files

Understanding Transportable Tablespace Data Pump

Exploring migration techniques

Further reading

Part 2: Understanding the Roots of Date and Time

Part 2: Understanding the Roots of Date and Time

Chapter 3: Date and Time Standardization Principles

Chapter 3: Date and Time Standardization Principles

Understanding relational database integrity

The ISO 8601 standard

Date value definition

Time element modeling and management

Composite date and time value

Periods of time

Further reading

Chapter 4: Concepts of Temporality

Chapter 4: Concepts of Temporality

What is temporality?

Introducing DST

Introducing UTC

Time zone perspective

Gregorian versus Julian calendar

Leap years in the Gregorian calendar

Further reading

Part 3: Modeling, Storing, and Managing Date and Time

Part 3: Modeling, Storing, and Managing Date and Time

Chapter 5: Modeling and Storage Principles

Chapter 5: Modeling and Storage Principles

Exploring data types

Exploring constructor functions

Storing the date as an INTEGER value

Getting the actual date and time values

Getting to know DATE arithmetic

Understanding the INTERVAL data type

TIMESTAMP arithmetic

Further reading

Chapter 6: Conversion Functions and Element Extraction

Chapter 6: Conversion Functions and Element Extraction

Understanding the TO_CHAR and TO_DATE conversion functions

Understanding the EXTRACT function

Reliability and integrity issues

Investigating the CAST function

Validating conversions

Further reading

Chapter 7: Date and Time Management Functions

Chapter 7: Date and Time Management Functions

Getting to know the ADD_MONTHS function

Identifying the number of days in a month using LAST_DAY

Understanding the usage of the MONTHS_BETWEEN function

Exploring the NEXT_DAY and NEXT_DATE functions

Investigating the TRUNC function

Understanding the usage of the ROUND function

Understanding the PERSONAL_ID concept and birthday management

Generating random dates

Further reading

Chapter 8: Delving into National Language Support Parameters

Chapter 8: Delving into National Language Support Parameters

NLS parameter overview

Exploring NLS parameters and their impact

Embedding NLS parameters in the TO_CHAR function

Other NLS parameters

Further reading

Part 4: Modeling Validity Intervals

Part 4: Modeling Validity Intervals

Chapter 9: Duration Modeling and Calculations

Chapter 9: Duration Modeling and Calculations

What timepoint means and how to use it

Deploying duration models using timepoint borders

Modeling unlimited validity

Further reading

Chapter 10: Interval Representation and Type Relationships

Chapter 10: Interval Representation and Type Relationships

Relationships between time interval representations

Using positional relations in temporal space

Modeling temporal validity using Oracle Database’s embedded PERIOD

Further reading

Chapter 11: Temporal Database Concepts

Chapter 11: Temporal Database Concepts

The origin and evolution of temporal models

The object-oriented temporal approach

The temporal aspect of management

Temporal system requirements

Exploring temporal dimensions

The attribute-oriented approach

The group-level temporal system

Conventional table with time-delimited attributes

Further reading

Chapter 12: Building Month Calendars Using SQL and PL/SQL

Chapter 12: Building Month Calendars Using SQL and PL/SQL

Defining a calendar using PL/SQL

Modeling a calendar using SQL

Name day management

Further reading

Part 5: Building Robust and Secure Temporal Solutions

Part 5: Building Robust and Secure Temporal Solutions

Chapter 13: Flashback Management for Reconstructing the Database Image

Chapter 13: Flashback Management for Reconstructing the Database Image

Transaction as a change vector source

Transaction log structure and log types

Reconstructing states using Flashback technology

Further reading

Chapter 14: Building Reliable Solutions to Avoid SQL Injection

Chapter 14: Building Reliable Solutions to Avoid SQL Injection

Understanding SQL injection

Solutions to limit SQL injection

Further reading

Part 6: Expanding a Business Worldwide Using Oracle Cloud

Part 6: Expanding a Business Worldwide Using Oracle Cloud

Chapter 15: Timestamp Enhancements

Chapter 15: Timestamp Enhancements

Applying time zones for the server and client sides

TIMESTAMP and transformation across time zones

Transforming DATE values across multiple time zones using the NEW_TIME function

TIMESTAMP normalization

Local value reflection using TIMESTAMP data type extensions

Local versus global expansion

Further reading

Chapter 16: Oracle Cloud Time Zone Reflection

Chapter 16: Oracle Cloud Time Zone Reflection

Summarizing time zone management

Exploring SQL translation profiles

Simplifying time zone management shift using SYSDATE_AT_DBTIMEZONE

Further reading

Assessments

Chapter 1 – Oracle Cloud Fundamentals

Chapter 2 – Data Loading and Migration Perspectives

Chapter 3 – Date and Time Standardization Principles

Chapter 4 – Concepts of Temporality

Chapter 5 – Modeling and Storage Principles

Chapter 6 – Conversion Functions and Element Extraction

Chapter 7 – Date and Time Management Functions

Chapter 8 – Delving into National Language Support Parameters

Chapter 9 – Duration Modeling and Calculations

Chapter 10 – Interval Representation and Type Relationships

Chapter 11 – Temporal Database Concepts

Chapter 12 – Building Month Calendars Using SQL and PL/SQL

Chapter 13 – Flashback Management for Reconstructing the Database Image

Chapter 14 – Building Reliable Solutions to Avoid SQL Injection

Chapter 15 – Timestamp Enhancements

Chapter 16 – Oracle Cloud Time-Zone Reflection

Index

Other Books You May Enjoy

Other Books You May Enjoy

Packt is searching for authors like you

Share Your Thoughts

Download a free PDF copy of this book

Customer Reviews

5 star

0

4 star

0

3 star

0

2 star

0

1 star

0

Who this book is for

There are five main roles and groups that are the target audience of this content:

Students who need to understand the complexity of data processing, its evolution, date and time definition techniques and aspects, and all the consequences of improper definitions and management.
Application developers and testers who need to get insights into individual date and time data types, functionalities, conversion and extraction principles, as well as the complexity of region and time-zone references. Beyond that, they will understand the concepts, limitations, and techniques involved in building reliable solutions to be spread to any region or time zone by referring to time-zone synchronization.
Cloud managers and integrators who need to focus on time-zone shifts with a proper reference for integrating and moving on-premises solutions to the Oracle Cloud environment by gaining the advantages of using autonomous databases and their automated optimization and administration processes.
Database administrators who need to accept the Oracle Cloud fundamentals, migration perspectives, and overall date and time standardization by helping developers optimize their solutions and the performance of systems. Beyond that, they can cooperate with developers to define solutions that do not allow SQL injection, even for date and time processing.
Testers who need to grasp the complexity of proper date and time processing, respecting the regional habits and national language parameters to offer universal solutions that are deployable worldwide.
Naturally, this book is well suited to anyone interested in getting deeper knowledge about date and time processing in Oracle Database to build robust date- and time-oriented applications in Oracle Cloud.