Home arrow Oracle arrow Database Interaction with PL/SQL: Sub-programs in Depth

Database Interaction with PL/SQL: Sub-programs in Depth

This is part 13 of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, we concentrated on procedures and discussed some issues of variable scoping with respect to sub-programs. In this article we proceed with some tips on using procedures together with the introduction to functions.

  1. Database Interaction with PL/SQL: Sub-programs in Depth
  2. Sub-programs interacting with an Oracle database
  3. Procedures with parameters
  4. Using %ROWTYPE in parameter declarations
  5. Introduction to FUNCTION
By: Jagadish Chatarji
Rating: starstarstarstarstar / 12
August 16, 2005

print this article



Please note that all the examples in this series have been tested only with Oracle 10g. I didn’t really test them with all the previous versions of Oracle. I suggest you to refer the documentation of respective version you are using, if any of the programs failed to execute.

Sub-programs calling other sub-programs

This is a very frequently used technique in any structured programming language. In my previous article, I stated that a sub-program needs to be executed from the main program. A sub-program can also be executed from another sub-program. In that way, they can be executed any number of times. 

So, in reality, we may not know when a sub-program gets executed. It may be executed from our main program, another sub-program, or even from an external source, in the case of stored procedures (discussed later).

Let’s go through a simple (but slightly confusing) example:


            procedure dispMsg as


                        dbms_output.put_line('from dispMsg');


            procedure printMsg as


                        dbms_output.put_line('from printMsg');


                        dbms_output.put_line('from printMsg2');





            dbms_output.put_line('Back to main');


The output of the above program would be as follows:

from dispMsg
from printMsg
from dispMsg

Now let us discuss the above program in detail. The program starts its execution at BEGIN (of course after ‘declare’). The first statement after BEGIN is ‘dispMsg’. That means I am immediately executing the sub-program ‘dispMsg’ (which displays the first message as ‘from dispMsg’). 

The next statement within the main program is ‘printMsg’. Now the flow of control jumps to ‘printMsg’. It executes the first statement within the ‘printMsg’ and displays the message ‘from printMsg’. Next it executes the second statement within the ‘printMsg’, which is ‘dispMsg’. The control again jumps to ‘dispMsg’ and executes it (which displays the message ‘from dispMsg’). After finishing ‘dispMsg’ the control returns back to the third statement of ‘printMsg’ (which displays the message ‘from printMsg2’). And finally, the control returns back the last statement of the main program (which displays ‘back to main’).

The most important issue to remember is that the control always returns back to the next statement of the calling statement (of sub-program). 

>>> More Oracle Articles          >>> More By Jagadish Chatarji

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: