1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Pl/sql : checking values of passing string to a table

Discussion in 'SQL PL/SQL' started by Bufoss, Oct 11, 2016.

  1. Bufoss

    Bufoss Newly Initiated

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Greece
    Hi,
    I have a problem to make a function passing a string parameter with values seperated with '#' and checks if
    there is an invalid combination (saved to table A) .

    Table A is as follows :
    TABLE_A
    | COL_A | COL_B COL_C |
    | INVALID | 1 | A#B |
    | INVALID | 2 | D#C |
    | INVALID | 3 | A#O#F |

    Code (SQL):

    FUNCTION VALIDATE_STR (P_STR IN VARCHAR2) RETURN VARCHAR2 IS

    Cursor COUNT_INVALID_CUR IS
        SELECT COUNT(*)
         FROM TABLE_A
        WHERE COL_A = 'INVALID';

    Cursor CHECK_STR(C_PARAM1 VARCHAR2,C_PARAM2 VARCHAR2) IS
       WITH temp AS ( SELECT regexp_substr(C_PARAM1,'[^#]+', 1, level) Code
                        FROM dual
                    CONNECT BY regexp_substr(C_PARAM2, '[^#]+', 1, level) IS NOT NULL)
            SELECT decode(COUNT(*), 0, 'true', 'false')
              FROM dual
             WHERE EXISTS ( SELECT NULL
                              FROM temp
                             GROUP BY Code
                            HAVING COUNT(*) > 1);


    V_COUNT                  NUMBER(8):=1;
    V_LEN                    NUMBER(8):=0;
    V_INVALID               TABLE_A.COL_C%TYPE;
    V_OUT                    VARCHAR2(5):='false';


    BEGIN

        --Get the number of different combinations
        OPEN COUNT_INVALID_CUR;
       FETCH COUNT_INVALID_CUR INTO V_LEN;
       CLOSE COUNT_INVALID_CUR;

       --If there are rows in TABLE_A for the value INVALID continue
       --else return false
       IF V_LEN > 0 THEN
           WHILE V_COUNT <= V_LEN
           LOOP

               -- Get Invalid Combinations from TABLE_A
               --GET_VALUE is function returns the COL_C from TABLE_A
               V_INVALID  := GET_VALUE('INVALID',
                                        V_COUNT,
                                        NULL,
                                        FALSE);

                --Execute cursor for the current combination in order to
                --check if ADF passed valid combination
                OPEN CHECK_STR(P_STR,V_INVALID_COMBINATION);
                FETCH CHECK_STR INTO V_OUT;
                CLOSE CHECK_STR;
           
                --Exit procedure if false combination found
                IF V_OUT = 'false' THEN
                   RETURN 'false';
                END IF;
           
                V_COUNT := V_COUNT+1;
           END LOOP;
       ELSE
           RETURN 'false';
       END IF;

       RETURN 'true';

    END VALIDATE_STR;

    Examples
    VALIDATE_STR (A#B) ==> 'false'
    VALIDATE_STR (C#D) ==> 'false'
    VALIDATE_STR (A#O#C) ==> 'true'
    VALIDATE_STR (O#C#D) ==> 'false'
    VALIDATE_STR (O#A) ==> 'true'


    Could you help me please ?
     
    Last edited: Oct 11, 2016
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What is your criteria for valid and invalid strings? It isn't clear from your limited example what is and what isn't valid.
    When you post that information someone may be able to assist you.
     
  3. Bufoss

    Bufoss Newly Initiated

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Greece
    The string passed to the function should no contain all the values of a row of TABLE_A.COL_C. There are three invalid combinations of values , if at least on combination matches (regardless if there are more values in parameter string) return 'false'.

    Example :
    1. VALIDATE_STR (A#B) ==> 'false'
    A#B exists in TABLE_A so it is invalid input

    2. VALIDATE_STR (O#C#D) ==> 'false'
    D#C exists in TABLE_A so it is invalid input (I don't care about the order of the values and if there are more values such as O)

    3. VALIDATE_STR (A#O#C) ==> 'true'
    There is no combination contains A,O,C in TABLE_A . So, it is valid input.

    I am sorry but my english is not so good.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You did not post all of the pieces to this puzzle; the GET_VALUE function needs to be provided before I can test your code and see where any errors might be.
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    1) can you provide source code of the function GET_VALUE ?
    2) To you necessary to define : full coincidence or of a part of a template?