CODEWITHSUNDEEP
sqloracle-databaseobject
Jyo
Jyo

Reputation: 47

need help to understand on below oracle code

I came across some oracle snippet. Can anyone explain it and what the code returns?

I tried google to find out what does odciobjectlist and did not get correct explanation.

Select ID,Objectname Name 
from table(SYS.odciObjectlist(SYS.OdciObject('1'.'Appr'),SYS.ODCIObject('2','Pending')))

Upvotes: 1

Views: 377

Answers (1)

APC
APC

Reputation: 146349

odciObjectlist is one of several Oracle owned types, which we can use for building our own extensions to Oracle. Find out more

Specifically SYS.odciObjectlist is an array of type SYS.odciObject, which is intended to store information about a schema object - schema name, object name. However, the constructor is fairly forgiving and we can use odciObject to store pairs of any string.

In the case of your query, it looks like somebody is instantiating a collection of (?) statuses (?) which are rendered as a queryable table using the table() function. It's a cheap way of generating a result set on the fly, rather than using some selects from dual and UNION ALL.

With table() we can take any SQL nested table collection and query it in the FROM clause of a SELECT, with a result set which has one row for each object in the collection. So if we take the query posted in your question and fix the grammar like this ...

Select objectschema as id
      ,objectname   as name
from table(SYS.odciObjectlist(
                SYS.OdciObject('1','Appr')
               ,SYS.OdciObject('2','Pending')
                             )
          )

...the output we get is:

ID   NAME                                                                                                                              
---- ---------------------------------------------------------
1    Appr                                                                                                                              
2    Pending

Upvotes: 4

Related Questions