DB2 support for internal procedures

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dhruv19280 - 14 Jun 2006 08:43 GMT

Can someone confirm that DB2 UDB v8.1.2 does NOT support internal
procedures as supported by Oracle? For example, this would work in
Oracle, but not in DB2. Am I correct?

CREATE OR REPLACE PROCEDURE TPROC1()
IS
--Variable and cursor declarations
PROCEDURE insert_tproc1 ()
IS
--Variable and cursor declarations
BEGIN
--Child Procedure body
END
BEGIN
--Parent Procedure body
END;

In case the same functionality can be achieved in some other way,
please let me know. Thanks in advance.

Reply to this Message

mike - 14 Jun 2006 08:52 GMT

You can have nested SQL PL procedures with DB2, in the sense
that one procedure can call another procedure, and that other
procedure may or may not be in the same source-code file
, but I think that db2 presently does not support the syntax that you
show.

> Can someone confirm that DB2 UDB v8.1.2 does NOT support internal
> procedures as supported by Oracle? For example, this would work in
[quoted text clipped - 15 lines]
> In case the same functionality can be achieved in some other way,
> please let me know. Thanks in advance.

Reply to this Message

dhruv19280 - 14 Jun 2006 08:58 GMT

Mike,

How are internal procedures different from normal procedures in Oracle?
Any difference at all? I was under the impression that the syntax above
somehow restricts the scope of the internal procedure. But it seems
not.

All help would be appreciated.

Thanks once again.

> You can have nested SQL PL procedures with DB2, in the sense
> that one procedure can call another procedure, and that other
[quoted text clipped - 21 lines]
> > In case the same functionality can be achieved in some other way,
> > please let me know. Thanks in advance.

Reply to this Message

Serge Rielau - 14 Jun 2006 11:36 GMT

> Mike,
>
[quoted text clipped - 32 lines]
>>> In case the same functionality can be achieved in some other way,
>>> please let me know. Thanks in advance.

Now, that's an Oracle question.. My assumption would be the same. It
ought to be a scope thing. That what you see should be 'private procedure'.
Anyway. What speaks against placing the procedure into another schema.
You can control who accesses it via authorization.
Functionally there is certainly no loss.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message

Mark Townsend - 14 Jun 2006 15:33 GMT

> Mike,
>
[quoted text clipped - 6 lines]
>
> Thanks once again.

I think you are talking about packages, which allow the definition of
public and private interfaces and variables in PL/SQL in Oracle. See
http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14261/packages.htm

Reply to this Message