Oracle Archives | {coding}Sight

Exporting Oracle Table to Excel Worksheet

July 20, 2018July 20, 2018 Nisarg Upadhyay Leave a comment

In this article, I will explain how we can divide and export data of an Oracle database table into multiple worksheets of an Excel file with help of the SQL Server integration service package.

Often, DBAs asked to generate Ad-Hoc reports from a database. Recently I have been asked to generate a report from the database. They want me to provide a report in multiple worksheets of an Excel file. There are different ways to do it but the query was resource-intensive. Hence I want to extract data from the database with one execution and decided to use the SQL Server integration services package. SQL Server data tools have one component called Conditional Split Transformation which can be used to achieve the task. Read More

Difference Between Inline and Out-of-Line Constraints

May 15, 2018May 15, 2018 Ben Brumm Leave a comment

Constraints on tables and columns allow you to enforce the data quality. In SQL, there are two ways of creating constraints on a table: inline and out of line.

In this article, I am going to explore these constraints and the advantages they have, as well as explain which one I recommend and why.

Oracle Database Security: Database Auditing

April 10, 2018April 11, 2018 Andreea-Elena Panait Leave a comment

In this article, I will continue with Oracle Database Security and I will present some important facts about standard database auditing, audit triggers, and audit policies in Oracle. Database auditing has two components: monitoring and persistent registration of established database activity sets and events. The purposes of database auditing are non-repudiation, investigation of suspicious activities, detection of problems generated by configurations regarding authorization (resources access), compliance with actual legislation and control. Read More

Database Security in Oracle

March 27, 2018March 27, 2018 Mikhail Flenov Leave a comment

There is no secret that information makes the world go around currently. If an enterprise takes care of its intellectual property and each employee can easily get the necessary information, the enterprise can hope for the growth. If there is chaos in data, the enterprise will fail despite the team spirit.

In this article, we are going to explore the database security basics and examples of information protection in Oracle. Actually, the theoretical basics for protecting information in the database, which we are going to consider in this article, will be also useful to people working with other databases.

Oracle Database Security – Encryption and Decryption

March 6, 2018April 11, 2018 Andreea-Elena Panait Leave a comment

Data encryption is very important because it’s a way to protect the current and the archived data in order to ensure its confidentiality. This prevents an unauthorized access and usage. In this article, I will briefly present some important aspects of data encryption and decryption.

Example of Improving Query Performance with Indexes

January 9, 2018January 18, 2018 Ben Brumm Leave a comment

In this article, we’ll look at how an index can improve the query performance.

Introduction

Indexes in Oracle and other databases are objects that store references to data in other tables. They are used to improve the query performance, most often the SELECT statement.

They aren’t a “silver bullet” – they don’t always solve performance problems with SELECT statements. However, they can certainly help.

Let’s consider this on a particular example.

50 Shades of Oracle Database Certification Exam

January 2, 2018January 18, 2018 Hasan Alizada Leave a comment

In this article, I would like to talk about one of the basic certifications from Oracle – Oracle Database SQL Certified Expert. Unfortunately, this certification has become unavailable recently, but still, this article may be useful for preparing for other certifications and exams from Oracle. I wish a good read to everyone who wants to know which questions and tricks may await them and wants to be ahead of the game. Read More

5 Ways to Update Data with a Subquery in Oracle SQL

December 6, 2017December 6, 2017 Ben Brumm Leave a comment

A subquery is a powerful way to find the data you want to use for another query. They are often used in SELECT and UPDATE statements to make these queries more efficient and easier to maintain.

There are several different ways to use subqueries in UPDATE statements. Let’s take a look at each of them.

Comparison of Tree Graphs

October 6, 2017October 6, 2017 Dmitriy Vlasov Leave a comment

When developing an information system that also includes various processing of design and technological documentation, I faced the following problem.

We have a certain product structure. During the day, different parts of this product are changed and by the evening, it is already unclear what has been changed. Sometimes, products can consist of more than 10 000 elements. The elements are not unique, and the reality is that the structure can be often modified, although the product is almost ready. The failure to understand the scope of changes complicates planning.

The product structure can be represented as a tree graph in PL/SQL. Since I could not find a suitable way to compare two graphs, I decided to create my own method.

Oracle as Workaround of Mutating Tables

October 2, 2017October 4, 2017 Sergey Averin Leave a comment

The Oracle mutating trigger error occurs when a trigger references the table that owns the trigger, resulting in the “ORA-04091: table name is mutating, trigger/function may not see it” message.

Let’s have a look at the existing workarounds.

The first one, through the package, is ancient and seems to be effective, however, it takes much time to prepare and run it. The second one is simple and performed using compound triggers.