Atikh's DBA blog
  • Home
  • Oracle
  • MySQL
  • MongoDB
  • PostgreSQL
  • Snowflake
  • About Me
  • Contact Us

Want to Convert number to text/word form? Here is the solution

 Atikh Shaikh     Oracle 12c, oracle 19c     No comments   

Oracle Database has great functionality that converts any number to its word format, suppose number "2 "you want to write it as "two", you can do it using Oracle database. you may not need it for single double, triple, or quadruple numbers but what if your number is big and it makes you lazy to read it or convert it into Word format, Here is the solution.

Make use of a dummy table dual along with a few other functions such as to_char and to_date, Here is the exact query to convert your number 3434590 into word or text format

SQL> select to_char(to_date(3434590,'j'),'jsp') from dual;

TO_CHAR(TO_DATE(3434590,'J'),'JSP')

------------------------------------------------------------

three million four hundred thirty-four thousand five hundred ninety


Where J stands for Jubilian date and which starts from 1 to 5373484 and JSP stands for Julian dates SPelled(SP)

as there is no direct function in oracle to convert number to words, you can try this method. Here is another simple example

oracle convert number to word



Read More
  • Share This:  
  •  Facebook
  •  Twitter
  •  Instagram
  •  Pin
  •  linkedin
  •  reddit

Oracle 23c : Download and Install on Windows 10

 Atikh Shaikh     oracle, Oracle23c     No comments   

As we have discussed features and release details of Oracle 23c, in the previous article, now we are going to install and make our hands dirty with Oracle database 23c on a virtual machine, Oracle has provided 23c in the form of a Virtual machine

Details and requirements for installation can be found on the Oracle website provided below 

https://www.oracle.com/database/technologies/databaseappdev-vm.html

This is only for testing purposes and should not be used for production deployments

oracle database 23c


Before the actual download of Oracle 23c, download and install the Oracle VM Virtual box from the link 

https://www.oracle.com/virtualization/technologies/vm/downloads/virtualbox-downloads.html 

You can find a complete article about the installation of the Virtual box here

To download Oracle 23c, click on the below, size is around 6.5G, and it will take a while to complete the download


oracle23c


Once Oracle 23c file is downloaded and Virtual box is installed in the system, Open Oracle VM Virtual Box and follow the below steps 

Click on Import appliance

VmWare Import appliances

In the next step, provide the location of Oracle_Database_23c_Free_Developer_Appliance, downloaded earlier

Oracle_Database_23c_Free_Developer_Appliance


Click on Finish and agree to terms

Agree to terms


Wait for the import to be finished

importing oracle appliances


Once the import is completed, you will see below the window, that indicates, the import was successful. 


oracle23c import success

Now click on START to turn on the VM Box

Oracle 23c VM start

The below screen show, installation is going on 

Installation is in progress

Once installation is completed, you will see the below window and readme.txt file which has all connection details 

readme.txt

Open sqlcl to connect to the database using the username and password provided in readme.txt

pluggable database connection

Similarly, you can use the below method to connect to the container database i.e. CDB

Oracle 23c version


Hurray, we tested the connectivity of Oracle 23, lets try to find out new DBA features


Read More
  • Share This:  
  •  Facebook
  •  Twitter
  •  Instagram
  •  Pin
  •  linkedin
  •  reddit

Install Oracle Virtual box(VM Box) on windows system

 Atikh Shaikh     oracle, Virtualbox     No comments   

 In this article, we will discuss the installation of the Oracle virtual box. Use the below link to download the Oracle virtual box 

https://www.oracle.com/virtualization/technologies/vm/downloads/virtualbox-downloads.html

Oracle virtual box

Once the download is finished, you can click on the .exe file 

downloaded virtual box

You will see below the welcome window, follow the regular next, next options, and provide inputs  like size, location, memory, etc if required

Oracle virtual box

Once you click on enough next option, the below status window will appear

status installation

Once installation is completed, you can see the below window, you can simply click on the Finish button.

oracle vmware


That's it!!! we are done with oracle virtual box installation on Windows Oracle machine, Next will be downloading and installing Oracle 23c

Read More
  • Share This:  
  •  Facebook
  •  Twitter
  •  Instagram
  •  Pin
  •  linkedin
  •  reddit

Oracle Database 23c Features

 Atikh Shaikh     23c, oracle     No comments   

The hottest topic of the week is the release of Oracle database version 23c, developer release. This version 23c is ready for Database Administrators and developers to test new features and functionalities.

DBAs and Developers can plan future strategies based on new releases. This latest version of Oracle database 23c is the next long-term upgrade after 19c. There was not much fuss about 18c,20c,21c, and 22c. but now 23c is more in an autonomous database and the free version is already in the DBAs and Developers' system. 

oracle23c


Below few important features identified by different sources 

JSON relational duality

Build an application in either relational or JSON paradigm with a single source of truth and benefit from the strengths of both, Data is held once but can be accessed, written, and modified with either approach.

JavaScript stored Procedures 

JavaScript code now be executed closer to data by writing JavaScript stored procedures or loading existing JavaScript libraries into oracle dba

JSON Schema 

Ensure and validate JSON document structures via industries' standard JSON demons

Operational Property graphs

Build both transaction and analytical property graph applications using new SQL standard property graph queries support.

Oracle Kafka APIs

Kafka application runs against the Oracle database 

SQL Domain 

The new Domain can act as a lightweight type modified that centrally documents intended data usage extending and drastically improving SQL standard domains.

Annotation

database metadata can now be stored directly alongside the data.

Refer to another article to download and try your hands on Oracle 23c.

Read More
  • Share This:  
  •  Facebook
  •  Twitter
  •  Instagram
  •  Pin
  •  linkedin
  •  reddit

Processing select statement in oracle database

 Atikh Shaikh     architecture, oracle     No comments   

Understanding the execution of any query in the Oracle database needs an understanding of different components of the Oracle database such as SGA, Processes, datafiles, memory, etc.

Here, we will discuss the execution of a simple select query like “select * from

 hr.emp

select query processing in oracle database


First of all, a connection to the database is required, and to achieve that you need to know database connection details such as port number, hostname, sid, etc. Once you have these details, the user will log in to the database and a connection is established using the listener.

Now user can execute any query, the query sent by the user once reaches the database, gets picked up by the server process, and from here onwards, processing will be carried out by the server process.

The server process will create a hash for the SQL query sent by the user, the hash is just hexadecimal coding in the form of SQL id and SQL hash value.

Now server process searches in the shared pool and checks if any SQL area is present for the user query, if SQL area is already present for this query, if the result finds in SQL area, data will be returned to the user from memory itself, no further process required.

In case the SQL area is not present for this SQL query, that means the query is getting executed the first time in the database. The server process will start building SQL area for the query

Processing of the select query is taken in 3 main parts

  • Parsing
  • Execution
  • Fetching

Parsing

Parsing is also known as validation of the query sent by the user, it will be done in three steps

  • Syntax check
  • Semantics check
  • Privilege check

Syntax Check

            Here, the syntax of the SQL query will be verified as per SQL standards and keywords, sometimes we misspell some keywords such as “SELECT” is written as “SLECT” or “SELCT”, syntax check will throw an error, once the syntax check is passed, it moves to semantic check

Semantic check

            In semantic check, the server process verifies the structure of the query, i.e., columns names, tables, or schema names used will be verified with the help of a dictionary cache

Privilege check

In this stage of parsing, privileges on users will be verified, the client is executing the query using USER1 schema and accessing the table from USER2, so the server process will check if USER1  has access to USER2

Once all these checks are verified as part of the parsing, it will start the execution of the SQL query based on the SQL plan described by OPTIMIZER, OPTIMIZER is an internal utility in the database, that keeps all the statistics of the database objects

The server process will bring data in the buffer cache i.e., memory from datafiles, once data is loaded in the memory, a complete SQL area will be created and the server process will return data to the user, this completes the processing of the SQL query. so, processing the query involves a lot of components of the database

Read More
  • Share This:  
  •  Facebook
  •  Twitter
  •  Instagram
  •  Pin
  •  linkedin
  •  reddit
Newer Posts Older Posts Home

Author

Atikh Shaikh
View my complete profile

Categories

  • MongoDB (18)
  • Oracle 12c (30)
  • Oracle12cR2 New Feature (3)
  • PostgreSQL (20)
  • RMAN (10)
  • Snowflake (8)
  • mysql (23)
  • oracle (73)

Blog Archive

  • ►  2018 (38)
    • ►  November (25)
    • ►  December (13)
  • ►  2019 (33)
    • ►  January (15)
    • ►  February (6)
    • ►  March (2)
    • ►  April (5)
    • ►  May (5)
  • ►  2020 (5)
    • ►  April (1)
    • ►  May (2)
    • ►  July (2)
  • ►  2021 (8)
    • ►  June (3)
    • ►  July (3)
    • ►  August (1)
    • ►  December (1)
  • ►  2022 (33)
    • ►  May (3)
    • ►  June (10)
    • ►  July (3)
    • ►  August (4)
    • ►  September (8)
    • ►  October (3)
    • ►  November (2)
  • ▼  2023 (14)
    • ►  February (1)
    • ▼  April (5)
      • Processing select statement in oracle database
      • Oracle Database 23c Features
      • Install Oracle Virtual box(VM Box) on windows system
      • Oracle 23c : Download and Install on Windows 10
      • Want to Convert number to text/word form? Here is ...
    • ►  May (2)
    • ►  June (1)
    • ►  September (1)
    • ►  October (1)
    • ►  December (3)
  • ►  2024 (5)
    • ►  January (2)
    • ►  March (3)
  • ►  2025 (5)
    • ►  March (1)
    • ►  April (3)
    • ►  May (1)

Popular Posts

  • ORA-29283: invalid file operation: unexpected "LFI" error (1509)[29437]
    I was trying to export the schema in my windows PC, it got stuck with below error    C:\Users\shaik\Videos\technodba exp>expdp userid...
  • Oracle Dataguard Broker Configuration (DGMGRL)
    Data Guard Broker is a command-line interface that makes managing primary and standby databases easy. DBA can use a single command to switch...
  • ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
    In previous articles, we have learned about user creation and grants  in MySQL in detail, but there are a few privileges called global priv...
  • PostgreSQL : How to get data directory location for PostgreSQL instance
    Sometimes, you start working on a PostgreSQL instance but forget about the data directory, here we will discuss different methods to know th...
  • Oracle 23ai : The all new Hybrid Read-Only for pluggable databases (PDBs)
      The latest Oracle database version, Oracle 23ai, introduced a new open mode called Hybrid Read-Only for pluggable databases (PDBs). Local ...

Labels

oracle Oracle 12c mysql PostgreSQL MongoDB oracle 19c Oracle23c oracle19c Orale PDB-CDB oracle12c python AWS Oracle ASM Virtualbox pluggable database storage engine

Pages

  • Disclaimer
  • Privacy Policy

Follow TechnoDBA

Copyright © Atikh's DBA blog | Powered by Blogger