6 Replies Latest reply on Apr 10, 2014 8:18 PM by Doolius

    Update records with text area

    Doolius

      APEX: 4.0.2

      THEME: SAND

      SERVER: Oracle-Application-Server-10g/10.1.3.1.0 Oracle-HTTP-Server

       

      I'm trying to let my user paste values into a text area, click a button and then update certian fields for that record.

       

      I've created an example with more comments on the page:

      workspace: stevendooley34

      username: dev01

      password: dev01

      application: Forum Examples

      page: update example

       

      Keep in mind: The apex version, theme, and server are different from where I have created the example. The above version, theme, and server are the stats for my environment at work.

       

      Thanks,

      Steven

       

      --edits

       

      Or if editing the below code would be easier I can go that route.

       

      update oef_itv_ndn

      set cancelled_cd = :MMR_CANCELLED_CD,

      closed_date = :MMR_CLOSED_DATE,

      admin_remarks = :MMR_ADMIN_REMARKS

      where abc = '12345';

       

      I've created a Dynamic Action with the above code that works correctly.

      but instead of " where abc = '12345' " I need it to be " where abc in :SEARCH_INPUT "

       

      :SEARCH_INPUT is the textarea that the users can paste values into in this format:

       

      12345

      67890

      98765

       

      so when it gets to the "where" clause I need it to update all records that are in the textarea.

       

      Message was edited by: StevenD609

        • 1. Re: Update records with text area
          Mike Kutz

          This is a "varying IN list" problem.

          The Tom Kyte Blog: Varying in lists...

           

          The input in the blog uses commas to separate the values.

          You want to use the new line character to separate the values.  chr(13)

          You'll also want to remove the carriage return character.  chr(10)

          And.. possibly convert the results to a number using the to_number() function.

           

          MK

          • 2. Re: Update records with text area
            Doolius

            The report that I use on the page goes with the "varying in lists":

            with data
               as
               ( 
               select    trim( substr (txt,
               instr (txt, chr(10), 1, level  ) + 1,
               instr (txt, chr(10), 1, level+1)
               - instr (txt, chr(10), 1, level) -1 ) )
               as token
               from (select chr(10)||:SEARCH_INPUT||chr(10) txt
               from dual)
               connect by level <=
               length(:SEARCH_INPUT)-length(replace(:SEARCH_INPUT,chr(10),''))+1
               )
            Select
            ..........
            From .........
            where (:SEARCH_TYPE='ABC' AND abc in (select upper( replace( token, chr(13), '') ) from data))
            

             

            I'm just not sure how to implement that with the UPDATE statement.

             

            If I had to guess I think I would have to update the "where" statement, but I don't know for sure.

             

            Steven

            • 3. Re: Update records with text area
              Mike Kutz

              doh!  I should have seen that problem.

              I saw a similar question somewhere else.  with statment + update multiple columns


              short answer:  Change UPDATE to MERGE.

               

              MK

              1 person found this helpful
              • 4. Re: Update records with text area
                Doolius

                I'm getting the error "Table or view does not exist Line 3 column 1 statement ignored"

                 

                merge into oef_itv_ndn A
                using (with data
                   as
                   ( 
                   select    trim( substr (txt,
                   instr (txt, chr(10), 1, level  ) + 1,
                   instr (txt, chr(10), 1, level+1)
                   - instr (txt, chr(10), 1, level) -1 ) )
                   as token
                   from (select chr(10)||:SEARCH_INPUT||chr(10) txt
                   from dual)
                   connect by level <=
                   length(:SEARCH_INPUT)-length(replace(:SEARCH_INPUT,chr(10),''))+1
                   )
                   select pcfn
                   from oef_itv_ndn) X
                ON (A.pcfn = X.PCFN)
                WHEN MATCHED THEN UPDATE
                set cancelled_cd = :MMR_CANCELLED_CD,
                closed_date = :MMR_CLOSED_DATE,
                admin_remarks = :MMR_ADMIN_REMARKS
                where (:SEARCH_TYPE = 'pcfn' and pcfn in (select upper(replace(token, chr(13),''))from data));
                

                 

                What am I missing?

                • 5. Re: Update records with text area
                  Mike Kutz

                  The "WHERE" clause is in the wrong position.

                  It should be part of the "USING ( ) " clause...

                  (the pseudo table 'data' is only defined in the scope of that sub-query)

                   

                  beyond that, it looks good to me.

                  If you're still having troubles, you may want to move this to the "SQL and PL/SQL" forum.

                   

                  MK

                  • 6. Re: Update records with text area
                    Doolius

                    Ahh duh! thanks Mike!

                    Correct SQL:

                    merge into oef_itv_ndn A
                    using (with data
                       as
                       ( 
                       select    trim( substr (txt,
                       instr (txt, chr(10), 1, level  ) + 1,
                       instr (txt, chr(10), 1, level+1)
                       - instr (txt, chr(10), 1, level) -1 ) )
                       as token
                       from (select chr(10)||:SEARCH_INPUT||chr(10) txt
                       from dual)
                       connect by level <=
                       length(:SEARCH_INPUT)-length(replace(:SEARCH_INPUT,chr(10),''))+1
                       )
                       select pcfn
                       from oef_itv_ndn
                       where (:SEARCH_TYPE = 'pcfn' and pcfn in (select upper(replace(token, chr(13),''))from data))) X
                    ON (A.pcfn = X.PCFN)
                    WHEN MATCHED THEN UPDATE
                    set cancelled_cd = :mmr_cancelled_cd,
                    closed_date = :mmr_closed_date,
                    admin_remarks = :mmr_admin_remarks;
                    

                     

                     

                     

                    Steven