5 Replies Latest reply: Aug 24, 2010 7:53 AM by 791732 RSS

    OdciNumberList, CAST MULTISET

    791732
      Can someone please explain CAST, MULTISET and OdciNumberList in small example with a table?

      Please....PLEASE do not point me to a reference book because I already went through it but, for some reason I am not understanding this concept.
      I know what cast does for sure. But, I don't know how to use all three of these together?

      I will really appreciate this.

      thanks
        • 1. Re: OdciNumberList, CAST MULTISET
          MichaelS
          Can someone please explain CAST, MULTISET and OdciNumberList in small example with a table?
          Can't imagine a smaller example with all ingredients:
          SQL> select * from table(cast(multiset(select empno from emp) as sys.odcinumberlist))
          /
          COLUMN_VALUE
          ------------
                  7369
                  7499
                  7521
                  7566
                  7654
                  7698
                  7782
                  7788
                  7839
                  7844
                  7876
                  7900
                  7902
                  7934
          
          14 rows selected.
          Multiset generates a system-generated collection which is cast'ed to the known collection sys.odcinumberlist.
          The TABLE function takes this collection as input and returns the collection elements.
          • 2. Re: OdciNumberList, CAST MULTISET
            791732
            This definitely helps. How about another small example with OdciNumberList, CAST MULTISET & CONNECT BY LEVEL?
            • 3. Re: OdciNumberList, CAST MULTISET
              MichaelS
              How about another small example with OdciNumberList, CAST MULTISET & CONNECT BY LEVEL?
              Not sure what you want to see: Examples can be endless ;)
              SQL> select *
                from table (cast (multiset (select level
                                              from dual
                                            connect by level <= 10) as sys.odcinumberlist))
              /
                 COLUMN_VALUE
              ---------------
                            1
                            2
                            3
                            4
                            5
                            6
                            7
                            8
                            9
                           10
              
              10 rows selected.
              • 4. Re: OdciNumberList, CAST MULTISET
                678284
                OdciNumberList sample.
                select * from table(sys.odcinumberlist(1,2,3,4,5));
                • 5. Re: OdciNumberList, CAST MULTISET
                  791732
                  Not sure what you want to see: Examples can be endless 
                  I asked the following questions a while ago. The right answer is there but, it's important to understand what the SQL is actually doing to improve my knowledge.
                  If you can interpret it, it will be the end of my confusion...

                  PL/SQL LOOP or INSERT MONTH

                  For example in above link,

                  select months_between(to_date(end_date,'YYYYMMDD'),to_date(start_date,'YYYYMMDD'))+1 from payments where no_months_1 = 6
                  -- 7
                  But, if you used in multiset and connect by level, that 7 becomes 1,2,3,4,5,6,7 ? am I correct?

                  and then in below, column_value -1 makes that 1, a 0 to include start_month and then add_months one by one.
                  Am I interpreting correctly?
                  select 
                     add_months( to_date(t1.start_date, 'YYYYMMDD') , t2.column_value - 1) as ELIGIBLE_MONTH,
                  Edited by: 788729 on Aug 24, 2010 5:52 AM