Introduction

OPTIMIZER_ADAPTIVE_FEATURES enables or disables all of the adaptive optimizer features, including adaptive plan (adaptive join methods and bitmap plans), automatic re-optimization, SQL plan directives, and adaptive distribution methods.

Issue

Error comes while running Release 12.2.5 Release Update Pack (RUP).

Issue Description

When we are done with all Oracle12c installation and just to go for EM express,you may face the following error in FireFox browser.

This error occur in case you have miss used values of memory_target and memory_max_target.

To overcome the same follow the following steps

Consider the following situation - a customer goes to a travel website to find out the different options for travelling to his vacation spot. For this, he/she creates a number of iteneraries out of which only one will be finalized at the end or all of them will be discarded. These iteneraries donot need to be stored in the database on a permanent basis. Storing them in a temporary table to be discarded/deleted at the end of session/transaction makes sense in such cases.

Simply put, temporary tables are used to store data temporarily during a transaction or session.

Why Indexes?

Basic use of index is to find data in a table more quickly by speeding up the query searches. Consider an employees table with 1 million records. If I query the table to extract data for 1 employee based on emp_id, all the records are compared for the emp_id. This approach will be very time taking in real time when many queries are searching for records. Thus we should use indexes to speed up the searching process.

CASE statement is used to select one out of many set of statements to execute.

Types of CASE statements:

How about this.. you can create a composite data structure in pl/sql which can hold an entire row's data. Records are made up of one or more fields of same or different datatypes, same as a row in a table.

Example:

I had some difficulty working with these 3 commands back when I was learning SQL particularly when deciding which one to use. SO, I've dedicated this blog to differentiating the three of them.

DELETE: used to remove rows selectively from a table. Delete is a DML command, so it will cause any delete triggers on the table to fire. You may need to explicitly commit after deleting from a table to make changes permanent.

So folks, I’m not going to explain the trigger syntax here as its same for all types of triggers. I’ll start off with INSTEAD OF trigger straight away.

INSTEAD OF triggers hold a special functionality as they can be used to update views which are normally not updateable and perform insert, update, delete, merge operations on them.

Introduction

Module overloading is a concept used across many programming languages mainly because it’s a very powerful feature which improves the usability of your software.

Another cool and powerful feature that gives us the independence of carrying out a specific task as a separate transaction while inside the main transaction. The main transaction merely suspends for the duration the autonomous transaction runs.

It has great applications and uses while writing pl/sql code.

What is the difference between dynamic sql and static sql?

Dynamic sql refers to statements that are constructed and executed at runtime. Whereas static sql statements are constructed when the pl/sql block containing the statement is compiled.

Functions are a great and fun way to implement any functionality in our code. Not only are they easy to code and include almost anywhere we need them, their reusability and the large acceptance of return datatypes make them a coder's superpower.

One of the benefits of user defined functions is that they can be included in sql queries just like any other oracle predefined functions.