4 Replies Latest reply: Feb 11, 2011 5:59 AM by ATD RSS

    PL SQL Validation

    829385
      Hi All,

      what i have to do is to take a value from an item on the page and then check whether that value exists in a particular table or not
      i chose pl sql validation returning boolean
      My approach looks like this


      BEGIN
      DECLARE TEMP VARCHAR2(10);
      TEMP=SELECT XYZ FROM ABC WHERE XYZ = :BLAH;
      IF(TEMP=:BLAH)
      RETURN TRUE;
      ELSE RETURN FALSE;
      END IF;
      END

      is it the right way.. i mean i am confused...please help me out
        • 1. Re: PL SQL Validation
          Z?
          Hi

          Firstly, the PL/SQL syntax there is quite a way off...

          DECARE should come before BEGIN.

          You can't assign values straigh from a SQL query like that, you would need to use a SELECT ... INTO ... FROM ... statement.

          Assignments in PL/SQL are := and not =

          The condition in the IF statement should be = and not :=

          Apart from that, yu should use a SQL exists query validation type rather than PL/SQL returning boolean and then you could just write a SQL statement! :)

          Cheers

          Ben
          • 2. Re: PL SQL Validation
            829385
            Hi Munky

            Thanks....i tried the SQL Exist method but seems the validation is not getting active...every time i am entering the value in the item and then checking it in the table(through validation) by knowing that there exists a value equal to it the things are still not working

            I have set type as exists

            validation expression1: select 2 from my_table where my_column = :p2_my_item

            where i have replaced the my tags with the actual names

            Always execute is set to NO
            • 3. Re: PL SQL Validation
              Joel_C
              There are two different condition to consider - execution and validation.

              Regarding 'Execution', you will need to clarify what you mean by "Always Execute is set to NO". Do you mean that the conditional execution parameter is set to "Never"? Or is it just a case of the Validation requiring a specific button press or submit event to occur first? Make sure that the conditional criteria are set up correctly as per your requirements - presumably when an update is made to a field or a button is pressed. If it's set to 'Never', it won't fire, ever (naturally!).

              As far as the Validation part is concerned, you need to tell us what you are validating against - I say 'against' because you only want the validation to 'activate' (halt post-submission processing) upon a failure. In other words, if the validation action criteria evaluates to 'true', it won't activate.

              So, if you want the validation to fail if the tuple exists in the table already (i.e. if you are attempting to enter a 'duplicate'), you ought to choose "not exists" in the validation type. I suspect you are thinking along the lines of the opposite - that the validation 'activates' if it evaluates to true.
              • 4. Re: PL SQL Validation
                ATD
                Hi,

                When checking for the existence of a value on the table for validation, you should use Not Exists. As long as the value does not exist on the table, then the validation passes.

                Andy