Category Archives: Oracle

All notes on Oracle..

Converting BYTES to CHAR on Oracle database

In this blog, I will be sharing the issues that we faced while converting the data type and NLS semantic to CHAR from BYTES. This was one of the business use cases where we needed to convert the entire columns throughout the schema from BYTES to CHAR types only for VARCHAR2 columns datatypes. Sometimes we […]
Read More »

Checking size of the tablespaces in Oracle for one schema

Most of the time we try to verify what objects are mapped to what tablespace. But most of the time we try to verify the size of the current schema. Imagine there could be a situation where you have a schema and all of its objects are been scattered around multiple tablespaces. While might not […]
Read More »

Using FIRST_VALUE & LAST_VALUE in Oracle Analytic Functions

In this blog, we will see how to use FIRST_VALUE & LAST_VALUE in Oracle with simple examples. FIRST_VALUE and LAST_VALUE in Oracle These are Oracle analytical functions used to return the first value or the last value from a set of ordered rows. These functions can get the first value or the last value within a […]
Read More »

Creating a JAVA Stored procedure on Oracle

In this blog, we will see how we can create a simple JAVA class on Oracle and calling the class using a simple function. Without taking more time, let us create one user on the Oracle database and then define a JAVA class and associate it with one Oracle function. Create one user to test […]
Read More »

OLE DB connection error while connecting Oracle database using SSIS

In this blog, we will see how we can fix the OLE DB connection error when connecting to an Oracle database using SQL Server Integration Services. SQL Server Integration Service (SSIS) is a Microsoft product which is mainly used for automating importing and exporting data or Extract, transform and load i.e. ETL.  Here is the […]
Read More »

Introduction to Rank Function in Oracle

In this blog, we will see how to use the RANK analytical function in an Oracle database and how it helps us in an analysis. What is the RANK function? The RANK function is an Oracle analytical function which calculates the rank of values which are retrieved, or against a set of rows. The same […]
Read More »

Partition By Clause in Oracle

An introductory discussion about how to use Partition by Clause in Oracle. What is a “partition by” clause in Oracle? It is used to break the data into small partitions and is been separated by a boundary or in simple dividing the input into logical groups. The analytical functions are performed within this partitions.  So […]
Read More »

Installing Oracle 18c on Windows Operating system

In this article, we will see how to install Oracle 18c on the Windows operating system.  To install 18c first we need to download the setup from Oracle.com. In my case, I have already downloaded and extracted locally on my windows machine. The below file will show the step by step guide on how to […]
Read More »

Analytic Functions in Oracle

An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows. Analytic functions are used to calculate or compute aggregate values based on a group of rows, it is also called as a Window. This determines the range of rows used to perform the […]
Read More »

Using full redaction for masking the column values in 12c

This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle’s Advanced Security.   Why would we need to mask a column’s data? One of the most common uses is […]
Read More »