Delving into National Language Support Parameters

It is obvious that individual clients working with applications can be located anywhere in the world. It is therefore necessary to prepare the platform so that all clients can use their own customary rules and regional customs. National Language Support (NLS) parameters determine the locale-specific runtime behavior on clients and servers. They can be set either for the server instance, specified in the initialization file applied during the instance startup (INIT.ORA), or for the client via environment variables overriding the default (server) values. It is done by executing the alter session command. Additionally, the definition can be enhanced by the statement level.

NLS parameters enhance the processing by focusing on local definitions, regional specifics, and rules. This chapter mainly focuses on the parameters related to date and time management, but also summarizes the main principles in a general manner, including local language...

NLS parameter overview

Various principles are used around the world for date and time management. Regional rules are applied, such as considering either Monday or Sunday the first day of the week, using a specific format for presenting date and time values (such as element delimiters), using specific time zones, and so on. To make a date and time system generalizable and applicable to any region by implementing local principles and rules, it is necessary to provide an interface that’s appropriate for the database, as well as the client side. And this is exactly where NLS parameters come onto the stage. These parameters can be set for the whole database, or each client can configure the parameters for themselves. Throughout the following sections, you will learn the proper definitions of individual parameters, values used, formats, and the impacts these have on the processing and output.

The following table shows a list of the individual parameters with their descriptions...

Exploring NLS parameters and their impact

In order to be able to prepare a universal solution that can be deployed anywhere in the world, it is necessary to know the regional characteristics and formats and adapt the solution to your specific environment and customs. The aim of this section is to introduce you to individual NLS parameters and show you how to create a robust solution for various specific elements of date and time processing and representation.

We will look closely at parameters directly related to date and time processing. Toward the end of the chapter, we will see a summary of a few other parameters that are outside of the scope of this book.

We will start with NLS_DATE_FORMAT, followed by NLS_DATE_LANGUAGE, NLS_CALENDAR, and NLS_TERRITORY. By learning about them, you will be able to implement sophisticated solutions directly tailored to specific clients’ requirements in any region and on a global scale.

NLS_DATE_FORMAT parameter

For date and time...

Embedding NLS parameters in the TO_CHAR function

To create a complex solution, the third parameter of TO_CHAR comes onto the scene. The language defined at the system, client, or session level needs to be relevant for applications that use durable database connections – but not only for them. Changing the date language can negatively impact already existing modules. If the user is not aware of the potential consequences, such an approach can be risky. Therefore, changing the processed language for the particular statement is more useful and robust. The third parameter (nls_parameter) of the TO_DATE and TO_CHAR conversion functions deals with the processed language:

TO_CHAR(<value> [, <format> [, <nls_parameter>]]

The main advantage of setting nls_parameter for the particular statement is robustness. It does not impact any other statements and strategies and only operates inside the proposed IS_WORKDAY function. Thus, correct results for whether a given...

Other NLS parameters

The currently configured NLS parameter values can be obtained by querying the NLS_SESSION_PARAMETERS data dictionary view. It consists of two attributes – the parameter name (Parameter) and the associated representation (Value):

...

Summary

In this chapter, you learned about regional settings. Oracle Cloud provides you with multilingual databases adjustable for each user separately. Date and time values are affected by the NLS parameters. NLS_DATE_FORMAT determines the output format and implicit conversions. NLS_DATE_LANGUAGE determines the language of date and time values when textual representations are used. NLS_TERRITORY is responsible for the definition of the week and whether the first day of the week is Monday or Sunday. All these parameters can be set on the session level or inherited from the system itself. A specific approach to the TO_CHAR function, embedding the NLS parameter inside the call, was also explored. Using this, the session configuration will not be affected, with the original parameters remaining in force.

This chapter concludes Part 3 of the book – Modeling, Storing, and Managing Date and Time, dealing with the principles of storage, construction, conversion, extraction, and...

Questions

1. What is the scope of the NLS_DATE_FORMAT parameter applied to the whole system?
   1. SPFILE only
   2. Memory only
   3. SPFILE and memory
   4. Physical database only
2. Which of the following parameters determines the first day of the week?
   1. NLS_CALENDAR
   2. NLS_TERRITORY
   3. NLS_REGION
   4. NLS_WEEK
3. Which NLS parameter can be embedded in the TO_CHAR function?
   1. NLS_DATE_FORMAT
   2. NLS_DATE_LANGUAGE
   3. NLS_TERRITORY
   4. NLS_CALENDAR
4. What is the name of the data dictionary that holds NLS parameter values for the system?
   1. NLS_SESSION_PARAMETERS
   2. NLS_SYSTEM_PARAMETERS
   3. NLS_PARAMS
   4. NLS_DATABASE_PARAMETERS

Further reading

Consider the following titles to learn more about the topics covered in this chapter:

• The Database Globalization Support Guide, covering NLS parameters as part of the Oracle documentation, can be read using the following link: https://docs.oracle.com/en/database/oracle/oracle-database/21/nlspg/setting-up-globalization-support-environment.html.

• The Globalization of Language in Oracle – National Language Support, by James Koopmann, deals with the Oracle data dictionary views that hold NLS parameters for different levels (database, instance, and session levels). It also emphasizes the NLS_LANG parameter, which can cause problems during maintenance and export if the value is not properly set and referenced. It provides two export scenarios and related instances of exception raising: https://www.databasejournal.com/oracle/the-globalization-of-language-in-oracle-national-language-support/.

...

The rest of the chapter is locked

Unlock this book and the full library FREE for 7 days

Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of

Renews at $15.99/month. Cancel anytime

Start free trial

Previous Chapter

Chapter 1 of 26

Next Chapter

You have been reading a chapter from

Developing Robust Date and Time Oriented Applications in Oracle Cloud

Published in: May 2023 Publisher: Packt ISBN-13: 9781804611869

© 2023 Packt Publishing Limited All Rights Reserved

Register for a free Packt account to unlock a world of extra content!

A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.

Sign up now

Unlock this book and the full library FREE for 7 days

Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of

Start free trial

Renews at $15.99/month. Cancel anytime}

Authors (1)

Michal Kvet

Michal Kvet is a researcher, educator, and database expert at the University of Žilina in Slovakia. His primary focus areas are databases, analytics, performance, and cloud computing. He works closely with Oracle and Oracle Academy. He is the co-author of multiple textbooks (a SQL and PL/SQL cookbook, a book on APEX application development, a book on temporal databases, and a MySQL cookbook), coordinates multiple Erasmus+ projects and co-organizes several research conferences and database workshops. Besides this, he supervises engineering projects and bachelor's, master's, and doctoral theses. Over the years, his research has been associated with date and time management and temporal databases. He has Oracle's SQL, PL/SQL, Cloud, Analytics, and Administration certifications. His core knowledge of temporality is provided to you in this book.

Read more

See other products by Michal Kvet

Personalised recommendations for you

Based on your interests and search pattern

Et al.

Ever wonder why speech recognition systems don't understand the Scottish accent, or what would happen if an astronaut only ate mac 'n' cheese, or other spurious reflections you'd have at a bar? We did, then collated those deliberations into absurd research articles with fake figures and methodologies inspired by even more fictionally absurd studies.

Aug 2023 7 hours 40 minutes

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

Dec 2023 12 hours 0 minutes

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

Dec 2023 12 hours 0 minutes

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

Dec 2023 12 hours 0 minutes

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

Dec 2023 12 hours 0 minutes

Mastering Tableau 2023

This book is a comprehensive resource to mastering your Tableau skills and becoming a BI expert. As you progress, you will learn how to build advanced dashboards and improve your storytelling to derive key business insight, as well as make you well-versed with advanced functionalities of Tableau in the business intelligence domain.

Aug 2023 22 hours 48 minutes

Building AI Applications with ChatGPT APIs

This guide covers all ChatGPT API features for effortless creation of robust AI powered apps. With its help, you’ll be able to leverage ChatGPT’s cutting-edge NLP models to take your app development skills to the next level. You’ll also work on ten exciting projects that will give you the practical know-how that you can apply to your existing applications.

Sep 2023 8 hours 36 minutes

Building AI Applications with ChatGPT APIs

This guide covers all ChatGPT API features for effortless creation of robust AI powered apps. With its help, you’ll be able to leverage ChatGPT’s cutting-edge NLP models to take your app development skills to the next level. You’ll also work on ten exciting projects that will give you the practical know-how that you can apply to your existing applications.

Sep 2023 8 hours 36 minutes

Data Engineering with AWS

Embark on a journey to master data engineering pipelines on AWS! Our book offers a hands-on experience of AWS services for ingesting, transforming, and consuming data. Whether you're an absolute beginner or someone with basic data engineering experience, this guide is an indispensable resource.

Oct 2023 21 hours 12 minutes

Modern Data Architecture on AWS

Every organization wants an agile, performant, and cost-effective data platform that meets all their current and future business needs. Purpose-built AWS analytics services and their features play a big part in building such a modern data platform. This book brings to you all the design and architectural patterns that’ll help you achieve this goal.

Aug 2023 14 hours 0 minutes

Practical Guide to Applied Conformal Prediction in Python

Discover the power of Conformal Prediction with the "Practical Guide to Applied Conformal Prediction in Python." Master the latest techniques to quantify uncertainty in machine learning and computer vision models, and seamlessly apply them to your industry applications.

Dec 2023 8 hours 0 minutes

TinyML Cookbook

With over 70 project-based recipes, the TinyML Cookbook is a practical guide that will help you to get the most out of your microcontrollers. It provides a comprehensive understanding of the theoretical foundations while giving you hands-on experience training ML models for deployment on Arduino Nano 33 BLE Sense, Raspberry Pi Pico, and SparkFun RedBoard Artemis Nano microcontrollers.

Nov 2023 22 hours 8 minutes

Machine Learning Kubernetes React Python Cybersecurity Unity Game Development Power BI Azure Web Development

Legal

Terms and Conditions Privacy Policy Cookie Policy Shipping Policy Cancellation Policy Return Policy

Support

Help Contact Us

Business

Partnerships

Sponsored eBooks Custom eBooks

Careers Become an author

Packt+ Membership

Subscription DataPro SecPro TechLeaders

United States

Company Address: Packt Publishing Ltd, Grosvenor House, 11 St Paul's Square, Birmingham, B3 1RB © 2024 Packt Publishing Limited All Rights Reserved

Parameter	Value
NLS_LANGUAGE	SLOVAK
NLS_TERRITORY	SLOVAKIA
NLS_CURRENCY	Sk
NLS_ISO_CURRENCY	SLOVAKIA
NLS_NUMERIC_CHARACTERS	,.
NLS_CALENDAR	GREGORIAN
NLS_DATE_FORMAT	DD.MM.YYYY HH24:MI:SS
NLS_DATE_LANGUAGE	ENGLISH

Summary

In this chapter, you learned about regional settings. Oracle Cloud provides you with multilingual databases adjustable for each user separately. Date and time values are affected by the NLS parameters. NLS_DATE_FORMAT determines the output format and implicit conversions. NLS_DATE_LANGUAGE determines the language of date and time values when textual representations are used. NLS_TERRITORY is responsible for the definition of the week and whether the first day of the week is Monday or Sunday. All these parameters can be set on the session level or inherited from the system itself. A specific approach to the TO_CHAR function, embedding the NLS parameter inside the call, was also explored. Using this, the session configuration will not be affected, with the original parameters remaining in force.

This chapter concludes Part 3 of the book – Modeling, Storing, and Managing Date and Time, dealing with the principles of storage, construction, conversion, extraction, and...

Questions

1. What is the scope of the NLS_DATE_FORMAT parameter applied to the whole system?
   1. SPFILE only
   2. Memory only
   3. SPFILE and memory
   4. Physical database only
2. Which of the following parameters determines the first day of the week?
   1. NLS_CALENDAR
   2. NLS_TERRITORY
   3. NLS_REGION
   4. NLS_WEEK
3. Which NLS parameter can be embedded in the TO_CHAR function?
   1. NLS_DATE_FORMAT
   2. NLS_DATE_LANGUAGE
   3. NLS_TERRITORY
   4. NLS_CALENDAR
4. What is the name of the data dictionary that holds NLS parameter values for the system?
   1. NLS_SESSION_PARAMETERS
   2. NLS_SYSTEM_PARAMETERS
   3. NLS_PARAMS
   4. NLS_DATABASE_PARAMETERS

Further reading

Consider the following titles to learn more about the topics covered in this chapter:

• The Database Globalization Support Guide, covering NLS parameters as part of the Oracle documentation, can be read using the following link: https://docs.oracle.com/en/database/oracle/oracle-database/21/nlspg/setting-up-globalization-support-environment.html.

• The Globalization of Language in Oracle – National Language Support, by James Koopmann, deals with the Oracle data dictionary views that hold NLS parameters for different levels (database, instance, and session levels). It also emphasizes the NLS_LANG parameter, which can cause problems during maintenance and export if the value is not properly set and referenced. It provides two export scenarios and related instances of exception raising: https://www.databasejournal.com/oracle/the-globalization-of-language-in-oracle-national-language-support/.

...