Re: How does Oracle do this?

Sybrand Bakker wrote:
> On 16 Aug 2006 08:28:37 -0700, "stephen O'D"
> <stephen.odonnell_at_gmail.com> wrote:
>
> >How can I do it the 'Oracle' way so that I can just write one of these
> >functions or is Oracle using some insider tricks that we cannot use?
>
> No.
> Oracle is using overloading. You can use overloading too, if you put
> the procedure in a package.
>
> --
> Sybrand Bakker, Senior Oracle DBA

Not quite. At least not in DBMS_AQ. Oracle uses opaque types there ("<OPAQUE_1"> and "<ADT_1"). The types are defined in STANDARD, but not available for general use, at least I didn't figure out what's needed to make use of them in user-defined stored procedure. It seems that the types are reserved for internal use and PL/SQL compiler doesn't accept them anywhere but a few select packages and types, which are probably hard-coded.

Not all is lost though. One may want to try using SYS.AnyData for queue payload. This will allow to encapsulate ADTs into AnyData wrappers with AnyData.ConvertObject() method and enqueue or dequeue AnyData instances with single procedure, retrieving specific object instance later with AnyData.GetObject() call. AnyData has its limitations though: it can't be stored persistently if wrapped type contains LOBs, and since queues store payload persistently until it's dequeued, types with LOB attributes won't work. Everything else will go just fine.

Please note that I didn't personally test the above, so one may hit some obstacles on her path to enlightenment, but according to the docs it should work.

Hth,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Wed Aug 16 2006 - 13:59:12 CDT