Re: How does Oracle do this?

Vladimir M. Zakharychev wrote:
> 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

Interesting, I had a feeling some 'internal magic' was going on. Unfortunately my queues make use of LOBs so that rules out the anydata type. Its *VERY* frustrating having to write the same function over and over to achive what I need here. I will have a hunt on metalink to see if there is anyway to get using opaque types (although I will not hold my breath!).

Thanks for the help.

Stephen. Received on Wed Aug 16 2006 - 16:10:56 CDT