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');

Oracle PL/SQL Tutorial

SQL commands are instructions used to communicate with the database to perform specific task that work with data. SQL commands can be used not only for searching the database but also to perform various other functions like, for example, you can create tables, add data to tables, or modify data, drop the table, set permissions for users. SQL commands are grouped into four major categories depending on their functionality:

Data Definition Language (DDL) - These SQL commands are used for creating, modifying, and dropping the structure of database objects. The commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE.
Data Manipulation Language (DML) - These SQL commands are used for storing, retrieving, modifying, and deleting data. These commands are SELECT, INSERT, UPDATE, and DELETE.
Transaction Control Language (TCL) - These SQL commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT.
Data Control Language (DCL) - These SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE