What is Procedure Overloading in Oracle PL/SQL | PL/SQL Procedure overloading Example

Same Name. Each overloaded version must use the same procedure name.

Different Signature. Each overloaded version must differ from all other overloaded versions in at least one of the following respects:

Number of parameters

Order of the parameters

Data types of the parameters

Number of type parameters (for a generic procedure)

Return type (only for a conversion operator)

Together with the procedure name, the preceding items are collectively called the signature of the procedure. When you call an overloaded procedure, the compiler uses the signature to check that the call correctly matches the definition.

Items Not Part of Signature. You cannot overload a procedure without varying the signature. In particular, you cannot overload a procedure by varying only one or more of the following items:

Procedure modifier keywords, such as Public, Shared, and Static

Parameter or type parameter names

Type parameter constraints (for a generic procedure)

Parameter modifier keywords, such as ByRef and Optional

Whether it returns a value

The data type of the return value (except for a conversion operator)

The items in the preceding list are not part of the signature. Although you cannot use them to differentiate between overloaded versions, you can vary them among overloaded versions that are properly differentiated by their signatures.

Oracle Pl/SQL: What is Overloading of Procedures in Oracle

Two or more procedures/functions are called overloaded when

a) They have the same names
b) Different no of formal parameters defined
c) Formal parameters differ in their datatypes/Subtypes and not in the same family
d) They belong to same subprogram/package/PL SQL Block


They are not overloaded when

a) Any of the above points are not satisfied
b) RETURN datatype alone differs and not the formal parameters

The main purpose of overloading procedures is that, when a PL SQL block is found to do a same operation with different inputs, we names them same and feed different parameters

Statistics in Oracle: DBMS_UTILITY

With DBMS_UTILITY.ANALYZE_SCHEMA you can gather all the statistics for all the tables, clusters and indexes of a schema.
Code examples
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','COMPUTE');
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_rows =>
1000);
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_percent
=> 25);
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','DELETE');
Note: It's also possible to analyze the whole database with the DBMS_UTILITY.ANALYZE_DATABASE('COMPUTE'); command.

Statistics in Oracle:Transfering statistics between database

It can be very handy to use production statistics on your development database, so that you can forecast the optimizer behavior.

You can do this the following way:

1. Create the statistics table.
exec DBMS_STATS.CREATE_STAT_TABLE(ownname =>'SCHEMA_NAME' ,stat_tab => 'STATS_TABLE' , tblspace => 'STATS_TABLESPACE');
Example:
exec DBMS_STATS.CREATE_STAT_TABLE(ownname =>'SYSTEM',stat_tab => 'STATS_TABLE');

2. Export statistics to statistics table
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('ORIGINAL_SCHEMA' ,'STATS_TABLE',NULL,'SYSTEM');

3. Import statistics into the data dictionary.
exec DBMS_STATS.IMPORT_SCHEMA_STATS('NEW_SCHEMA','STATS_TABLE',NULL,'SYSTEM');

4. Drop the statistics table.
exec DBMS_STATS.DROP_STAT_TABLE('SYSTEM','STATS_TABLE');

How to set ORACLE_HOME Path in Unix and Linux?

ORACLE_HOME refers to either:

1. a directory where the Oracle software is installed; or
2. an environment variable pointing to the directory where the Oracle software is installed. 

Check current value:

echo The current ORACLE_HOME is %ORACLE_HOME%

Set the ORACLE_HOME environment variable:

set ORACLE_HOME=C:\oracle\ora10.2

Unix & Linux

Check current value:

env | grep ORACLE_HOME

Change the ORACLE_HOME environment variable (valid for bash and ksh):

export ORACLE_HOME=/app/oracle/product/10.2.0/db_1

Oracle Pseudocoloumns : LEVEL | Oracle PL/SQL Interview Questions

LEVEL

For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on. A root row is the highest row within an inverted tree. A child row is any nonroot row. A parent row is any row that has children. A leaf row is any row without children. Figure 2-1 shows the nodes of an inverted tree with their LEVEL values.

 

To define a hierarchical relationship in a query, you must use the START WITH and CONNECT BY clauses.

Oracle CURRVAL and NEXTVAL Pseudocoloumns

You can use CURRVAL and NEXTVAL in:

* The SELECT list of a SELECT statement that is not contained in a subquery, materialized view, or view

* The SELECT list of a subquery in an INSERT statement

* The VALUES clause of an INSERT statement

* The SET clause of an UPDATE statement

Restrictions: You cannot use CURRVAL and NEXTVAL:

* A subquery in a DELETE, SELECT, or UPDATE statement

* A query of a view or of a materialized view

* A SELECT statement with the DISTINCT operator

* A SELECT statement with a GROUP BY clause or ORDER BY clause

* A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator

* The WHERE clause of a SELECT statement

* DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement

* The condition of a CHECK constraint

Also, within a single sql statement that uses CURRVAL or NEXTVAL, all referenced LONG columns, updated tables, and locked tables must be located on the same database.

Oracle Pseudocolumns : CURRVAL and NEXTVAL

CURRVAL and NEXTVAL

A sequence is a schema object that can generate unique sequential values. These values are often used for primary and unique keys. You can refer to sequence values in sql statements with these pseudocolumns:

CURRVAL

The CURRVAL pseudocolumn returns the current value of a sequence.

NEXTVAL

The NEXTVAL pseudocolumn increments the sequence and returns the next value.

You must qualify CURRVAL and NEXTVAL with the name of the sequence:

sequence.CURRVAL
sequence.NEXTVAL

To refer to the current or next value of a sequence in the schema of another user, you must have been granted either SELECT object privilege on the sequence or SELECT ANY SEQUENCE system privilege, and you must qualify the sequence with the schema containing it:

schema.sequence.CURRVAL
schema.sequence.NEXTVAL

To refer to the value of a sequence on a remote database, you must qualify the sequence with a complete or partial name of a database link:

schema.sequence.CURRVAL@dblink
schema.sequence.NEXTVAL@dblink

Oracle Pseudocolumns CURRVAL ,NEXTVAL,LEVEL,ROWID,ROWNUM

A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. This section describes these pseudocolumns:

* CURRVAL and NEXTVAL

* LEVEL

* ROWID

* ROWNUM

ROWNUM and ORDER BY in Oracle PL/SQL

From the Oracle7 Server SQL Reference Manual: ROWNUM

Oracle7 assigns a ROWNUM value to each row as it is retrieved, before rows are sorted for an ORDER BY clause, so an ORDER BY clause normally does not affect the ROWNUM of each row. However, if an ORDER BY clause causes Oracle7 to use an index to access the data, Oracle7 may retrieve the rows in a different order than without the index, so the ROWNUMs may differ than without the ORDER BY clause.

You can use ROWNUM to limit the number of rows returned by a query, as in this example:

SELECT * FROM emp WHERE ROWNUM < 10;

You can also use ROWNUM to assign unique values to each row of a table, as in this example:

UPDATE tabx SET col1 = ROWNUM;

Note that conditions testing for ROWNUM values greater than a positive integer are always false.

For example, this query returns no rows:

SELECT * FROM emp WHERE ROWNUM > 1;

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row
to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition
false. All rows subsequently fail to satisfy the condition, so no rows are returned.

Sleep function in Oralce PL / SQL

The following small function attempts to obtain a system resource, eg if an index should be created on a "busy" table. You wait a while and tried until the counter has reached the maximum.
CREATE OR REPLACE PROCEDURE Sleep_function IS
  GotIt  BOOLEAN := FALSE;
  Count  NUMBER  := 0;
BEGIN
  WHILE (NOT GotIt AND NOT (Count > 10)) LOOP
    BEGIN
      -- Try to get free slot, if OK, set GotIt = TRUE
      -- else EXCEPTION will automatically fire.
      (Insert Code here)
      GotIt := TRUE;
    EXCEPTION
      WHEN OTHERS THEN
        GotIt := FALSE;
        DBMS_LOCK.SLEEP(10);
        Count := Count + 1;
    END;
  END LOOP;
END;
/ 

Difference between a PROCEDURE & FUNCTION in oracle

Functions
----------
1) can be used with Select statement
2) Not returning output parameter but returns Table variables
3) You can join UDF
4) Cannot be used to change server configuration
5) Cannot be used with XML FOR clause
6) Cannot have transaction within function

Stored Procedure
-----------------
1) have to use EXEC or EXECUTE
2) return output parameter
3) can create table but won?t return Table Variables
4) you can not join SP
5) can be used to change server configuration
6) can be used with XML FOR Clause
7) can have transaction within SP

1. Functions must return a value(scalar, inline table or multi statement table) whereas stored proc may or may not return a value.
2. Functions can return a table whereas stored procs can create a table but can't return table.
3. Stored procs can be called independently using exec keyword whereas function are called using select statements.
4. Stored procs can be used to change server configuration(in terms of security-i.e. setting granular permissions of user rights) whereas function can't be used for this
5. XML and output parameters can't be passed to functions whereas it can be with sp's.
6. Transaction related statement can be handled in sp whereas it can't be in function.
7. Stored procedures can call a function or another stored proc similarly a function can call another function and a stored proc. The catch with function is that no user defined stored proc can be called. Only extended/system defined procs can be called.

How to Rectify Oracle Deadlock | Oracle Deadlock Rectification with a Quick solution

Each user session might have one or more tasks running on its behalf where each task might acquire or wait to acquire a variety of resources. The following types of resources can cause blocking that could result in a deadlock.

Causes to Deadlock

LocksWorker threadsMemoryParallel query execution-related resourcesMultiple Active Result Sets (MARS) resources
All the above listed may cause to Deadlock.The default interval is 5 seconds.
If the lock monitor thread finds deadlocks the deadlock detection interval will drop from 5 seconds to as low as 100 milliseconds depending on the frequency of deadlocks.
If the lock monitor thread stops finding deadlocks the Database Engine increases the intervals between searches to 5 seconds.
You can find some more information regarding Deadlock
http://msdn.microsoft.com/en-us/library/ms178104.aspx

Oracle PL/SQL Interview Questions | Oracle Database Links

What is the purpose of database links in Oracle?

Database links are created to establish communication between different databases or different environments such as development, test and production of the same database. The database links are usually designed to be read-only to access other database information . They are also useful when you want to copy production data into test environment for testing.