An article suggestion from PacktPub.com

Dear All All

Packt publishers have extracted a chapter of my book as an article.

PL/SQL: Using Collections The Oracle Advanced PL/SQL Developer Professional Guide helps you master advanced PL/SQL concepts. Besides the clear and precise explanation on advanced topics, it also contains example code and demonstrations, which gives a sense of application and usage to readers In this article by Saurabh Gupta, author of Oracle Advanced PL/SQL Developer Professional Guide, we'll cover the following topics: Collections—an overview Associative arrays Nested tables Varray PL/SQL collection methods

Click here to view the full article.

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.