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
create or replace procedure p_ins_rec (p_data number)
function f_getnum(p_num NUMBER) return number
insert into t_demo_sql values
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.