Procs within procs without a package

[welchdor]

This seems like a dumb question to me, but I have never been clear about this... Can you have a procedure that has other procedures in it without using a package? It seems I always run into times where I have a block of code I'll be calling several times within a procedure, and the "goto" doesn't seem to return control back to after where it was called, so I guess I have to make these blocks other procedures. If I don't do this with a package, I don't know how to structure it as just a procedure.
Can someone clear this up for me?
Thanks very much.

[TimHall]

Yes you can. Put the procedure/function in the declaration section:

SQL> SET SERVEROUTPUT ON
SQL> CREATE OR REPLACE PROCEDURE a AS
2
3 PROCEDURE b AS
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE('Entered b');
6 END b;
7
8 BEGIN
9 DBMS_OUTPUT.PUT_LINE('Entered a');
10 b;
11 END a;
12 /

Procedure created.

SQL> exec a;
Entered a
Entered b

PL/SQL procedure successfully completed.

SQL> DROP PROCEDURE a;

Procedure dropped.

SQL>

I think you should really ask if you should be doing this. I really don't see a good reason for using free standing procedures or functions anywhere. I can't remember the last time I did it in a real-world environment. Packages allow you to break the dependency chain preventing endless invalidations of related procedures and triggers.

Take my advice, ditch free standing procedures and functions now! In addition, code nothing in a trigger other than a call to a packaged procedure. You'll thank me for it!

Cheers

[Orca777]

Hi,
this feature is inherited from pascal;
And sometimes local Procs/Funcs i need when i run an anonymous PL/SQL-Block;
This works due i have no privilege for CREATE PROCEDURE/FUNCTION and could be used run structured PL/SQL without creating a Package in a prod-env.

Orca

[bensr]

I do agree with TimHall,
why would you do something like that.
Using a package is much better practice, and gives a lot of other possibilities for using procedures/functions and variables more then once.