Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 481
  • Last Modified:

how to create oracle dynamic object?

I want to pass dates dynamicatlly(hard coded now)  in the below query and I want to use the output of this query in another query's FROM clause as table object?
How can I achieve that can you please let me know.
I am usgin Oracle 10g.
SELECT   1 ord_er,
            'Opportunites' Market,
            NVL (SUM (DECODE (ldc_Code, 'COH', 1)), 0) coh,
            NVL (SUM (DECODE (ldc_Code, 'DEO', 1)), 0) deo,
            NVL (SUM (DECODE (ldc_Code, 'DKO', 1)), 0) dko,
            NVL (SUM (DECODE (ldc_Code, 'NFG', 1)), 0) nfg,
            NVL (SUM (DECODE (ldc_Code, 'NGY', 1)), 0) ngy,
            NVL (SUM (DECODE (ldc_Code, 'NIP', 1)), 0) nip,
            NVL (SUM (DECODE (ldc_Code, 'VDO', 1)), 0) vdo,
            NVL (SUM (DECODE (ldc_code, 'XXX', 0, 1)), 0) Total
     FROM   ecare_winback_Tb
    WHERE   TRUNC (created_Date) BETWEEN TO_DATE ('27-APR-09', 'DD-MON-YY')
                                         - 7
                                     AND  TO_DATE ('27-APR-09', 'DD-MON-YY')
                                          - 1
   UNION
     SELECT   DECODE (wb_bucket,
                      'OPPO',
                      6,
                      'WINB',
                      2,
                      'LOST',
                      3,
                      'DEAD',
                      4)
                 ord_er,
              wb_bucket Market,
              NVL (SUM (DECODE (ldc_Code, 'COH', 1)), 0) coh,
              NVL (SUM (DECODE (ldc_Code, 'DEO', 1)), 0) deo,
              NVL (SUM (DECODE (ldc_Code, 'DKO', 1)), 0) dko,
              NVL (SUM (DECODE (ldc_Code, 'NFG', 1)), 0) nfg,
              NVL (SUM (DECODE (ldc_Code, 'NGY', 1)), 0) ngy,
              NVL (SUM (DECODE (ldc_Code, 'NIP', 1)), 0) nip,
              NVL (SUM (DECODE (ldc_Code, 'VDO', 1)), 0) vdo,
              NVL (SUM (DECODE (ldc_code, 'XXX', 0, 1)), 0) Total
       FROM   ecare_winback_Tb
      WHERE   TRUNC (created_Date) BETWEEN TO_DATE ('27-APR-09', 'DD-MON-YY')
                                           - 7
                                       AND  TO_DATE ('27-APR-09', 'DD-MON-YY')
                                            - 1
   GROUP BY   wb_bucket
   UNION
   SELECT   5 ord_er,
            'Completed' Market,
            NVL (SUM (DECODE (ldc_Code, 'COH', 1)), 0) coh,
            NVL (SUM (DECODE (ldc_Code, 'DEO', 1)), 0) deo,
            NVL (SUM (DECODE (ldc_Code, 'DKO', 1)), 0) dko,
            NVL (SUM (DECODE (ldc_Code, 'NFG', 1)), 0) nfg,
            NVL (SUM (DECODE (ldc_Code, 'NGY', 1)), 0) ngy,
            NVL (SUM (DECODE (ldc_Code, 'NIP', 1)), 0) nip,
            NVL (SUM (DECODE (ldc_Code, 'VDO', 1)), 0) vdo,
            NVL (SUM (DECODE (ldc_code, 'XXX', 0, 1)), 0) Total
     FROM   ecare_winback_Tb
    WHERE   TRUNC (created_Date) BETWEEN TO_DATE ('27-APR-09', 'DD-MON-YY')
                                         - 7
                                     AND  TO_DATE ('27-APR-09', 'DD-MON-YY')
                                          - 1
            AND wb_bucket != 'OPPO'
   ORDER BY   1;

Open in new window

0
GouthamAnand
Asked:
GouthamAnand
  • 5
  • 3
1 Solution
 
MikeOM_DBACommented:
It all depends on how you are executing this query.
There are many different options, but one of the easiest is to create a procedure which accepts the date parameters and writes the query results to a temporary table which then is accessed by the other query.
0
 
GouthamAnandAuthor Commented:
here temporary table is type object? I am able to create as

create or replace PROCEDURE ECARE.win_TEST_PRC(p_date1 in varchar2, p_date2 in varchar2)
AS
CURSOR c1
   IS    
SELECT   1 ord_er,
            'Opportunites' Market,
            NVL (SUM (DECODE (ldc_Code, 'COH', 1)), 0) coh,
            NVL (SUM (DECODE (ldc_Code, 'DEO', 1)), 0) deo,
            NVL (SUM (DECODE (ldc_Code, 'DKO', 1)), 0) dko,
            NVL (SUM (DECODE (ldc_Code, 'NFG', 1)), 0) nfg,
            NVL (SUM (DECODE (ldc_Code, 'NGY', 1)), 0) ngy,
            NVL (SUM (DECODE (ldc_Code, 'NIP', 1)), 0) nip,
            NVL (SUM (DECODE (ldc_Code, 'VDO', 1)), 0) vdo,
            NVL (SUM (DECODE (ldc_code, 'XXX', 0, 1)), 0) Total
     FROM   ecare_winback_Tb
    WHERE   TRUNC (created_Date) BETWEEN TO_DATE (p_date1, 'DD-MON-YY')
                                         - 7
                                     AND  TO_DATE (p_date2, 'DD-MON-YY')
                                          - 1
   UNION
     SELECT   DECODE (wb_bucket,
                      'OPPO',
                      6,
                      'WINB',
                      2,
                      'LOST',
                      3,
                      'DEAD',
                      4)
                 ord_er,
              wb_bucket Market,
              NVL (SUM (DECODE (ldc_Code, 'COH', 1)), 0) coh,
              NVL (SUM (DECODE (ldc_Code, 'DEO', 1)), 0) deo,
              NVL (SUM (DECODE (ldc_Code, 'DKO', 1)), 0) dko,
              NVL (SUM (DECODE (ldc_Code, 'NFG', 1)), 0) nfg,
              NVL (SUM (DECODE (ldc_Code, 'NGY', 1)), 0) ngy,
              NVL (SUM (DECODE (ldc_Code, 'NIP', 1)), 0) nip,
              NVL (SUM (DECODE (ldc_Code, 'VDO', 1)), 0) vdo,
              NVL (SUM (DECODE (ldc_code, 'XXX', 0, 1)), 0) Total
       FROM   ecare_winback_Tb
      WHERE   TRUNC (created_Date) BETWEEN TO_DATE ('27-APR-09', 'DD-MON-YY')
                                           - 7
                                       AND  TO_DATE ('27-APR-09', 'DD-MON-YY')
                                            - 1
   GROUP BY   wb_bucket
   UNION
   SELECT   5 ord_er,
            'Completed' Market,
            NVL (SUM (DECODE (ldc_Code, 'COH', 1)), 0) coh,
            NVL (SUM (DECODE (ldc_Code, 'DEO', 1)), 0) deo,
            NVL (SUM (DECODE (ldc_Code, 'DKO', 1)), 0) dko,
            NVL (SUM (DECODE (ldc_Code, 'NFG', 1)), 0) nfg,
            NVL (SUM (DECODE (ldc_Code, 'NGY', 1)), 0) ngy,
            NVL (SUM (DECODE (ldc_Code, 'NIP', 1)), 0) nip,
            NVL (SUM (DECODE (ldc_Code, 'VDO', 1)), 0) vdo,
            NVL (SUM (DECODE (ldc_code, 'XXX', 0, 1)), 0) Total
     FROM   ecare_winback_Tb
    WHERE   TRUNC (created_Date) BETWEEN TO_DATE ('27-APR-09', 'DD-MON-YY')
                                         - 7
                                     AND  TO_DATE ('27-APR-09', 'DD-MON-YY')
                                          - 1
            AND wb_bucket != 'OPPO'
   ORDER BY   1;  
   
   
 
   TYPE winback_obj IS
      TABLE OF  c1%ROWTYPE INDEX BY PLS_INTEGER;
     
       l_winback_obj winback_obj;

     
BEGIN
OPEN c1;
   LOOP
      FETCH c1
         BULK COLLECT INTO l_winback_obj;
    END LOOP;  

close c1;

     
END win_TEST_PRC;
/

I want to pass this l_winback_obj to another query as

select * from TABLE(l_winback_obj) --SOMETHING LIKE THIS

Can you please let me know How can I do that?
0
 
GouthamAnandAuthor Commented:
How Can I select values from the object table type?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
MikeOM_DBACommented:
Try something like this:
 

/* Formatted on 2009/05/12 14:15 (Formatter Plus v4.8.7) */
CREATE OR REPLACE TYPE testobjtype AS OBJECT (
   ord_er   NUMBER (9),
   market   VARCHAR2 (32),
   coh      NUMBER (13),
   deo      NUMBER (13),
   dko      NUMBER (13),
   nfg      NUMBER (13),
   ngy      NUMBER (13),
   nip      NUMBER (13),
   vdo      NUMBER (13),
   total    NUMBER (33)
)
/
 
CREATE OR REPLACE TYPE testtabletype AS TABLE OF testobjtype
/
 
CREATE OR REPLACE FUNCTION ecare.win_test_prc (
   p_date1   IN   VARCHAR2,
   p_date2   IN   VARCHAR2
)
   RETURN testtabletype
AS
   CURSOR c1
   IS
      SELECT   1 ord_er, 'Opportunites' market,
               NVL (SUM (DECODE (ldc_code, 'COH', 1)), 0) coh,
               NVL (SUM (DECODE (ldc_code, 'DEO', 1)), 0) deo,
               NVL (SUM (DECODE (ldc_code, 'DKO', 1)), 0) dko,
               NVL (SUM (DECODE (ldc_code, 'NFG', 1)), 0) nfg,
               NVL (SUM (DECODE (ldc_code, 'NGY', 1)), 0) ngy,
               NVL (SUM (DECODE (ldc_code, 'NIP', 1)), 0) nip,
               NVL (SUM (DECODE (ldc_code, 'VDO', 1)), 0) vdo,
               NVL (SUM (DECODE (ldc_code, 'XXX', 0, 1)), 0) total
          FROM ecare_winback_tb
         WHERE TRUNC (created_date) BETWEEN TO_DATE (p_date1, 'DD-MON-YY')
                                            - 7
                                        AND TO_DATE (p_date2, 'DD-MON-YY')
                                            - 1
      UNION
      SELECT   DECODE (wb_bucket,
                       'OPPO', 6,
                       'WINB', 2,
                       'LOST', 3,
                       'DEAD', 4
                      ) ord_er,
               wb_bucket market,
               NVL (SUM (DECODE (ldc_code, 'COH', 1)), 0) coh,
               NVL (SUM (DECODE (ldc_code, 'DEO', 1)), 0) deo,
               NVL (SUM (DECODE (ldc_code, 'DKO', 1)), 0) dko,
               NVL (SUM (DECODE (ldc_code, 'NFG', 1)), 0) nfg,
               NVL (SUM (DECODE (ldc_code, 'NGY', 1)), 0) ngy,
               NVL (SUM (DECODE (ldc_code, 'NIP', 1)), 0) nip,
               NVL (SUM (DECODE (ldc_code, 'VDO', 1)), 0) vdo,
               NVL (SUM (DECODE (ldc_code, 'XXX', 0, 1)), 0) total
          FROM ecare_winback_tb
         WHERE TRUNC (created_date) BETWEEN TO_DATE (p_date1, 'DD-MON-YY')
                                            - 7
                                        AND TO_DATE (p_date2, 'DD-MON-YY')
                                            - 1
      GROUP BY wb_bucket
      UNION
      SELECT   5 ord_er, 'Completed' market,
               NVL (SUM (DECODE (ldc_code, 'COH', 1)), 0) coh,
               NVL (SUM (DECODE (ldc_code, 'DEO', 1)), 0) deo,
               NVL (SUM (DECODE (ldc_code, 'DKO', 1)), 0) dko,
               NVL (SUM (DECODE (ldc_code, 'NFG', 1)), 0) nfg,
               NVL (SUM (DECODE (ldc_code, 'NGY', 1)), 0) ngy,
               NVL (SUM (DECODE (ldc_code, 'NIP', 1)), 0) nip,
               NVL (SUM (DECODE (ldc_code, 'VDO', 1)), 0) vdo,
               NVL (SUM (DECODE (ldc_code, 'XXX', 0, 1)), 0) total
          FROM ecare_winback_tb
         WHERE TRUNC (created_date) BETWEEN TO_DATE (p_date1, 'DD-MON-YY')
                                            - 7
                                        AND TO_DATE (p_date2, 'DD-MON-YY')
                                            - 1
           AND wb_bucket != 'OPPO'
      ORDER BY 1;
 
   l_winback_obj   testtabletype := testtabletype ();
BEGIN
   OPEN c1;
 
   FETCH c1
     BULK COLLECT INTO l_winback_obj;
 
   CLOSE c1;
END win_test_prc;
/
-- -------------------------------------------
SELECT *
  FROM THE (SELECT CAST (ecare.win_test_prc ('27-APR-09', '27-APR-09') AS mytabletype)
              FROM DUAL) a
/

Open in new window

0
 
GouthamAnandAuthor Commented:
I am getting this error.
PLS-00386: type mismatch found at 'L_WINBACK_OBJ' between FETCH cursor and INTO variables
0
 
GouthamAnandAuthor Commented:
Also I want to use like

select * from (select * from XXXX--here I want to use the object l_winback_obj
where XXX --- here I want to use the one column of the object l_winback_obj
 = 'Opportunites')
0
 
GouthamAnandAuthor Commented:
Actually data is like
ORD_ER      MARKET      COH      DEO      DKO      NFG      NGY      NIP      VDO      TOTAL
1      Opportunites      24      652      0      18      55      19      231      999
2      WINB      2      52      0      2      5      1      18      80

I do not know why I am getting
PLS-00386: type mismatch found at 'L_WINBACK_OBJ' between FETCH cursor and INTO variables

I use pl/sql table types then I can not give the return table type for the function, I think. So can we use the database table types as you given in the example?
0
 
MikeOM_DBACommented:
OK, BULK-COLLECT is not working, try this:
 

CREATE OR REPLACE TYPE testobjtype AS OBJECT (
   ord_er   NUMBER (9),
   market   VARCHAR2 (32),
   coh      NUMBER (13),
   deo      NUMBER (13),
   dko      NUMBER (13),
   nfg      NUMBER (13),
   ngy      NUMBER (13),
   nip      NUMBER (13),
   vdo      NUMBER (13),
   total    NUMBER (33)
)
/
 
CREATE OR REPLACE TYPE testtabletype AS TABLE OF testobjtype
/
 
CREATE OR REPLACE FUNCTION ecare.win_test_prc (
   p_date1   IN   VARCHAR2,
   p_date2   IN   VARCHAR2
)
   RETURN testtabletype
AS
   CURSOR c1
   IS
      SELECT   1 ord_er, 'Opportunites' market,
               NVL (SUM (DECODE (ldc_code, 'COH', 1)), 0) coh,
               NVL (SUM (DECODE (ldc_code, 'DEO', 1)), 0) deo,
               NVL (SUM (DECODE (ldc_code, 'DKO', 1)), 0) dko,
               NVL (SUM (DECODE (ldc_code, 'NFG', 1)), 0) nfg,
               NVL (SUM (DECODE (ldc_code, 'NGY', 1)), 0) ngy,
               NVL (SUM (DECODE (ldc_code, 'NIP', 1)), 0) nip,
               NVL (SUM (DECODE (ldc_code, 'VDO', 1)), 0) vdo,
               NVL (SUM (DECODE (ldc_code, 'XXX', 0, 1)), 0) total
          FROM ecare_winback_tb
         WHERE TRUNC (created_date) BETWEEN TO_DATE (p_date1, 'DD-MON-YY')
                                            - 7
                                        AND TO_DATE (p_date2, 'DD-MON-YY')
                                            - 1
      UNION
      SELECT   DECODE (wb_bucket,
                       'OPPO', 6,
                       'WINB', 2,
                       'LOST', 3,
                       'DEAD', 4
                      ) ord_er,
               wb_bucket market,
               NVL (SUM (DECODE (ldc_code, 'COH', 1)), 0) coh,
               NVL (SUM (DECODE (ldc_code, 'DEO', 1)), 0) deo,
               NVL (SUM (DECODE (ldc_code, 'DKO', 1)), 0) dko,
               NVL (SUM (DECODE (ldc_code, 'NFG', 1)), 0) nfg,
               NVL (SUM (DECODE (ldc_code, 'NGY', 1)), 0) ngy,
               NVL (SUM (DECODE (ldc_code, 'NIP', 1)), 0) nip,
               NVL (SUM (DECODE (ldc_code, 'VDO', 1)), 0) vdo,
               NVL (SUM (DECODE (ldc_code, 'XXX', 0, 1)), 0) total
          FROM ecare_winback_tb
         WHERE TRUNC (created_date) BETWEEN   TO_DATE ('27-APR-09',
                                                       'DD-MON-YY'
                                                      )
                                            - 7
                                        AND   TO_DATE ('27-APR-09',
                                                       'DD-MON-YY'
                                                      )
                                            - 1
      GROUP BY wb_bucket
      UNION
      SELECT   5 ord_er, 'Completed' market,
               NVL (SUM (DECODE (ldc_code, 'COH', 1)), 0) coh,
               NVL (SUM (DECODE (ldc_code, 'DEO', 1)), 0) deo,
               NVL (SUM (DECODE (ldc_code, 'DKO', 1)), 0) dko,
               NVL (SUM (DECODE (ldc_code, 'NFG', 1)), 0) nfg,
               NVL (SUM (DECODE (ldc_code, 'NGY', 1)), 0) ngy,
               NVL (SUM (DECODE (ldc_code, 'NIP', 1)), 0) nip,
               NVL (SUM (DECODE (ldc_code, 'VDO', 1)), 0) vdo,
               NVL (SUM (DECODE (ldc_code, 'XXX', 0, 1)), 0) total
          FROM ecare_winback_tb
         WHERE TRUNC (created_date) BETWEEN   TO_DATE ('27-APR-09',
                                                       'DD-MON-YY'
                                                      )
                                            - 7
                                        AND   TO_DATE ('27-APR-09',
                                                       'DD-MON-YY'
                                                      )
                                            - 1
           AND wb_bucket != 'OPPO'
      ORDER BY 1;
 
   l_winback_obj   testtabletype := testtabletype ();
BEGIN
--OPEN c1;
--FETCH c1
--    BULK COLLECT INTO l_winback_obj;
--close c1;
   FOR x IN c1
   LOOP
      l_winback_obj.EXTEND;
      l_winback_obj (l_winback_obj.COUNT) :=
           testobjtype (x.ord_er,
                        x.market,
                        x.coh,
                        x.deo,
                        x.dko,
                        x.nfg,
                        x.ngy,
                        x.nip,
                        x.vdo,
                        x.total
                       );
   END LOOP;
 
   RETURN l_winback_obj;
END win_test_prc;
/
-- -------------------------------------------
SELECT *
  FROM THE (SELECT CAST (ecare.win_test_prc ('27-APR-09', '27-APR-09') AS mytabletype)
              FROM DUAL) a
/

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now