2 Replies Latest reply: Jun 19, 2013 11:49 AM by Etbin RSS

    Temporary table within a package

    Sire_Hugolin

      I'm not sure this is the best way to achieve it, but I'm trying to use a temporary table within my package, but I failed!

       

      In my package, my procedure do receive 5 different phone numbers (vTel1 to vTel5) and I need to order them, using data from a table. Also, if 2 of them are the same, I need only the one with the highest rank.

       

      Let say my TelOrder table look likes:

      ReasonTel1Tel2Tel3Tel4Tel5
      Reason123154
      Reason212null34

       

      And I receive those variable

      vTel1='5141111111'

      vTel2=null

      vTel3='5143333333'

      vTel4='5141111111'

      vTel5='5145555555'

      vReason='Reason1'

       

      Using the Reason1, I need to be able to get the result looking like:

      RowNumPhoneOrder
      151433333331
      251411111112
      351455555554

       

      And I need this code to be apart from the procedure, cause many procedures will use the same code, and I don't want to abuse the ctrl+c, ctrl+v at each update.

       

      I've come close by using something like:

      EXECUTE IMMEDIATE '

           INSERT INTO Table

           SELECT Rownum as RN, Ordre, contact_info, Contact_info_type

           FROM

             (SELECT a.contact_info, a.ordre, contact_info_type FROM

               (SELECT contact_info,min(ordre) as Ordre FROM

                 (SELECT Tel1 as Ordre, ''' || vTel1 || ''' as contact_info, 1 as contact_info_type FROM TelOrder WHERE Reason=''' || vReason || '''

                 UNION ALL

                 SELECT Tel2 as Ordre, ''' || vTel2 || ''' as contact_info, 2 as contact_info_type FROM TelOrder WHERE Reason=''' || vReason || '''

                 UNION ALL

                 SELECT Tel3 as Ordre, ''' || vTel3 || ''' as contact_info, 4 as contact_info_type FROM TelOrder WHERE Reason=''' || vReason || '''

                 UNION ALL

                 SELECT Tel4 as Ordre, ''' || vTel4 || ''' as contact_info, 4 as contact_info_type FROM TelOrder WHERE Reason=''' || vReason || '''

                 UNION ALL

                 SELECT Tel5 as Ordre, ''' || vTel5 || ''' as contact_info, 1 as contact_info_type FROM TelOrder WHERE Reason=''' || vReason || '''

                 )

               WHERE Ordre is not null and contact_info is not null

               GROUP BY contact_info

               ) a

             JOIN

               (SELECT Tel1 as Ordre, ''' || vTel1 || ''' as contact_info, 1 as contact_info_type FROM TelOrder WHERE Reason=''' || vReason || '''

               UNION ALL

               SELECT Tel2 as Ordre, ''' || vTel2 || ''' as contact_info, 2 as contact_info_type FROM TelOrder WHERE Reason=''' || vReason || '''

               UNION ALL

               SELECT Tel3 as Ordre, ''' || vTel3 || ''' as contact_info, 4 as contact_info_type FROM TelOrder WHERE Reason=''' || vReason || '''

               UNION ALL

               SELECT Tel4 as Ordre, ''' || vTel4 || ''' as contact_info, 4 as contact_info_type FROM TelOrder WHERE Reason=''' || vReason || '''

               UNION ALL

               SELECT Tel5 as Ordre, ''' || vTel5 || ''' as contact_info, 1 as contact_info_type FROM TelOrder WHERE Reason=''' || vReason || '''

               ) b ON a.contact_info=b.contact_info AND a.ordre=b.ordre

             ORDER BY a.Ordre

             )';

      But when I try to remove this code and send it into another procedure/function, I can't make it work.

       

      PLEASE HELP!!!!  

        • 1. Re: Temporary table within a package
          Marwim

          Hello,

          I'm not sure this is the best way to achieve it

          Surely not, because this is the forum for the tool Oracle Designer ;-) Your question should be asked in

          PL/SQL

          Before you post your question there I have to say that EXECUTE IMMEDIATE inside a procedure is rarely necessary. You can simply write an INSERT without EXECUTE IMMEDIATE.

          INSERT INTO TABLE -> "TABLE" is not a good name for a table. Using keywords or even reserved word for object names will sooner or later lead to bugs that are hard to find.

          But when I try to remove this code and send it into another procedure/function, I can't make it work.

          What does "not work mean". Please answer this questions for yourself and then post in the correct space.

           

          Regards

          Marcus

          • 2. Re: Temporary table within a package
            Etbin

            No Database to try it. Check at your own risk if this might work for you

            no version specified from your side, NOT TESTED from my side, so let's say we're even

             

            select row_number() over (order by the_order) "RowNum",

                   the_val "Phone",

                   the_order "Order"

              from (select v.the_val,t.the_order,

                           row_number() over (partition by v.the_val order by t.the_order) rn

                      from (select reason,the_order,the_phone

                              from (select reason,tel1,tel2,tel3,tel4,tel5

                                      from telorder

                                     where reason = :the_reason

                                   )

                            unpivot include nulls (the_order for the_phone in (tel1 as 'tel1',

                                                                               tel2 as 'tel2',

                                                                               tel3 as 'tel3',

                                                                               tel4 as 'tel4',

                                                                               tel5 as 'tel5'

                                                                              )

                                                  )

                           ) t,

                           (select 'tel1' the_var,:v_tel1 the_val from dual union all

                            select 'tel2' the_var,:v_tel2 the_val from dual union all

                            select 'tel3' the_var,:v_tel3 the_val from dual union all

                            select 'tel4' the_var,:v_tel4 the_val from dual union all

                            select 'tel5' the_var,:v_tel5 the_val from dual

                           ) v

                     where t.reason = :v_reason

                       and t.the_phone = v.the_var

                   )

            where rn = 1

               and the_val is not null

             

            Regards

             

            Etbin