Skip to Main Content
  • Questions
  • advantage of procedure within a procedure

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Steve.

Asked: June 26, 2008 - 11:36 am UTC

Last updated: November 02, 2018 - 4:54 pm UTC

Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

I've seen package code where there are procedures defined within a procedure, then the "within" procedure is called once in the main procedure - actually I've also seen 3 "within" procedures and the only thing the main procedure does is call these 3 procedures.
What, if any, are the advantages of this type of arrangement over executing the code directly, or over having additional private procedures defined in the package body instead?

Thanks.
Steve

and Tom said...

The construct you refer to looks like this:

ops$tkyte%ORA10GR2> create or replace package my_pkg
  2  as
  3      procedure p;
  4  end;
  5  /

Package created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace package body my_pkg
  2  as
  3
  4  procedure p
  5  is
  6      l_data       number;
  7      l_other_data number;
  8
  9      procedure p_inner( p_data in out number )
 10      is
 11      begin
 12          l_data := l_data+1;
 13          p_data := p_data/2;
 14      end;
 15  begin
 16      l_data := 42;
 17      l_other_data := 84;
 18      p_inner( l_other_data );
 19
 20      dbms_output.put_line( 'l_data = ' || l_data || ', l_other_data = ' || l_other_data );
 21  end;
 22
 23  end;
 24  /

Package body created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec my_pkg.p
l_data = 43, l_other_data = 42

PL/SQL procedure successfully completed.




the purported advantage of the p_inner is that is can access l_data without having l_data passed.

to me, the disadvantage of p_inner is that is can access l_data without having l_data passed.


I do not personally find this construct entirely useful. I like bit sized pieces of code (a subroutine should fit on a screen). I like formal parameters to all subroutines (no globals, globals should be used sparingly and only when you need to maintain state).

So, while this construct exists in the language, it is my opinion that it should be avoided. We have specifications to allow us to expose only that which we want exposed - we can hide the rest of the internal procedures and by having them not be nested inside the other procedure - you limit their scope, what they can see and modify to:

a) their formal parameters
b) their local variables
c) globals (to be avoided....)

In general, a nested procedure like that could lead to accidental side effects ( local nested procedure can see and modify outer procedures variables ). That should be avoided.

Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A leftover perhaps.....

A reader, June 26, 2008 - 12:08 pm UTC


Something inherited from PL/1?
Tom Kyte
June 26, 2008 - 4:23 pm UTC

Ada...

Thanks

Steve, June 26, 2008 - 1:06 pm UTC

"Nested procedure", much better description, thanks for the example too.
When I first saw code last year using a nested procedure, I didn't think much about them other than I probably would never have a use. Now that I've recently seen more code using them and looked closer at how they were being used, I saw no benefit and only potential for trouble.

Thanks for confirming my suspicions.

Steve

Embedded Procedures/Functions Helpful in PL/SQL scripts.

Bob, June 26, 2008 - 2:07 pm UTC

I occassionally use an embedded procedure or function in a PL/SQL script. 

It comes in handy if there is a lot of code in the procedure and the block or the code is repetitive.

SQL> SET SERVEROUTPUT ON SIZE 50000
SQL>
SQL> DECLARE
  2
  3    PROCEDURE P_PRINT( v_printme VARCHAR2 ) IS
  4
  5    BEGIN
  6      dbms_output.put_line(v_printme);
  7    END P_PRINT;
  8
  9    FUNCTION F_CALC_RATING( age NUMBER, weight NUMBER) return number IS
 10
 11    BEGIN
 12      RETURN age * weight;
 13    END F_CALC_RATING;
 14
 15  BEGIN
 16
 17    P_PRINT('HELLO World');
 18
 19    P_PRINT('Rating for Tom is ' || TO_CHAR(F_CALC_RATING(35, 175)));
 20
 21    P_PRINT('Rating for Joe is ' || TO_CHAR(F_CALC_RATING(27, 275)));
 22
 23    P_PRINT('Rating for John is ' || TO_CHAR(F_CALC_RATING(52, 185)));
 24
 25    P_PRINT('Goodbye, thank you for dropping by.');
 26
 27  END;
 28  /
HELLO World
Rating for Tom is 6125
Rating for Joe is 7425
Rating for John is 9620
Goodbye, thank you for dropping by.

PL/SQL procedure successfully completed.

SQL>

Tom Kyte
June 26, 2008 - 4:36 pm UTC

Ok, yes, this I agree with IF you are not allowed to install code permanently.


And it just transported me back to version 6.

which had plsql

but no stored procedures

and the only way to write a legible block of code of any size (only anonymous blocks in embedded sql back then) was to do this.

So, yes, that particular example here is a "package before packages"

Nested procedures and even globals can be useful is used carefully

Ray DeBruyn, June 26, 2008 - 3:23 pm UTC

Hi Tom,

I do a lot of PL/SQL Web Toolkit pages. I may create a customer list, and edit form and a submit procedure. I'll create a package with these and that's all the package is used for.

I don't want to enter a ton of code, so I'll put enough for an idea of a submit procedure.

The main code has three executable lines, initialize, do_dml and do_redirect. The idea is to break down the tasks into small, easy to follow sets of code. I've gotten to follow coding standards that allow me to create a code generator or really more of a smart template.

PROCEDURE edit_submit (p_person_id IN data_element_users.id%TYPE
, z_chk IN NUMBER
, p_business_id IN bk_businesses.id%TYPE
, p_parameter_names IN names_arr DEFAULT empty_names_arr
, p_parameter_values IN values_arr DEFAULT empty_values_arr
, p_delete IN VARCHAR2 DEFAULT NULL )
IS

inserting BOOLEAN;
updating BOOLEAN;
deleting BOOLEAN;

PROCEDURE set_parameters
IS
BEGIN

NULL;
--{GENERATED_DML_SET_PARAMETERS} 21
FOR rowIndex IN 1..p_parameter_names.COUNT LOOP

IF p_parameter_names(rowIndex) = 'p_name' THEN

business_info.name := p_parameter_values(rowIndex);

END IF;

IF p_parameter_names(rowIndex) = 'p_primary_phone_number' THEN

business_info.primary_phone_number := p_parameter_values(rowIndex);

END IF;

IF p_parameter_names(rowIndex) = 'p_website_url' THEN

business_info.website_url := p_parameter_values(rowIndex);

END IF;

IF p_parameter_names(rowIndex) = 'p_business_status_id' THEN

business_info.business_status_id := p_parameter_values(rowIndex);

END IF;

IF p_parameter_names(rowIndex) = 'p_active_flag_yn' THEN

tennant_info.active_flag_yn := NVL(p_parameter_values(rowIndex), 'N');

END IF;

IF p_parameter_names(rowIndex) = 'p_primary_room_number' THEN

tennant_info.primary_room_number := p_parameter_values(rowIndex);

END IF;

END LOOP;

END set_parameters;

PROCEDURE initialize
IS
BEGIN

initialize_global(p_person_id, z_chk, p_business_id);

set_parameters;

IF is_eis_admin OR is_business_admin THEN NULL;
ELSE

error_message := error_message || '<FONT CLASS=ERROR>You do not have permission to view this page.</FONT><BR>';

END IF;

--DEVELOPER set boolean conditions
deleting := p_delete IS NOT NULL;
inserting := business_info.id IS NULL;
updating := business_info.id IS NOT NULL;

END initialize;

FUNCTION validate_insert
RETURN BOOLEAN
IS

v_return BOOLEAN;

BEGIN

v_return := TRUE;

--DEVELOPER define insert validation rules
IF 'CONDITION' = 'BAD CONDITION' THEN

error_message := error_message || '<FONT CLASS=ERROR>Validation failed on ...</FONT><BR>';
v_return := FALSE;

END IF;

RETURN v_return;

END validate_insert;

PROCEDURE do_insert
IS
BEGIN

IF validate_insert THEN

SELECT seq_bk_tables.NEXTVAL
INTO business_info.id
FROM DUAL;

INSERT INTO bk_businesses VALUES business_info;

tennant_info.building_id := building_info.id;
tennant_info.business_id := business_info.id;

INSERT INTO bk_tennants VALUES tennant_info;

END IF;

EXCEPTION
WHEN OTHERS THEN

error_message := error_message || '<FONT CLASS=ERROR>Failed to insert due to the following unexpected error: ' || SQLERRM || '</FONT><BR>';
business_info.id := NULL;

END do_insert;

FUNCTION validate_update
RETURN BOOLEAN
IS

v_return BOOLEAN;

BEGIN

v_return := TRUE;

--DEVELOPER define update validation rules
IF 'CONDITION' = 'BAD CONDITION' THEN

error_message := error_message || '<FONT CLASS=ERROR>Validation failed on ...</FONT><BR>';
v_return := FALSE;

END IF;

RETURN v_return;

END validate_update;

PROCEDURE do_update
IS
BEGIN

IF validate_update THEN

UPDATE bk_businesses
SET ROW = business_info
WHERE id = business_info.id;

UPDATE bk_tennants
SET ROW = tennant_info
WHERE business_id = tennant_info.business_id
AND building_id = tennant_info.building_id;

END IF;

EXCEPTION
WHEN OTHERS THEN

error_message := error_message || '<FONT CLASS=ERROR>Failed to update due to the following unexpected error: ' || SQLERRM || '</FONT><BR>';

END do_update;

FUNCTION validate_delete
RETURN BOOLEAN
IS

v_return BOOLEAN;

BEGIN

v_return := TRUE;

--DEVELOPER define delete validation rules
IF is_eis_admin THEN NULL;
ELSE

error_message := error_message || '<FONT CLASS=ERROR>You do not have permission to delete businesses.</FONT><BR>';
v_return := FALSE;

END IF;

RETURN v_return;

END validate_delete;

PROCEDURE do_delete
IS
BEGIN

IF validate_delete THEN

DELETE bk_businesses
WHERE id = business_info.id;

business_info.id := NULL;

END IF;

EXCEPTION
WHEN OTHERS THEN

error_message := error_message || '<FONT CLASS=ERROR>Failed to delete due to the following unexpected error: ' || SQLERRM || '</FONT><BR>';

END do_delete;

PROCEDURE do_dml
IS
BEGIN

IF error_message IS NULL THEN

IF deleting THEN

do_delete;

ELSIF updating THEN

do_update;

ELSIF inserting THEN

do_insert;

END IF;

END IF;

END do_dml;

PROCEDURE do_redirect
IS
BEGIN

error_message := NVL(error_message, '<FONT CLASS=SUCCESS>Success</FONT><BR>');
error_message := REPLACE(error_message, ' ', '%20');

--DEVELOPER add redirect parameters
owa_util.redirect_url('p_business.edit_form'
|| '?p_person_id=' || p_person_id
|| '&z_chk=' || z_chk
|| '&p_business_id=' || business_info.id
|| '&p_message=' || error_message);

END do_redirect;

BEGIN

initialize;

do_dml;

do_redirect;

END edit_submit;
Tom Kyte
June 26, 2008 - 4:38 pm UTC

one word for you:


packages


use them. and you do not even want to know what I think of:
            EXCEPTION
                WHEN OTHERS THEN

                    error_message := error_message || '<FONT CLASS=ERROR>Failed 
to delete due to the following unexpected error: ' || SQLERRM || '</FONT><BR>';

            END do_delete;


but, suffice to say, it is not good.

Follow on from last post

Ray DeBruyn, June 26, 2008 - 3:29 pm UTC

I had too much in the last post and I just want to add this. The advantage of nesting the procedures is that I can define more parameters through p_parameter_names and p_parameter_values that immediately become available. I don't need to modify code to change the parameter list as new ones need to be added.

I also do application level security and there is data I need for all procedures in this package, so I create an initialize_global procedure that populates package global variables for me.
Tom Kyte
June 26, 2008 - 4:43 pm UTC

I don't see that as an advantage. I don't mind having formal parameter lists

In fact, I demand it. A change that adds a parameter is a non-insignificant change, it takes review. Nothing better than a type safe formal named parameter.

sorry, but I am not a fan of this particular coding style myself.

and, it should be in a package, the nested procedures provide nothing a package couldn't provide "better"

Ray DeBruyn, June 26, 2008 - 5:38 pm UTC

This was a piece of code from a package. I thought I was adding too much to begin with, so I just chose the one example piece.

One big difference here is that this is the client code. This is the one place where I need to handle all exceptions including the unexpected ones. That being said, there's not any real effort in this snippet to find expected errors.

I like this style also for formatting - tables within tables etc. If the code starts to get too unruly, I just break it apart into smaller chunks. I put those into procedures and call them separately.

Also, I hate html that has incorrect open vs close tags. If I have a TABLE with a TR that has two TDs and each TD has a table, I'll code it as:

htp.p('<TD VALIGN=TOP>');

add_table;

htp.p('</TD>');

add_table will have all the TABLE, TR and TD tags for that table - no confusion.
Tom Kyte
June 26, 2008 - 5:40 pm UTC

actually, truth be told, for mod_plsql - I'd be very hard pressed to write code anymore.

http://apex.oracle.com/

It is been years since I've actually coded an "accept" routine by hand..

Perhaps a warning ?

Gary, June 26, 2008 - 7:32 pm UTC

"local nested procedure can see and modify outer procedures variables"
Perhaps you could petition Oracle development for a warning about this, similar to the 'WHEN OTHERS...' warning you got added.

Great link to Apex

Stew, June 26, 2008 - 11:00 pm UTC

I thought it was highly appropriate, and at the same time very funny because you just provided the link and not the product name. I've become a big fan of Apex in the last 6 months and just came back from the ODTUG conference where I met hundreds of like-minded programmers (plus some DBAs who looked a bit lost?). Thanks for the interesting and thoughtful keynote speech you made. I hope you got to enjoy the French Quarter some before you left! :-)

How call sub-sub-procedure

A reader, October 31, 2018 - 8:43 am UTC

any way to call p_inner() while it is a subprogram inside other ?
Chris Saxon
November 01, 2018 - 10:46 am UTC

If you declare a nested procedure, you can only call it within the scope of that declaration. So if you have a procedure within a procedure, you can only access it from the body of the parent procedure:

declare
  procedure p ( p int ) as
  
    procedure p_inner ( p int ) as
    begin
      dbms_output.put_line ( 'Value ' || p );
    end p_inner;
   
  begin
    p_inner ( p );
  end p;
begin
  p ( 1 );
end;
/

Value 1

declare
  procedure p ( p int ) as
  
    procedure p_inner ( p int ) as
    begin
      dbms_output.put_line ( 'Value ' || p );
    end p_inner;
   
  begin
    p_inner ( p );
  end p;
begin
  p_inner ( 1 );
end;
/

PLS-00201: identifier 'P_INNER' must be declared

Nesting in PL/SQL is for the birds.

lh, November 02, 2018 - 12:11 pm UTC

For Ada it was said that 'Nesting in Ada is for the birds'.
Chris Saxon
November 02, 2018 - 4:54 pm UTC

Indeed:

Given a data abstraction construct like the
Ada package and in light of current thoughts on
programming methodology, we feel that nesting is an
anachronism.


https://www.doc.ic.ac.uk/~alw/doc/papers/ada80.pdf

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library