Oracle SQL :LAG function

The LAG function provides access to a row at a given offset prior to the current position, and the LEAD function provides access to a row...

Oracle SQL LEAD function

The Oracle/PLSQL LEAD function is an analytic function that lets you query more than one row in a table at a time without having to join t...

Oracle SQL :CUBE function

In addition to the subtotals generated by the  ROLLUP  extension, the  CUBE  extension will generate subtotals for all combinations of the ...

Oracle SQL :UTL_FILE example

SET SERVEROUTPUT ON DECLARE   fHandle UTL_FILE.FILE_TYPE;   vTextIn varchar2(25);   vTextOut varchar2(25); BEGIN   -- Might get INVALID_PAT...

Oracle Plsql Tips

1)Requirement You need a small lookup table that will be generated in memory every time a PL/SQL procedure is invoked or a package that ...

Oracle EBS : Math Indexes

1)SELECT numeric_number FROM table_math WHERE numeric_number + 0 = ? Nevertheless we can index these expressions with a function-based...

Oracle SQL Function based Index

Suppose you want to retrieve the  records SELECT first_name, last_name, phone_number   FROM emp  WHERE UPPER(last_name) = UPPER(...

Oracle SQL :How to decide to create index on column

How to decide to create index on column? Suppose a table has 30 columns. Sometimes my query is running fast ,sometimes may not? ...

Oracle PLSQL : Index by Integer

The first type of collection is known as index-by tables. These behave in the same way as arrays except that have no upper bounds, allowing...

Oracle PLSQL Bulk bind

DECLARE   TYPE Numlist IS VARRAY (100) OF NUMBER;   Id NUMLIST := NUMLIST(7902, 7698, 7839); BEGIN -- Efficient method, using a bulk b...

Oracle / PLSQL: CAST Function

What is the user of CAST function ? The Oracle/PLSQL CAST function converts one datatype to another. select CAST( '25-Mar-2019...

Oracle SQL :Interview questions Part-2

1Oracle database automatically creates an index for the primary key of a table and for columns included in a unique constraint. Options -...

Oracle SQL :Interview questions

1) Which of the following is a valid reason for deciding to create a subprogram as a function rather than as a procedure?  A. It must ...

Oracle SQL :PLSQL :utL_file debugging

When you run UTL_FILE, you might encounter these errors  – possible causes and fixes are given below. 1. PLS-00201: identifier ‘UTL_FILE...

Oracle SQL:PLSQL :UTL_FILE

What is The Maximum Number of Files That Can Be Opened Using UTL_FILE.FOPEN() ? CAUSE The maximum number of concurrent files that can be ...

Oracle SQL :PLSQL :Subtype

·          Subtypes specify the same set of operations as their base type, but only a subset of its values. ·          A subtype doe...

Oracle SQL :Difference Between CHAR and VARCHAR

Differences Between CHAR and VARCHAR2 Data Types Predefined Subtypes How Blank-Padding Works Value Comparisons : ...

Oracle PLSQL : Data types: Boolean

BOOLEAN Data Type The PL/SQL data type BOOLEAN stores logical values, which are the Boolean values      TRUE and FALSE and the valu...

Oracle SQL:ORA-01555: snapshot too old

Problem: There is a procedure to insert records in few tables and to update in few tables , after processing  X records or so , it is...

Oracle SQL :Truncate: Delete

Oracle SQL : Truncate: Delete What’s the Difference Between TRUNCATE and DELETE? DELETE can delete specific records, TRUNCATE deletes ...