Solved

how to create oracle dynamic object?

Posted on 2009-05-12
8
448 Views
Last Modified: 2013-12-18
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
Comment
Question by:GouthamAnand
  • 5
  • 3
8 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 24366296
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
 

Author Comment

by:GouthamAnand
ID: 24366570
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
 

Author Comment

by:GouthamAnand
ID: 24366772
How Can I select values from the object table type?
0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 500 total points
ID: 24367607
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:GouthamAnand
ID: 24368209
I am getting this error.
PLS-00386: type mismatch found at 'L_WINBACK_OBJ' between FETCH cursor and INTO variables
0
 

Author Comment

by:GouthamAnand
ID: 24368295
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
 

Author Comment

by:GouthamAnand
ID: 24368420
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 24374579
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
oracle query help 29 77
sql for Oracle views 8 48
passing parameters to sql script oracle 4 27
Use of Exception to end a Loop 3 18
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now