>>
You're reading...
Oracle

PL/SQL # Tip: Locally declared subprograms in PL/SQL

Hi all,

From long time, I am planning to start a blog section on PL/SQL programming tips. I have observed that as we grow more in technology, we skip some of the key features of language. When discovered later, the same tip appears to be an important bottleneck in programming.

Here, we go with the first one!!

We all know about the usage of local subprograms in anonymous blocks or subprograms. We declare local subprograms for private usage within the current block. They accomplish some small utility of the block and support modular programming. Few facts associated with the local subprograms are as below

1. Local subprograms must be defined as the last construct in the declarative section. However, their prototype can be declared along with other constructs and identifiers in the declarative section. The concept is known as Forward Declaration.

2. Results of locally declared functions in an anonymous blocks cannot be cached under ‘Oracle 11g Result Caching’ Scheme.

3. Locally declared subprograms cannot be used within SQL statements which appear in the current block. Let us conduct a small case study

create table t_demo_sql
(a number,
b number)
/

Table created.

create or replace procedure p_ins_rec (p_data number)
is
function f_getnum(p_num NUMBER) return number
is
begin
return (p_num*2);
end;
begin
insert into t_demo_sql values
(p_num, f_getnum(p_num));
end;
/

Warning: Procedure created with compilation errors.

SELECT NAME, ATTRIBUTE, TEXT FROM USER_ERRORS WHERE NAME='P_INS_REC'
/

NAME ATTRIBUTE TEXT
---------- --------- -----------------------------------------------------
P_INS_REC ERROR PLS-00231: function 'F_GETNUM' may not be used in SQL
P_INS_REC ERROR PL/SQL: ORA-00904: : invalid identifier
P_INS_REC ERROR PL/SQL: SQL Statement ignored

Instead, the results of local subprograms must be captured in local block variables. Thereafter, these block variables can be used within the SQL statements.

Above features of local subprograms appears to be small but are very useful in day to day programming. Hope you all will like it!!

Do share your suggestions, additional observations or feedback, which of course value a lot to me.

Advertisements

About Saurabh K. Gupta

Database Enthusiast, Author, Speaker and blogger

Discussion

2 thoughts on “PL/SQL # Tip: Locally declared subprograms in PL/SQL

  1. This fabulous website is a good blog.

    Posted by Monster Beats | May 28, 2012, 9:08 pm
  2. Hi, thanks very much for your article.
    Can you please provide an example of how this would be written when you refer to:
    ‘Instead, the results of local subprograms must be captured in local block variables. Thereafter, these block variables can be used within the SQL statements.’

    as it would be appreciated.

    Kind regards,

    Tom

    Posted by Tom | December 9, 2015, 5:19 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Saurabh K. Gupta

Saurabh K. Gupta

Database Enthusiast, Author, Speaker and blogger

View Full Profile →

Twitter Profile

Advanced Oracle PL/SQL Developer’s Guide – Second Edition

Advanced Oracle PL/SQL Developer's Guide - Second Edition

Oracle Advanced PL/SQL Developer Professional Guide

Oracle Advanced PL/SQL Developer Professional Guide

Disclaimer

SBHOracle is an independent blog and all the posts are based on my self experience and hands on with the technologies. It shares no relations with any of my current projects or from those in the past.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 782 other followers

SbhOracle Blog stats

  • 76,401 hits
Aggregated by OraNA
%d bloggers like this: