10 Replies Latest reply: Mar 19, 2014 3:49 AM by AlexAA RSS

    Save shuttle item

    PatrickL

      Hello,

       

       

      I'm trying to save the content of a shuttle item. I´m using a "Save"-Button which triggers a Process (PL/SQL anonymous block).

      The content of the shuttle item shall be stored into a table.

      Filling the shuttle item with a computation works. Storing new items that have been draged from the left side to the right side works as well.

      Unfortunately I´m not able to delete an existing item of the right side.

      If I remove an item from the right side to the left side and click the "Save"-Button, I got the error "ORA-01722: invalid number".

      I´m using the following PL/SQL block:

       

       

      DECLARE
        v_count        NUMBER  := 0;
        v_shuttle_ids  VARCHAR2(1000);
        v_shuttle      APEX_APPLICATION_GLOBAL.VC_ARR2;
      BEGIN
        v_shuttle := APEX_UTIL.STRING_TO_TABLE(:P47_PFLANZLICHE_HERKUNFT);
      
        FOR i in 1 .. v_shuttle.count LOOP
          SELECT COUNT(*) INTO v_count FROM rjm.material_objekte WHERE objekte_id = :P47_OBJEKTE_ID AND material_id = v_shuttle(i);
      
          IF v_count = 0 THEN
            INSERT INTO rjm.material_objekte (objekte_id, material_id) VALUES (:P47_OBJEKTE_ID, v_shuttle(i));
          END IF;
      
          v_shuttle_ids := v_shuttle_ids || v_shuttle(i) || ',';
        END LOOP;
         
        v_shuttle_ids := SUBSTR(v_shuttle_ids, 0, LENGTH(v_shuttle_ids) - 1);
      
        DELETE FROM rjm.material_objekte WHERE objekte_id = :P47_OBJEKTE_ID AND material_id NOT IN (v_shuttle_ids);
      END;
      

       

       

      The Delete-Statement fails, because the datatypes are not compatible. The variable v_shuttle_ids has the datatype VARCHAR2 and the database column material_id has the datatype NUMBER.

      Has anybody a solution for the issue (for example casting the datatypes) or has a better way of deleting existing items in the shuttle item?

       

       

      Thank you in advance,

      Patrick

        • 1. Re: Save shuttle item
          vdsadhi

          Hi Patrick,

           

          Try to use to_number function when concatenating and storing the list items into a variable  v_shuttle_ids.

           

          Thanks,

          Lakshmi

          • 2. Re: Save shuttle item
            Tom Petrus

            The reason is your usage of v_shuttle_ids in an IN list. This is wrong. IN operates on a set of values, and not on a string, which is basically what you're passing it, eg: DELETE FROM rjm.material_objekte WHERE objekte_id = :P47_OBJEKTE_ID AND material_id NOT IN ('1,2,5,8,9'); 

             

            While it should be something like

            DELETE FROM rjm.material_objekte WHERE objekte_id = :P47_OBJEKTE_ID AND material_id NOT IN (1,2,5,8,9); 


            To remedy this you will need to split your string into rows, a technique you can find plenty of info about:

            plsql - SQL - Splitting a string into a new row - Stack Overflow

            http://stackoverflow.com/questions/18770581/oracle-split-multiple-comma-separated-values-in-oracle-table-to-multiple-rows

            http://stackoverflow.com/questions/14328621/oracle-10g-splitting-string-into-multiple-rows

             

            And many, many more on the web.

            • 3. Re: Save shuttle item
              PatrickL

              Hi Lakshmi,

               

              thank you for your reply.

              If I try the command

              v_shuttle_ids := TO_NUMBER(v_shuttle_ids) || TO_NUMBER(v_shuttle(i)) || ',';
              

              I get the following error:

              ORA-06502: PL/SQL: numeric or value error: character to number conversion error


              If I try the command

              v_shuttle_ids := v_shuttle_ids || TO_NUMBER(v_shuttle(i)) || ',';
              

              I still get the error:

              ORA-01722: invalid number

               

              Best Regards,

              Patrick

              • 4. Re: Save shuttle item
                PatrickL

                Hi Tom,

                 

                thank you for your reply.

                I will check the links.

                 

                Best,

                Patrick

                • 5. Re: Save shuttle item
                  PatrickL

                  Hi Tom,

                   

                  I tried to use a collection ( CREATE TYPE NUMBER_TT AS TABLE OF NUMBER; ) and modified the code like this:

                   

                  DECLARE
                    v_count        NUMBER  := 0;
                    v_shuttle_ids  NUMBER_TT;
                    v_shuttle      APEX_APPLICATION_GLOBAL.VC_ARR2;
                  BEGIN
                    v_shuttle_ids := NUMBER_TT();
                    v_shuttle := APEX_UTIL.STRING_TO_TABLE(:P47_PFLANZLICHE_HERKUNFT);
                    FOR i IN 1 .. v_shuttle.count LOOP
                      SELECT COUNT(*) INTO v_count FROM rjm.material_objekte WHERE objekte_id = :P47_OBJEKTE_ID AND material_id = v_shuttle(i);
                  
                  
                      IF v_count = 0 THEN
                        INSERT INTO rjm.material_objekte (objekte_id, material_id) VALUES (:P47_OBJEKTE_ID, v_shuttle(i));
                      END IF;
                  
                  
                      v_shuttle_ids.extend(v_shuttle(i));
                    END LOOP;
                  
                    DELETE FROM rjm.material_objekte WHERE objekte_id = :P47_OBJEKTE_ID AND material_id NOT IN (SELECT regexp_substr(column_value,'[^,]+',1,level) FROM TABLE(v_shuttle_ids) connect by level <= length(regexp_replace(column_value,'[^,]+')) + 1);
                  
                  
                  END;
                  
                  

                   

                  Now I don´t get an error, but the affected rows are not deleted.

                  I´m not sure, if I applied the delete statement in the right way.

                  Do you have any advice?

                   

                  Thank you,

                  Patrick

                  • 6. Re: Save shuttle item
                    vdsadhi

                    Hi Patrick,

                     

                    I thought to_number will work but i didn't try it.

                     

                    I hope you want to delete all the values in shuttle, then you can use the delete statement as given below, no need to concatenate the shuttle values,

                     

                    DELETE FROM rjm.material_objekte WHERE objekte_id = :P47_OBJEKTE_ID AND INSTR(':'||:P47_PFLANZLICHE_HERKUNFT||':',':'||material_id||':')>0

                     

                     

                    Thanks,

                    Lakshmi

                    • 7. Re: Save shuttle item
                      AlexAA

                      As i understand problem, your code must look like this:

                      DECLARE
                        v_count        NUMBER  := 0;
                        v_shuttle_ids  NUMBER_TT;
                        v_shuttle      APEX_APPLICATION_GLOBAL.VC_ARR2;
                      BEGIN
                        v_shuttle_ids := NUMBER_TT();
                        v_shuttle := APEX_UTIL.STRING_TO_TABLE(:P47_PFLANZLICHE_HERKUNFT);
                        FOR i IN 1 .. v_shuttle.count LOOP
                          SELECT COUNT(*) INTO v_count FROM rjm.material_objekte WHERE objekte_id = :P47_OBJEKTE_ID AND material_id = v_shuttle(i);
                          IF v_count = 0 THEN
                            INSERT INTO rjm.material_objekte (objekte_id, material_id) VALUES (:P47_OBJEKTE_ID, v_shuttle(i));
                          END IF;
                          v_shuttle_ids.extend();
                          v_shuttle_ids(v_shuttle_ids.last) := v_shuttle(i);
                        END LOOP;
                        DELETE FROM rjm.material_objekte WHERE objekte_id = :P47_OBJEKTE_ID AND material_id NOT IN (SELECT column_value FROM TABLE(v_shuttle_ids));
                      END;
                      
                      
                      • 8. Re: Save shuttle item
                        PatrickL

                        Hi Lakshmi,

                        unfortunately I don´t have to delete all the values. Just the values that are draged from the right side to the left side. So this can be one value or all values.

                        Thanks,

                        Patrick

                        • 9. Re: Save shuttle item
                          PatrickL

                          Hi AlexAA,

                          that was the solution! Thank you very much!

                           

                          
                              v_shuttle_ids.extend();  
                              v_shuttle_ids(v_shuttle_ids.last) := v_shuttle(i);  
                          
                          

                           

                          I´m working with collections the first time. After the extend, I did not go to the last position of the collection.

                          I think that I overwritted my collection each time.

                           

                          Thanks,

                          Patrick

                          • 10. Re: Save shuttle item
                            AlexAA

                            I glad all working. More about collection you can read in official documentation PL/SQL Collections and Records

                            Using [collection variable].extend(3) you appends 3 elements to collection.