Generate Sequence and Random Numbers Using SQL Query

Generate Sequence of Numbers from 1 to N using SQL Query

SELECT LEVEL AS ID 
FROM DUAL 
CONNECT BY LEVEL <= 20;

After certain numbers generation, you will run out of your memory saying that ORA-30009: Not enough memory for CONNECT BY operation. Use with the clause and create the cartesian product as shown below.

WITH more_data AS (
  SELECT /*+ MATERIALIZE */ level AS id
  FROM   dual
  CONNECT BY level <= 100
)
SELECT rownum AS id
FROM   more_data, more_data, more_data
WHERE  rownum <= 1000000;

Generate a random number using SQL Query

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as HR@ORCLL

SQL> SELECT dbms_random.random FROM dual;
    RANDOM
----------
1624593079

SQL> 

When Compiler Catches Overloading Errors

When Compiler Catches Overloading Errors

I have a procedure INITIALIZE which accepts CHAR as IN parameter , another procedure INITIALIZE accepts VARCHAR2 as IN parameter, Now I'm calling INITIALIZE procedure with IN parameter value as 'AB'. Which procedure will execute?

In fact , the compiler throws PLS-00307: too many declarations of 'INITIALIZE' match this call. See the below demonstration .

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as HR@ORCL
SQL> 
SQL> DECLARE
  2    PROCEDURE initialize ( in_val CHAR) IS
  3    BEGIN
  4    dbms_output.put_line('CHAR data type '||in_val);
  5    END initialize;
  6  
  7    PROCEDURE initialize ( in_val VARCHAR2) IS
  8    BEGIN
  9    dbms_output.put_line('VARCHAR2 data type '||in_val);
 10    END initialize;
 11  
 12  BEGIN
 13    initialize('AB');
 14  END;
 15  
 16  /
DECLARE
  PROCEDURE initialize ( in_val CHAR) IS
  BEGIN
  dbms_output.put_line('CHAR data type '||in_val);
  END initialize;

  PROCEDURE initialize ( in_val VARCHAR2) IS
  BEGIN
  dbms_output.put_line('VARCHAR2 data type '||in_val);
  END initialize;

BEGIN
  initialize('AB');
END;
ORA-06550: line 13, column 3:
PLS-00307: too many declarations of 'INITIALIZE' match this call
ORA-06550: line 13, column 3:
PL/SQL: Statement ignored

The above procedure program throws error PLS-00307, because :

"The PL/SQL compiler catches overloading errors as soon as it can determine that it will be unable to tell which subprogram was invoked. When subprograms have identical headings, the compiler catches the overloading error when you try to compile the subprograms themselves (if they are local) or when you try to compile the package specification that declares them (if they are packaged); otherwise, it catches the error when you try to compile an ambiguous invocation of a subprogram."

Let's correct the above program by changing the synonymous procedures to different procedures, i m changing the CHAR datatype to NUMBER in the first procedure. 

The anonymous block compiles successfully and executes.

SQL> DECLARE
  2    PROCEDURE initialize ( in_val NUMBER) IS
  3    BEGIN
  4    dbms_output.put_line('NUMBER data type '||in_val);
  5    END initialize;
  6  
  7    PROCEDURE initialize ( in_val VARCHAR2) IS
  8    BEGIN
  9    dbms_output.put_line('VARCHAR2 data type '||in_val);
 10    END initialize;
 11  
 12  BEGIN
 13    initialize('AB');
 14  END;
 15  /
VARCHAR2 data type AB
PL/SQL procedure successfully completed

Oracle Data Dictionary

Data Dictionary tables/Views contain information about the database and its objects. These are automatically created and maintained by oracle server.

You use SQL statements to access the data dictionary. Because the data dictionary is read-only,you can issue only queries against its tables and views.You can query the dictionary views that are based on the dictionary tables to find information such as:

Definitions of all schema objects in the database (tables, views, indexes, synonyms, sequences, procedures, functions, packages, triggers, and so on)

Default values for columns

Integrity constraint information

Names of Oracle users

Privileges and roles that each user has been granted

Other general database information

Oracle Data Dictionary has two parts :
1 Base Tables
2 User Accessible Views

Base tables can only read and written by Oracle Server. Views summarize and display the information stored in the base tables of data dictionary.
The Oracle user SYS owns all base tables and user-accessible views of the data dictionary. No Oracle user should ever alter (UPDATE, DELETE, or INSERT) any rows or schema objects contained in the SYS schema, because such activity can compromise data integrity.

View are categorized into 4 types.
USER_: These views contain data from your schema or what you own
ALL_: These views contain data from your schema and what all you can access from other schemas
DBA_: These views contains data from the whole database
V$ : Dynamic performance views

DICTIONARY

SQL> DESC DICTIONARY;

Name       Type           Nullable Default Comments                  
---------- -------------- -------- ------- --------------------------
TABLE_NAME VARCHAR2(30)   Y                Name of the object        
COMMENTS   VARCHAR2(4000) Y                Text comment on the object

SQL> SELECT *
FROM dictionary
WHERE table_name = 'USER_OBJECTS';

USER_OBJECTS and ALL_OBJECTS

USER_OBJECTS:
Query USER_OBJECTS to see all the objects that you own.

Using USER_OBJECTS, you can obtain a listing of all object names and types in your schema, plus the following information:
-Date created
-Date of last modification
-Status (valid or invalid)

ALL_OBJECTS:

Query ALL_OBJECTS to see all the objects to which you have access.

SQL> SELECT object_name, object_type, created, status
  2  FROM user_objects
  3  ORDER BY object_type;

OBJECT_NAME         OBJECT_TYPE         CREATED     STATUS
------------------- ------------------- ----------- -------
CHANNELS_DIM        DIMENSION           7/26/2016 1 VALID
TIMES_DIM           DIMENSION           7/26/2016 1 VALID
PRODUCTS_DIM        DIMENSION           7/26/2016 1 VALID
CUSTOMERS_DIM       DIMENSION           7/26/2016 1 VALID
PROMOTIONS_DIM      DIMENSION           7/26/2016 1 VALID
CUSTOMERS_PK        INDEX               7/26/2016 1 VALID
COUNTRIES_PK        INDEX               7/26/2016 1 VALID
SALES_PROD_BIX      INDEX               7/26/2016 1 VALID
SALES_CUST_BIX      INDEX               7/26/2016 1 VALID

USER_TABLES

SQL> DESC user_tables

Name                      Type         Nullable Default Comments                                                                                       
------------------------- ------------ -------- ------- ------------------------------------------------------------------------------------------------
TABLE_NAME                VARCHAR2(30)                  Name of the table                                                                             
TABLESPACE_NAME           VARCHAR2(30) Y                Name of the tablespace containing the table                                                   
CLUSTER_NAME              VARCHAR2(30) Y                Name of the cluster, if any, to which the table belongs                                       
IOT_NAME                  VARCHAR2(30) Y                Name of the index-only table, if any, to which the overflow or mapping table entry belongs     
STATUS                    VARCHAR2(8)  Y                Status of the table will be UNUSABLE if a previous DROP TABLE operation failed,VALID otherwise

SQL> SELECT table_name
  2  FROM user_tables;

TABLE_NAME
------------------------------
EMPLOYEES_3
ERR$_BIGDATA
BIGDATA_MOVE
TAB_VIRTUAL_COL
SALES
EMPLOYEE_PHONE
USER_TAB_COLUMNS


SQL> DESCRIBE user_tab_columns

Name                 Type          Nullable Default Comments                                                          
-------------------- ------------- -------- ------- --------------------------------------------------------------------
TABLE_NAME           VARCHAR2(30)                   Table, view or cluster name                                       
COLUMN_NAME          VARCHAR2(30)                   Column name                                                       
DATA_TYPE            VARCHAR2(106) Y                Datatype of the column                                            
DATA_TYPE_MOD        VARCHAR2(3)   Y                Datatype modifier of the column                                  
DATA_TYPE_OWNER      VARCHAR2(30)  Y                Owner of the datatype of the column                              
DATA_LENGTH          NUMBER                         Length of the column in bytes                                     
DATA_PRECISION       NUMBER        Y                Length: decimal digits (NUMBER) or binary digits (FLOAT)          
DATA_SCALE           NUMBER        Y                Digits to right of decimal point in a number                      
NULLABLE             VARCHAR2(1)   Y                Does column allow NULL values?                                     
COLUMN_ID            NUMBER        Y                Sequence number of the column as created                          
DEFAULT_LENGTH       NUMBER        Y                Length of default value for the column                            
DATA_DEFAULT         LONG          Y                Default value for the column                                      
NUM_DISTINCT         NUMBER        Y                The number of distinct values in the column                      
LOW_VALUE            RAW(32)       Y                The low value in the column                                       
HIGH_VALUE           RAW(32)       Y                The high value in the column                                       
SQL>

SQL> SELECT column_name, data_type, data_length,
  2  data_precision, data_scale, nullable
  3  FROM user_tab_columns
  4  WHERE table_name = 'EMPLOYEES';

COLUMN_NAME                    DATA_TYPE         DATA_LENGTH DATA_PRECISION DATA_SCALE NULLABLE
------------------------------ ----------------- ----------- -------------- ---------- --------
EMPLOYEE_ID                    NUMBER                     22              6          0 N
FIRST_NAME                     VARCHAR2                   20                           Y
LAST_NAME                      VARCHAR2                   25                           N
EMAIL                          VARCHAR2                   25                           N
PHONE_NUMBER                   VARCHAR2                   20                           Y
HIRE_DATE                      DATE                        7                           N
JOB_ID                         VARCHAR2                   10                           N
SALARY                         NUMBER                     22              8          2 Y
COMMISSION_PCT                 NUMBER                     22              2          2 Y
MANAGER_ID                     NUMBER                     22              6          0 Y
DEPARTMENT_ID                  NUMBER                     22              4          0 Y
11 rows selected
SQL>

USER_CONSTRAINTS and USER_CONS_COLUMNS

USER_CONSTRAINTS describes the constraint definitions on your tables.

USER_CONS_COLUMNS describes columns that are owned by you and that are specified in constraints.

SQL> DESCRIBE user_constraints
Name              Type         Nullable Default Comments                                                                 
----------------- ------------ -------- ------- ---------------------------------------------------------------------------
OWNER             VARCHAR2(30) Y                Owner of the table                                                       
CONSTRAINT_NAME   VARCHAR2(30)                  Name associated with constraint definition                               
CONSTRAINT_TYPE   VARCHAR2(1)  Y                Type of constraint definition                                             
TABLE_NAME        VARCHAR2(30)                  Name associated with table with constraint definition                     
SEARCH_CONDITION  LONG         Y                Text of search condition for table check                                 
R_OWNER           VARCHAR2(30) Y                Owner of table used in referential constraint                             
R_CONSTRAINT_NAME VARCHAR2(30) Y                Name of unique constraint definition for referenced table                 
DELETE_RULE       VARCHAR2(9)  Y                The delete rule for a referential constraint                             
STATUS            VARCHAR2(8)  Y                Enforcement status of constraint -  ENABLED or DISABLED                   
DEFERRABLE        VARCHAR2(14) Y                Is the constraint deferrable - DEFERRABLE or NOT DEFERRABLE               
DEFERRED          VARCHAR2(9)  Y                Is the constraint deferred by default -  DEFERRED or IMMEDIATE           
VALIDATED         VARCHAR2(13) Y                Was this constraint system validated? -  VALIDATED or NOT VALIDATED       
GENERATED         VARCHAR2(14) Y                Was the constraint name system generated? -  GENERATED NAME or USER NAME 
BAD               VARCHAR2(3)  Y                Creating this constraint should give ORA-02436.  Rewrite it before 2000 AD.
RELY              VARCHAR2(4)  Y                If set, this flag will be used in optimizer                               
LAST_CHANGE       DATE         Y                The date when this column was last enabled or disabled                   
INDEX_OWNER       VARCHAR2(30) Y                The owner of the index used by the constraint                             
INDEX_NAME        VARCHAR2(30) Y                The index used by the constraint                                         
INVALID           VARCHAR2(7)  Y                                                                                         
VIEW_RELATED      VARCHAR2(14) Y                                                                                         


SQL>

SQL>

SQL> SELECT constraint_name, constraint_type,
  2  search_condition, r_constraint_name,
  3  delete_rule, status
  4  FROM user_constraints
  5  WHERE table_name = 'EMPLOYEES';
CONSTRAINT_NAME                CONSTRAINT_TYPE SEARCH_CONDITION                                                                 R_CONSTRAINT_NAME              DELETE_RULE STATUS
------------------------------ --------------- -------------------------------------------------------------------------------- ------------------------------ ----------- --------
EMP_MANAGER_FK                 R                                                                                                EMP_EMP_ID_PK                  NO ACTION   ENABLED
EMP_JOB_FK                     R                                                                                                JOB_ID_PK                      NO ACTION   ENABLED
EMP_DEPT_FK                    R                                                                                                DEPT_ID_PK                     NO ACTION   ENABLED
EMP_LAST_NAME_NN               C               "LAST_NAME" IS NOT NULL                                                                                                     ENABLED
EMP_EMAIL_NN                   C               "EMAIL" IS NOT NULL                                                                                                         ENABLED
EMP_HIRE_DATE_NN               C               "HIRE_DATE" IS NOT NULL                                                                                                     ENABLED
EMP_JOB_NN                     C               "JOB_ID" IS NOT NULL                                                                                                        ENABLED
EMP_SALARY_MIN                 C               salary > 0                                                                                                                  ENABLED
EMP_EMAIL_UK                   U                                                                                                                                           ENABLED
EMP_EMP_ID_PK                  P                                                                                                                                           ENABLED
10 rows selected



SQL>


SQL> DESCRIBE user_cons_columns

Name            Type           Nullable Default Comments                                                                                       
--------------- -------------- -------- ------- ------------------------------------------------------------------------------------------------
OWNER           VARCHAR2(30)                    Owner of the constraint definition                                                             
CONSTRAINT_NAME VARCHAR2(30)                    Name associated with the constraint definition                                                 
TABLE_NAME      VARCHAR2(30)                    Name associated with table with constraint definition                                         
COLUMN_NAME     VARCHAR2(4000) Y                Name associated with column or attribute of object column specified in the constraint definition
POSITION        NUMBER         Y                Original position of column or attribute in definition                                         


SQL>

SQL>

SQL> SELECT constraint_name, column_name
  2  FROM user_cons_columns
  3  WHERE table_name = 'EMPLOYEES';

CONSTRAINT_NAME                COLUMN_NAME
------------------------------ --------------------------------------------------------------------------------
EMP_MANAGER_FK                 MANAGER_ID
EMP_JOB_FK                     JOB_ID
EMP_DEPT_FK                    DEPARTMENT_ID
EMP_LAST_NAME_NN               LAST_NAME
EMP_EMAIL_NN                   EMAIL
EMP_HIRE_DATE_NN               HIRE_DATE
EMP_JOB_NN                     JOB_ID
EMP_SALARY_MIN                 SALARY
EMP_EMAIL_UK                   EMAIL
EMP_EMP_ID_PK                  EMPLOYEE_ID
10 rows selected

SQL>

Other Useful views:

SELECT * FROM DICTIONARY;
SELECT * FROM User_Users;
SELECT * FROM all_users;
SELECT * FROM User_Sys_Privs;
SELECT * FROM User_Role_Privs;
SELECT * FROM user_catalog;
SELECT * FROM user_tablespaces;
SELECT * FROM User_Free_Space;
SELECT * FROM User_Ts_Quotas;
SELECT * FROM User_Errors
SELECT * FROM user_source
SELECT * FROM user_indexes
SELECT * FROM User_Procedures
SELECT * FROM User_Triggers
SELECT * FROM User_Indexes
SELECT * FROM User_Views
SELECT * FROM User_Synonyms
SELECT * FROM user_tab_partitions
SELECT * FROM user_part_indexes;
SELECT * FROM User_Part_Tables;
SELECT * FROM User_All_Tables;
SELECT * FROM User_Tab_Privs;
SELECT * FROM User_Tab_Privs_Made;
SELECT * FROM user_tab_privs_recd;
SELECT * FROM user_password_limits;
SELECT * FROM user_scheduler_jobs;
SELECT * FROM User_Jobs;
SELECT * FROM user_datapump_jobs;
SELECT * FROM User_Constraints;
SELECT * FROM user_dependencies;
SELECT * FROM User_Db_Links;
SELECT * FROM User_Extents;
SELECT * FROM User_Segments;
SELECT * FROM User_External_Tables;
SELECT * FROM User_Flashback_Archive;
SELECT * FROM user_identifiers;
SELECT * FROM User_Java_Classes;
SELECT * FROM user_mviews;
SELECT * FROM user_nested_tables;
SELECT * FROM user_recyclebin;
SELECT * FROM user_tab_statistics;
SELECT * FROM user_unused_col_tabs;
SELECT * FROM User_Updatable_Columns;
SELECT * FROM user_varrays;
SELECT * FROM user_xml_column_names;
SELECT * FROM user_xml_indexes;
SELECT * FROM all_Xml_Schemas;
SELECT * FROM User_Xml_Views;
SELECT * FROM v$instance;
SELECT * FROM v$version;
SELECT * FROM v$parameter;
SELECT * FROM v$spparameter;
SELECT * FROM v$session;
SELECT * FROM v$session_blockers;
SELECT * FROM v$session_longops;
SELECT * FROM v$session_wait;
SELECT * FROM v$session_event;
SELECT * FROM v$sql
SELECT * FROM v$sql_plan
SELECT * FROM v$sql_shared_memory;
SELECT * FROM v$sql_workarea;
SELECT * FROM v$lock;
SELECT * FROM v$library_cache_memory;
SELECT * FROM v$license;
SELECT * FROM v$listener_network;
SELECT * FROM v$sess_io;
SELECT * FROM v$memory_target_advice;
SELECT * FROM v$sga;
SELECT * FROM v$reserved_words;
SELECT * FROM v$flash_recovery_area_usage;
SELECT * FROM v$process
SELECT * FROM v$db_cache_advice;
SELECT * FROM v$datapump_session;
SELECT * FROM v$nls_parameters;
SELECT * FROM v$undostat;
SELECT * FROM v$database;

Invalid objects in Oracle Database

Invalid Objects in Oracle Database :

We always wonder why there are invalids in my schema/database. What has been changed/caused to increase invalids.The reason could be , there is a change in the dependent objects structure. There could be a change in the package which has many referencing/dependent objects.Or there are some errors in your   package/ procedure/ functions by which objects become invalid.

Generally, these invalids are most common in the development database, development would be done my many developers and they would be doing changes to may of the objects which cause invalids. Also in the database where there is a major upgrade or patch deployed, in production databases where there is release happened.

How do I deal with these? Here is what i generally do , when i find invalids.

Find number of the invalid objects:

SELECT COUNT(*) FROM All_Objects WHERE STATUS = 'INVALID';

Find what all objects are invalid :

SELECT * FROM All_Objects WHERE STATUS='INVALID';

To check what object got changed recently:

SELECT * FROM All_Objects ORDER BY last_ddl_time DESC;

with above the query you will get the recently changed objects.
To check the dependent objects of an object:

SELECT * FROM All_Dependencies WHERE NAME ='MOVE_DATA';

To check for the objects with errors:

SELECT * FROM All_Errors;

ALTER ... COMPILE
when you now the objects with errors, the most important thing is resolve them. The dependent object will get compiled only when the referencing object is in valid status.

How to compile an individual/single object manually:

ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;

DBMS_UTILITY
I have a huge number of invalid objects in my schema, How do I compile ?

EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => 'HR',compile_all => FALSE);

compile_all parameter :when false, compiles only invalid objects in a schema; when true, compiles the entire schema.

UTL_RECOMP
An alternative way of compiling objects in a schema level, database level.

Schema Level

EXEC UTL_RECOMP.recomp_serial('HR');
EXEC UTL_RECOMP.recomp_parallel(8, 'HR');

Database Level

EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(8);

RECOMP_SERIAL : Compile one by one object.
RECOMP_PARALLEL :Recompile all objects using  parallel threads.

Operational Notes as defined in documentation : https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/u_recomp.htm

This package uses the job queue for parallel recompilation.
This package must be run using SQL*PLUS.
You must be connected AS SYSDBA to run this script.
This package expects the following packages to have been created with VALID status:
STANDARD (standard.sql)
DBMS_STANDARD (dbmsstdx.sql)
DBMS_JOB (dbmsjob.sql)
DBMS_RANDOM (dbmsrand.sql)
There should be no other DDL on the database while running entries in this package. Not following this recommendation may lead to deadlocks.

Oracle PLSQL - Difference Between Parameter Modes

Parameter Modes  in PLSQL procedures and functions are IN , OUT, IN OUT. The differences are as follows.

Oracle PLSQL - Difference between procedure and function

Oracle - How Oracle SQL Statement is processed

Stages of Processing SQL Statement:

SQL statements pass through several stages during their processing:

Parsing
Binding
Executing

Oracle uses cursors, private SQL areas, to store parsed statements and other information relating to the statements it’s currently processing. Oracle automatically opens a cursor for all SQL statements.

Parsing

During the parsing stage, Oracle does several things to check your SQL statements:
• Oracle checks that your statements are syntactically correct.
The server consults the data dictionary to check whether the tables and column specifications are correct.
• Oracle ensures that you have the privileges to perform the actions you are attempting through your SQL statements.
• Oracle prepares  the execution plan for the statement, which involves selecting the best access methods for the objects in the statement.

After it checks the privileges, Oracle assigns a number called the SQL hash value to the SQL statement for identification purposes. If the SQL hash value already exists in memory, Oracle will look for an existing execution plan for the statement, which details the ideal way it should access the various database objects, among other things. 

Soft Parse:
If the execution plan exists, Oracle will proceed straight to the actual execution of the statement using that execution plan.This is called a soft parse, and it is the preferred technique for statement processing. Because it uses previously formed execution plans, soft parsing is fast and efficient.

Hard Parse:
The opposite of a soft parse is a hard parse, and Oracle has to perform this type of parse when it doesn’t find the SQL hash value in memory for the statement it wants to execute. Hard parses are tough on system memory and other resources. Oracle has to create a fresh execution plan, which means that it has to evaluate the numerous possibilities and choose the best plan from among them. During this process, Oracle needs to access the library cache and dictionary cache numerous times to check the data dictionary, and each time it accesses these commonly used areas, Oracle needs to use latches, which are low-level serialization control mechanisms, to protect shared data structures in the SGA. Thus, hard parsing contributes to an increase in latch contention.

Any time there’s a severe contention for resources during statement processing, the execution time will increase. Remember that too many hard parses will lead to a fragmentation of the shared pool, making the contention worse. 

After the parsing operation is complete, Oracle allots a shared SQL area for the statement. Other users can access this parsed version as long as it is retained in memory.

Binding

During the binding stage, Oracle retrieves the values for the variables used in the parsing stage. Note that the variables are expanded to literal values only after the parsing stage is over.

Execution

Once Oracle completes the parsing and binding, it executes the statement. Note that Oracle will first check whether there is a parsed representation of the statement in memory already. If there is, the user can execute this parsed representation directly, without going through the parsing process all over again.

It’s during the execution phase that the database reads the data from the disk into the memory buffers (if it doesn’t find the data there already). The database also takes out all the necessary locks and ensures that it logs any changes made during the SQL execution.

After the execution of the SQL statement, Oracle automatically closes the cursors.

Oracle DML Error Logging

Oracle DML Error Logging

Key Points of DML Error Logging
• DML SQL Query fails even if 1 row does not meet criteria or violates a constraint
• Instead of the 100,000 row update/insert/whatever failing because a single row doesn’t quite work out, we can have the 99,999 successful rows go through and have the one bad row logged to a table
• It is intuitive and errors are logged into error log table
• Feature available with Oracle database 10g release 2

-When you need to load millions of rows of data into a table, we use INSERT , UPDATE, MERGE .Similarly , delete for deleting bulk amount of rows.
-So when loading millions of rows into a table, a single integrity or constraint violation leads to failure of the statement and rollback . 
-No matter you loaded only one or 90000 out of 100000 rows , statement fails and rollback will be performed. In these situations, you need to have data either error free or efficient way to handle errors.
-You can take advantage using SQL* Loader if your data is in a file.
-However, if you have data in table or other objects ,you can use an anonymous block to handle errors , and you can use bulk collect , forall features for performance.
-A direct path INSERT DML is much faster than the above approach.

You need to create  error logging table for this DML error logging.

Syntax for creating error logging table.

DBMS_ERRLOG.CREATE_ERROR_LOG (
        dml_table_name                  IN VARCHAR2,
        err_log_table_name              IN VARCHAR2 := NULL,
        err_log_table_owner             IN VARCHAR2 := NULL,
        err_log_table_space             IN VARCHAR2 := NULL,
        skip_unsupported                IN BOOLEAN  := FALSE);

-All the parameters except DML_TABLE_NAME are optional.
-If the optional details are omitted, the name of the error logging table will be ERR$_ together with the first 25 characters of the DML_TABLE_NAME.
-The SKIP_UNSUPPORTED parameter, if set to TRUE, instructs the error logging clause to skip over LONG, LOB, and object type columns that are not supported and omit them from the error logging table.

LOG ERRORS SYNTAX:
With the error logging table created, you can add the error logging clause to most DML statements,  

LOG ERRORS [INTO [schema.]table] 
[ (simple_expression) ] 
[ REJECT LIMIT  {integer|UNLIMITED} ]


-The INTO clause is optional;
-If you omit it, the error logging clause will put errors into a table with the same name format used by the CREATE_ERROR_LOG procedure.
-SIMPLE_EXPRESSION is any expression that would evaluate to a character string and is used for tagging rows in the error table to indicate the process that caused the error, the time of the data load, and so on.
-REJECT LIMIT can be set to any integer or UNLIMITED and specifies the number of errors that can occur before the statement fails.
-This value is optional, but if it is omitted, the default value is 0, which effectively disables the error logging feature.


The following types of errors are handled by the error logging clause: 
Column values that are too large
Constraint violations (NOT NULL, unique, referential, and check constraints), except in certain circumstances detailed below
Errors raised during trigger execution
Errors resulting from type conversion between a column in a subquery and the corresponding column of the table
Partition mapping errors


The following conditions cause the statement to fail and roll back without invoking the error logging capability: 
Violated deferred constraints
Out-of-space errors
Any direct-path INSERT operation (INSERT or MERGE) that raises a unique constraint or index violation
Any UPDATE operation (UPDATE or MERGE) that raises a unique constraint or index violation



we are using scott schema to demonstrate 

Create a regular table
SQL> create table emp_test as select * from scott.emp where 1=0;

Add Primary Key
SQL> alter table emp_test add constraint emp_pk primary key(empno);

Add Constraint
SQL> alter table emp_test add constraint emp_check_sal check(sal > 900);

Create error log table
SQL> exec dbms_errlog.create_error_log( ‘emp_test' );


SQL> desc err$_emp_test
Name Null? Type
----------------------- -------- ----------------
ORA_ERR_NUMBER$ NUMBER
ORA_ERR_MESG$ VARCHAR2(2000)
ORA_ERR_ROWID$ ROWID
ORA_ERR_OPTYP$ VARCHAR2(2)
ORA_ERR_TAG$ VARCHAR2(2000)
EMPNO VARCHAR2(4000)
ENAME VARCHAR2(4000)
JOB     VARCHAR2(4000)
MGR VARCHAR2(4000)
HIREDATE VARCHAR2(4000)
SAL        VARCHAR2(4000)
COMM VARCHAR2(4000)
DEPTNO VARCHAR2(4000)

SQL> insert into emp_test select * from emp; --Note DML fails because we have added check constraint
SQL> select * from emp_test; --Note no rows displayed
SQL> insert into emp_test select * from emp
LOG ERRORS REJECT LIMIT UNLIMITED ; --Note DML succeeds
SQL> select * from emp_test; --Note rows displayed
SQL> select ora_err_number$, ora_err_tag$,
ora_err_optyp$,empno,ora_err_mesg$ from err$_emp_test;
SQL> update emp_test set sal = sal - 2000 where sal between 2000 and 3000
LOG ERRORS ('My Update') REJECT LIMIT UNLIMITED;

Oracle Temporary Tables

About Temporary Tables :

Temporary tables are database tables that hold session specific data
Each session can only see and modify its own data
Types :– Transaction Specific – Session Specific

Transaction Specific
– Data exists for the duration of the Transaction
– Declared by syntax “On Commit Delete Rows”
Session Specific
– Data exists for the duration of the Session
– Declared by syntax “On Commit Preserve Rows”

Key Points :

• TRUNCATE statement issued against a temporary table, will truncate only the
session specific data. There is no effect on the data of other sessions.
• Data in temporary tables is automatically deleted at the end of the database
session, even if it ends abnormally.
• Indexes can be created on temporary tables. The content of the index and the
scope of the index is that same as the database session.
• Views can be created against temporary tables and combinations of temporary and
permanent tables.
• Tempoarary tables can have triggers associated with them.
• Export and Import utilities can be used to transfer the table definitions, but no data
rows are processed
• Foreign keys cannot be specified
• Cannot contain columns of nested table or varray
• Cannot be partitioned, index- organized or clustered

Example :

create global temporary table emp_temp
(empno number primary key,
deptno number,
ename varchar2(10))
on commit delete rows;

insert into emp_temp
select empno,deptno,ename from emp;
Commit;


Notice all the rows are lost after commit.

Oracle SQL WITH Query

WITH Query:

In complex queries that process the same subquery multiple times, the WITH clause lets you factor out the subquery, give it a name, then reference that name multiple times within
the original complex query.

This query joins two tables and computes the aggregate SUM(SAL) more than once.
The text in blue represents the parts of the query that are repeated. 

SELECT dname, SUM(sal) AS dept_total
FROM emp, dept
WHERE emp.deptno = dept.deptno
GROUP BY dname HAVING SUM(sal) >( SELECT SUM(sal) * 1/3 FROM emp, dept
WHERE emp.deptno = dept.deptno ) ORDER BY SUM(sal) DESC;

Query optimized with WITH syntax. 
The aggregation and fetch from the database tables is done only once

WITH summary AS
( SELECT dname, SUM(sal) AS dept_total FROM emp, dept WHERE emp.deptno = dept.deptno GROUP BY dname )
SELECT dname, dept_total
FROM summary
WHERE dept_total > ( SELECT SUM(dept_total) * 1/3 FROM summary )
ORDER BY dept_total DESC;

Dell Oracle SQL PL/SQL Interview Questions

Select * from a where hiredate+60 < sysdate; (here hiredate is non-unique index, so query scanning index range or full table scanning.
Have you worked on explain plan, how to tune sql query
Can you write pivot query?                                                                                 
Which exception rise in bulk collect and forall processing
What are analytical functions?             
What is dbms_profiler do?
Difference between sql *loader and external table, explain requirements for that
Have you ever worked on partitions, explain Virtual column based partitioning
what is nvl and nvl2 function and examples
Explain about TK-proof , syntax of TK-proof 
Merge statement syntax
Partition Exchange(One table have 5 partitions like P1...P5, I want to move P5 partition insert into another Table)? 
Difference between 10g and 11g
COLN
---------
A
A
A
B
B
B
B

Write  a query  output like as
coln
------
3
4
coln
-----
A
B
C
D
E
I want output like this
TEXT
--------
A,B,C,D,E
EMPNO     ENAME  
-----------    -----------
12345         REDDY
I want to count of empno and ename
coln
------
-1
-2
-3
-10
1
8
7
I want to count of positive and negative
select sum(decode(sign(value),1,value)) pos, sum(decode(sign(value),-1,value)) neg from pn
select ( select sum(value) from pn where 0<value) as positive, (select sum(value) from pn where 0>value) as negative from dual
select unique ( select sum(value) from pn where 0<value) as positive, (select sum(value) from pn where 0>value) as negative from pn

CTS Oracle SQL PL/SQL Interview Questions

Tell me something yourself?
What are the activities are you doing daily in your   project?
Tell me ratios of sql , pl/sql&unix?
Difference between primary key & unique key?
Diff b/w primary key & unique key + not null?
What is the use of views?
If table dropped, then what happen view?
What is index? types of indexes?
Which functions are not supported to index?
What is save point?
What is ora-1555?
What are the data types available in pl/sql, not in sql?
What is the main use of %type,%rowtype?
What is cursor? Types of cursors?
Diff b/w explict cursor & for loop cursor?
What is exception? Types of exceptions?
What is raise_application_error?
What is pragma_exception_init?
Diff b/w triggers&procedures?
Can you call procedure in select statement?
Restrictions in functions?
What is the use of packages?
How to drop package body only?
Can use pragma_autonamous_tranctions in packages?
What is bulk collect?
What is nullif()?
What is mutating error?
What are the module's , you are working
Which will fire default first statement level or row level   trigger?
What is bulk collect? And any restrictions in bulk collect?
What is the use of limit clause in bulk collect?
How to debug your code?
How to trace error handling?
How to find which line error was raised?
What are the methods there in save exceptions?
What is functional based index? Write syntax?
How to update complex view?
Can you alter procedure with in package?
Is it possible to open cursor which is in package in    another procedure?
What is substr()&instr()?
Difference between case and decode?
Can you use sysdate in check constraints? If no, why?
Difference between column level constraints & table level    constraints?
What is optimizer?
What is nested_loop join?
What is pivot operater?
Tell me some oracle 10g features?
What is m_view?
What is master & detail tables?
What is sqlcode and sqlerrm?
Diff b/w procedure & packages?
What are the dis_adv of packages?

CSS Oracle SQL PL/SQL Interview Questions

Tell me about yourself
What is index?
How does index form internally?
What are pseudo columns?
What are exceptions? How to handle exceptions in PL/SQL?
Explain about exception propagation?
What is autonomous transaction?
What is trigger? Have you ever used it?
Define view
Can we update a view?(what are the conditions)?
if I have created view from two tables by taking all the columns in the table now is it possible to update that view?( ans : NO)
then I have to compulsory update that view how to do that(ans: by using instead of triggers).
What is instead of triggers?
what is syntax of case?
Difference between procedure and functions?
What is a package and what are its advantages?


Then asked on project for 5 to 10 min.
asked two queries
select duplicate records from a emp table?
Select Nth maximum salary from emp table?
SELECT * FROM EMP  WHERE ROWID NOT IN(SELECT MIN(ROWID) FROM EMP  GROUP BY EMPNO);
SELECT A.* FROM EMP A WHERE &N=(SELECT COUNT(DISTINCT(SAL)) FROM EMP B WHERE A.SAL<=B.SAL);                                                               
Why PL/SQL
Why package?
Why Triggers while you are having Constraints
Types of Triggers and Mutating Triggers
Examples for statement level and row level triggers
Bulk-fetch and bulk binding
What are the options that you use while pre-compiling a Pro*C program and why?
All about cursors
What is the need of Error Handling in PL/SQL and how will you do it
Explain Method 4 in paper
Write queries for deletion of duplicate records and nth max salary
Search and Replace in UNIX
Find syntax
Grep syntax
Copy all the fields from the result of ls –l command into different host variables and insert them into a database object
Describe materialized view
I/O redirection
Why Locks and different types of locks
Autonomous transaction
Referential Constraint