7 Replies Latest reply on Feb 27, 2019 3:24 AM by benton

    Stop Duplicate Page Items

    benton

      Application Express 5.1.4.00.08 on a AWS

      Win 10

       

      Hello,

       

      I have ten page items named :P1_NAME_1 through :P1_NAME_10.

       

      These items are filled in by the user.

       

      I would like to set a validation on these page items to ensure that all page items have unique values (no duplicates) chosen by the user.

       

      Ben

        • 1. Re: Stop Duplicate Page Items
          Scott Wesley

          You could compare the results of these, ultimately replacing the names with a concatenated list of your items.

           

          select count(*), count(distinct column_value) from table(apex_string.split('Plank,Einsten,Curie',','));

          select count(*), count(distinct column_value) from table(apex_string.split('Plank,Curie,Curie',','));

           

          This would appear in a declarative validation in your page processing, returning error when count != count distinct.

          • 2. Re: Stop Duplicate Page Items
            benton

            Hello,

             

            I have attempted to form a statement in the Page Validation section of  Type = PL/SQL Expression, but it throws errors;

             

            Error processing validation.

             

            ORA-06550: line 1, column 29: PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: ( - + case mod new not null <an identifier> <a double-quoted delimited-identifier> <a bind variable> continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date <a string literal with character set specification> <a number> <a single-quoted SQL string> pipe <an alternatively-quoted string literal with character set

             

            Validation code I created based on example code;

             

            DECLARE

            v_cnt_a number;

            v_cnt_b number;

             

             

            BEGIN

             

             

            SELECT count(*)

               INTO v_cnt_a

               FROM table(apex_string.split(

            ':P9200_FBLK

            ,:P9200_FBLK_2

            ,:P9200_FBLK_3

            ,:P9200_FBLK_4

            ,:P9200_FBLK_5

            ,:P9200_FBLK_6

            ,:P9200_FBLK_7

            ,:P9200_FBLK_8

            ,:P9200_FBLK_9

            ,:P9200_FBLK_10'

            ,'

            ,'));

               

            SELECT count(distinct column_value)

               INTO v_cnt_a

               FROM table(apex_string.split('

            :P9200_FBLK

            ,:P9200_FBLK_2

            ,:P9200_FBLK_3

            ,:P9200_FBLK_4

            ,:P9200_FBLK_5

            ,:P9200_FBLK_6

            ,:P9200_FBLK_7

            ,:P9200_FBLK_8

            ,:P9200_FBLK_9

            ,:P9200_FBLK_10'

            ,'

            ,'));

             

             

            IF (v_cnt_a != v_cnt_b)

              THEN

               RETURN FALSE;

              ELSE

               RETURN TRUE;

              END IF;

             

             

            END;

             

            Ben

            • 3. Re: Stop Duplicate Page Items
              Scott Wesley

              You would need to use one of the PL/SQL Function ... attributes. You currently return a boolean, so ...

               

               

              And in your case, you could just do the one SQL

               

              SELECT count(*), count(distinct column_value)

                 INTO v_cnt_a, v_cnt_b

              ...

               

              And there has been some misinterpretation here. You want to compare actual values of these items?

               

              Another solution could be using multiset operators

              https://oracle-base.com/articles/8i/collections-8i

              Turn your values into a nested table, and check for uniqueness.

               

              Otherwise, you'll probably need to do this before the SQL. I can't recall if multiset operators available on the apex_t_varchar2 type. Something I'd play with if I had more time.

              apex_string.join(apex_t_varchar2(:P9200_FBLK,:P9200_FBLK_2,:P9200_FBLK_3),',')

              • 4. Re: Stop Duplicate Page Items
                benton
                And there has been some misinterpretation here. You want to compare actual values of these items?

                Yes

                 

                User chooses value from LOV, over multiple items, and it is the values from the chosen LOV's that I want to check to be unique.

                • 5. Re: Stop Duplicate Page Items
                  benton

                  Does anybody know how to get the following statement to accept the values of the bind variable (in bold)?

                   

                  DECLARE

                  v_cnt_a number;

                  v_cnt_b number;

                   

                   

                  BEGIN

                   

                  SELECT count(*)

                        , count(distinct column_value)

                     INTO v_cnt_a

                        , v_cnt_b

                     FROM table(apex_string.split(

                  ':P9200_FOREST_BLOCK

                  ,:P9200_FOREST_BLOCK_2

                  ,:P9200_FOREST_BLOCK_3

                  ,:P9200_FOREST_BLOCK_4

                  ,:P9200_FOREST_BLOCK_5

                  ,:P9200_FOREST_BLOCK_6

                  ,:P9200_FOREST_BLOCK_7

                  ,:P9200_FOREST_BLOCK_7

                  ,:P9200_FOREST_BLOCK_9

                  ,:P9200_FOREST_BLOCK_10'

                  ,'

                  ,')); 

                   

                  IF (v_cnt_a != v_cnt_b)

                    THEN

                     RETURN TRUE;

                    ELSE

                     RETURN FALSE;

                    END IF;

                   

                  END;

                  • 6. Re: Stop Duplicate Page Items
                    Scott Wesley

                    This concept worked for me in SQL Dev

                     

                    DECLARE
                    
                    v_cnt_a number;
                    v_cnt_b number;
                    
                      str varchar2(50);
                    BEGIN
                      str := apex_string.join(apex_t_varchar2('A','B','C'),',');
                    --  str := apex_string.join(apex_t_varchar2(:P15_B,:P15_C,:P15_A),',')
                    
                    SELECT count(*)
                          , count(distinct column_value)
                      INTO v_cnt_a
                        , v_cnt_b
                      FROM table(apex_string.split(str,','));
                    
                    IF (v_cnt_a != v_cnt_b)
                      THEN
                      dbms_output.put_line('T');
                      ELSE
                      dbms_output.put_line('F');
                      END IF;
                    
                    END;
                    /
                    
                    1 person found this helpful
                    • 7. Re: Stop Duplicate Page Items
                      benton

                      Thank You Scott for solving this problem for me.

                       

                      This is the end result;

                       

                      DECLARE 

                          v_cnt_a number; 

                          v_cnt_b number;

                          str varchar2(50);

                       

                      BEGIN

                       

                        IF :P9203_OPTIONS = 2 THEN

                          str := apex_string.join(apex_t_varchar2(:P9_FBLK,:P9_FBLK_2),',');   

                        ELSIF :P9203_OPTIONS = 3 THEN

                          str := apex_string.join(apex_t_varchar2(:P9_FBLK,:P9_FBLK_2,:P9_FBLK_3),','); 

                        ELSIF :P9203_OPTIONS = 4 THEN

                          str := apex_string.join(apex_t_varchar2(:P9_FBLK,:P9_FBLK_2,:P9_FBLK_3,:P9_FBLK_4),',');   

                        ELSIF :P9203_OPTIONS = 5 THEN

                          str := apex_string.join(apex_t_varchar2(:P9_FBLK,:P9_FBLK_2,:P9_FBLK_3,:P9_FBLK_4,:P9_FBLK_5),',');    

                        ELSIF :P9203_OPTIONS = 6 THEN

                          str := apex_string.join(apex_t_varchar2(:P9_FBLK,:P9_FBLK_2,:P9_FBLK_3,:P9_FBLK_4,:P9_FBLK_5,:P9_FBLK_6),',');   

                        ELSIF :P9203_OPTIONS = 7 THEN

                          str := apex_string.join(apex_t_varchar2(:P9_FBLK,:P9_FBLK_2,:P9_FBLK_3,:P9_FBLK_4,:P9_FBLK_5,:P9_FBLK_6,:P9_FBLK_7),',');    

                        ELSIF :P9203_OPTIONS = 8 THEN

                          str := apex_string.join(apex_t_varchar2(:P9_FBLK,:P9_FBLK_2,:P9_FBLK_3,:P9_FBLK_4,:P9_FBLK_5,:P9_FBLK_6,:P9_FBLK_7,:P9_FBLK_8),',');    

                        ELSIF :P9203_OPTIONS = 9 THEN

                          str := apex_string.join(apex_t_varchar2(:P9_FBLK,:P9_FBLK_2,:P9_FBLK_3,:P9_FBLK_4,:P9_FBLK_5,:P9_FBLK_6,:P9_FBLK_7,:P9_FBLK_8,:P9_FBLK_9),',');   

                        ELSIF :P9203_OPTIONS = 10 THEN

                          str := apex_string.join(apex_t_varchar2(:P9_FBLK,:P9_FBLK_2,:P9_FBLK_3,:P9_FBLK_4,:P9_FBLK_5,:P9_FBLK_6,:P9_FBLK_7,:P9_FBLK_8,:P9_FBLK_9,:P9_FBLK_10),',');

                        ELSE

                          str := apex_string.join(apex_t_varchar2(:P9_FBLK),',');

                        END IF;

                       

                      SELECT count(*) 

                           , count(distinct column_value) 

                        INTO v_cnt_a 

                           , v_cnt_b 

                        FROM table(apex_string.split(str,','));

                       

                      IF (v_cnt_a != v_cnt_b)

                        THEN

                         RETURN FALSE;

                        ELSE

                         RETURN TRUE;

                        END IF;

                       

                      END;

                      Regards

                      Ben