Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

Oracle 11g Subprogram Overloading

By Saurabh Gupta | on June 17, 2011 |
Oracle 11g Tutorials

Polymorphism is an Object Oriented Programming language feature which allows the objects to accept same input and behave differently. It can be implemented in three ways i.e. overriding, hiding, and overloading.

In Oracle, overloading is one of the polymorphism techniques which allow the creation of more than one program units, within the vicinity of block, with the same name but different parameter lists.

In this tutorial, we shall move ahead to understand the overloading techniques and restrictions in Oracle.

Overloading: Background

Overloading is not new to Oracle. It is one of the most extensively used concepts in the making of Oracle built in functions. Many of the built in functions, which are defined in the STANDARD package, DBMS_OUTPUT or others packages, are overloaded.

Code [1]:

PUT_LINE function can be used with numeric, string or date inputs as shown below.

SQL> EXEC DBMS_OUTPUT.PUT_LINE(100);
100
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC DBMS_OUTPUT.PUT_LINE('Oracle');
Oracle
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC DBMS_OUTPUT.PUT_LINE(SYSDATE);
18-MAY-11
 
PL/SQL PROCEDURE successfully completed.

Another overloaded function TO_CHAR can be used with date, number and string inputs. Its overloaded headers are as below.

FUNCTION TO_CHAR (RIGHT DATE) RETURN VARCHAR2;
FUNCTION TO_CHAR (LEFT NUMBER) RETURN VARCHAR2;
FUNCTION TO_CHAR (LEFT DATE, RIGHT VARCHAR2) RETURN VARCHAR2;
FUNCTION TO_CHAR (LEFT NUMBER, RIGHT VARCHAR2) RETURN VARCHAR2;

Many mathematical functions accept all type of input which belongs to the same data type family. This implies that Oracle kernel is quite familiar with overloading and its benefits.

Overloading of Local and Packaged subprograms

Oracle allows overloading of subprograms in local scope, packaged scope or object type scope. The subprograms declared in these scopes can exist with same name with different signatures. Note that a ‘subprogram signature’ is a component of its name and parameter list only. As the subprogram name is same, the parameters passed to the subprograms should different in count, order, or belonging data type family.

Only local or packaged procedure and functions can be overloaded. Standalone (or stored) subprograms cannot be overloaded as they need to obey the rule of unique name and namespace categorization. Object type methods contained by super type and subtype can be overloaded and overridden too.

I shall demonstrate overloading with the help of a sample program in Code [2]

Code [2]
CREATE OR REPLACE PACKAGE PKG_OVERLOAD_TEST 
IS
   PROCEDURE P_TEST1(A NUMBER, B NUMBER);
   PROCEDURE P_TEST1(A VARCHAR2);
END;
 
CREATE OR REPLACE PACKAGE BODY PKG_OVERLOAD_TEST
IS
   PROCEDURE P_TEST1(A NUMBER, B NUMBER)
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE('Sum of two numbers is:'||to_char(A+B));
   END;
 
   PROCEDURE P_test1( a varchar2) 
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE('The input string is:'||A);
   END;
END;

The overloaded subprograms show different behavior based on the inputs provided.

Code [3]:

Testing the procedure call with two number inputs.

SQL> BEGIN
  2     PKG_OVERLOAD_TEST.P_TEST1(1,2);
  3  END;
  4  /
SUM OF two numbers IS:3
 
PL/SQL PROCEDURE successfully completed.

When the PL/SQL engine receives the subprogram invocation call, it compares the inputs with the parameter list available for overloaded units. It executes only the one whose signature matches with the input and produces the output.

In the Code [3], the two inputs were of number type. Optimizer matches them with the overloaded modules available for PKG_OVERLOAD_TEST. Parameter list for the first subprogram header matches the call and is executed.

Similarly, second overloaded subprogram can also be tested as below with a string input.

Code [4]
SQL> BEGIN
  2     PKG_OVERLOAD_TEST.P_TEST1('Exforsys');
  3  END;
  4  /
The INPUT string IS:Exforsys
 
PL/SQL PROCEDURE successfully completed.

Overloading Usage guidelines/restrictions

If the parameter list of the overloaded program units appears same in terms of count and their data types, Oracle raises exception and restricts the creation of the package. Note that Oracle allows overloading, not duplication.

Special attention should be kept towards data type of the parameters. Even if the parameter data types are different, but belong to the same data type family, Oracle prevents the overloading of subprograms.

Code [5]:

Two procedures L_CALC accept NUMBER and INTEGER type inputs. But the package body fails to compile the program unit because INTEGER and NUMBER belong to the same data type family.

DECLARE
   PROCEDURE L_CALC(A NUMBER)
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE(A*10);
   END;
 
   PROCEDURE L_CALC(A INTEGER)
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE(A*10);
   END;
BEGIN
   L_CALC(7);
END;
/
   L_CALC(7);
   *
ERROR at line 13:
ORA-06550: line 13, COLUMN 4:
PLS-00307: too many declarations OF 'L_CALC' MATCH this CALL
ORA-06550: line 13, COLUMN 4:
PL/SQL: Statement ignored

Oracle allows the creation of a overloaded function with same signature but different RETURN type. But at the time of execution, optimizer finds conflict of headers and raises exception PLS-00307.

 Overloading is not possible if the parameters differ only in their passage mode.

Code [6]:

Locally overloaded procedures L_ADD fail to overload due to same signatures.

SQL> DECLARE
  2     PROCEDURE L_ADD(A IN NUMBER, B IN NUMBER) ----//Local procedure 1
  3     IS 
  4     BEGIN
  5        DBMS_OUTPUT.PUT_LINE(A+B);
  6     END;
  7     PROCEDURE L_ADD(A IN NUMBER, B OUT NUMBER)  ----//Local procedure 2
  8     IS
  9     BEGIN
 10        B:= A;
 11     END;
 12  BEGIN
 13     L_ADD(1,2);
 14  END;
 15  /
   L_ADD(1,2);
   *
ERROR at line 13:
ORA-06550: line 13, COLUMN 4:
PLS-00307: too many declarations OF 'L_ADD' MATCH this CALL
ORA-06550: line 13, COLUMN 4:
PL/SQL: Statement ignored

Metadata for overloaded subprograms in Packages

Package metadata is captured in USER_PROCEDURES dictionary view. For overloaded subprograms, the system generates a sequence id, in order of their prototyping within the package.

Run the below query to view the package metadata along with the overloaded subprogram sequence.

Code [7]
SQL> SELECT OBJECT_NAME, OBJECT_TYPE, PROCEDURE_NAME, SUBPROGRAM_ID, OVERLOAD 
     FROM USER_PROCEDURES 
     WHERE OBJECT_NAME='PKG_OVERLOAD_TEST';

Overloading in Object types

In Object types, polymorphism is practiced through Overloaded and Overridden methods. Overloading is practiced in inherited object types where the subtypes have a member method with the same name as that of inherited one, but differ in nature of parameters. Based on the member methods signatures, optimizer routes the invocation calls and produces the output.

For example, a super (parent) type CALC_OT contains multiple calculation member methods, one of which is to add 2 numbers as shown in the below definition.

CREATE OR REPLACE TYPE CALC_OT AS OBJECT
( …
 MEMBER PROCEDURE ADD(P1 NUMBER, P2 NUMBER)
);

Now a sub (child) type ADD_OT adds inherited number inputs with its owned ones. Its member subprogram can be overloaded and created with the same name as the inherited one.

CREATE OR REPLACE TYPE ADD_OT UNDER CALC_OT
(…
…
MEMBER PROCEDURE ADD(P3 NUMBER)
);

Overriding is another technique to create a subprogram in object type hierarchy with the same name. But unlike overloading, here the member method is redefined in the sub (child) type body. This eclipses the subprogram definition available in the type body of super (parent) type. If the subtype extends to some other types too, they inherit the redefined version of the overridden subprogram and not the original version.

When a type variable invokes the member method, Oracle executes the associated member subprogram with that type only.

Code [8]:

A super type OT_OVERLOAD_TEST has been created, which contains DISPLAY method to print the type attribute values. The type has been extended to OT_SUB_OVERLOAD, which inherits its parent’s attributes and methods. But it declares an overriding method DISPLAY to print the alternate name for it id.

SQL> CREATE OR REPLACE TYPE OT_OVERLOAD_TEST AS OBJECT
	(ID NUMBER,
	 NAME VARCHAR2(100),
	 MEMBER FUNCTION DISPLAY RETURN VARCHAR2
	 )
      NOT FINAL;
	/	
 
TYPE created.
 
SQL> CREATE OR REPLACE TYPE BODY OT_OVERLOAD_TEST AS
        MEMBER FUNCTION DISPLAY RETURN VARCHAR2
        IS
        BEGIN
           RETURN 'Name for the id:'||to_char(ID)||' is '||NAME;
        END;
    END;
    /
TYPE body created.
 
SQL> CREATE OR REPLACE TYPE OT_SUB_OVERLOAD UNDER OT_OVERLOAD_TEST
     (ALTER_NAME VARCHAR2(100),
      OVERRIDING MEMBER FUNCTION DISPLAY RETURN VARCHAR2);
      /
 
TYPE created.
 
SQL> CREATE OR REPLACE TYPE BODY OT_SUB_OVERLOAD AS
      OVERRIDING MEMBER FUNCTION DISPLAY RETURN VARCHAR2
      IS
      BEGIN
         RETURN 'Alternate name for the id:'||to_char(SELF.ID)||' is '||ALTER_NAME;
      END;
    END;
    /
 
TYPE body created.
 
SQL> DECLARE
  2  l_parent ot_overload_test := ot_overload_test(100,'Exforsys');
  3  l_child ot_sub_overload := ot_sub_overload(100,'Exforsys','Oracle 11g tutorials');
  4  BEGIN
  5  dbms_output.put_line(l_parent.display());
  6  Dbms_Output.Put_Line(L_Child.Display());
  7  END;
  8  /
Name FOR the id:100 IS Exforsys
Alternate name FOR the id:100 IS Oracle 11g tutorials
 
PL/SQL PROCEDURE successfully completed.
« « Oracle 11g Read Only Tables
People Skills Tips and Strategies » »

Author Description

Avatar

Free Training

RSSSubscribe 394 Followers
  • Popular
  • Recent
  • Conditional compilation in Oracle PL/SQL

    June 28, 2011 - 0 Comment
  • Oracle Pragma

    July 6, 2011 - 0 Comment
  • Oracle 11g SecureFiles

    September 4, 2011 - 0 Comment
  • Manage Oracle dependencies

    August 16, 2011 - 0 Comment
  • Oracle 11g Virtual Columns

    May 30, 2011 - 0 Comment
  • UTL_FILE

    August 10, 2011 - 0 Comment
  • Oracle 11g Invisible Index

    May 27, 2011 - 0 Comment
  • Compound Triggers in Oracle 11g

    May 9, 2011 - 0 Comment
  • Oracle 11g Collections

    June 2, 2011 - 0 Comment
  • Oracle VPD implementation

    October 24, 2011 - 0 Comment
  • Oracle XML Storage

    June 19, 2012 - 0 Comment
  • Oracle Analytic Enhancements

    June 19, 2012 - 0 Comment
  • Oracle Partitioning Overview

    January 17, 2012 - 0 Comment
  • PL/Scope and PL/SQL Hierarchical Profiler

    November 6, 2011 - 0 Comment
  • Oracle VPD implementation

    October 24, 2011 - 0 Comment
  • Oracle 11g SecureFiles

    September 4, 2011 - 0 Comment
  • Manage Oracle dependencies

    August 16, 2011 - 0 Comment
  • UTL_FILE

    August 10, 2011 - 0 Comment
  • Oracle Pragma

    July 6, 2011 - 0 Comment
  • Conditional compilation in Oracle PL/SQL

    June 28, 2011 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • Oracle XML Storage
  • Oracle Analytic Enhancements
  • Oracle Partitioning Overview
  • PL/Scope and PL/SQL Hierarchical Profiler
  • Oracle VPD implementation

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT