This discussion is archived
1 2 Previous Next 25 Replies Latest reply: Apr 3, 2013 6:53 AM by padders Go to original post RSS
  • 15. Re: Fool question about constraint check
    mcardia Newbie
    Currently Being Moderated
    I created this function below. It´s working for me and maybe it´s helpful for others.
    I validate most king of constraint (IN, IS NULL, IS NOT NULL, X > Y AND X < Z, etc...) for text and numeric columns.

    Maybe something can be improved, but it´s working for now.
        function constraint_validate(wvalue in varchar, wconstraint in varchar) return varchar is
            wcolumn     varchar(30)   := null;
            wcondition  varchar(4000) := null;
            wplsql      varchar(4000) := null;
            wreturn     varchar(3)    := null;
        begin
            select search_condition
            into wcondition
            from all_constraints
            where (
                    ((instr(wconstraint, '.') = 0 AND constraint_name = wconstraint))
                    or
                    ((instr(wconstraint, '.') > 0 AND constraint_name = substr(wconstraint, instr(wconstraint, '.')+1)) AND owner = substr(wconstraint, 1, instr(wconstraint, '.')-1))
                  )
            and search_condition is not null;
            wcondition := trim(wcondition);
            wcolumn    := trim(regexp_substr(wcondition, '^"?[A-Za-z_#$][^ ]*'));
    
            if wvalue is null and regexp_like(wcondition, '^[^ ]+ IN +\([^\)]+\)$', 'i') then
                wcondition := 'instr(''' || upper(trim(regexp_substr(replace(wcondition, chr(39), ''), '\(.+\)'))) || ''', ''NULL'') > 0';
            elsif wvalue is null and regexp_like(wcondition, '^[^ ]+ IS (NOT )?NULL$', 'i') then
                wcondition := replace(wcondition, wcolumn, 'NULL');
            else
                wcondition := replace(wcondition, wcolumn, chr(39) || wvalue || chr(39));
            end if;
            wplsql := 'begin if '|| wcondition ||' then :x := ''Y''; else :x :=''N''; end if; end; ';
            execute immediate wplsql using out wreturn;
            return(wreturn);
        exception
            when no_data_found then
                return('Y');
        end constraint_validate;
  • 16. Re: Fool question about constraint check
    jeneesh Guru
    Currently Being Moderated
    EdStevens wrote:
    Just to play devil's advocate, if you depend on the application to guarantee the validity of your data, what's to prevent using sqlplus to create invalid data?
    If I have an answer for this - Another devil's advocate can again ask -> What if somebody disabled/dropped the constraint and loaded data..

    Anyhow, my intention was different as you could understand..

    I don't believe much in validating business rules at DB level.... Debatable..
  • 17. Re: Fool question about constraint check
    mcardia Newbie
    Currently Being Moderated
    I don´t like too many business rules in the level too.

    Unfortunately, here where i work, that is the standard model. To many things in triggers.

    But that is not the case here. We use PL/SQL as our main development tool. Mostly forms, DAD (web). And that integration i´m working is in this level.

    About disable the constraint. That is the role of the DBA to allow or revoke privileges to users.

    I´m am developer. Here, I don´t have permission to manipulate DDL, DML and DCL at production server. Only the DBA can do that.
    Users can´t connect to database if not by forms or other program we allow. Oracle has ways to do that kind of security.


    Thanks to all.
  • 18. Re: Fool question about constraint check
    padders Pro
    Currently Being Moderated
    It´s working for me and maybe it´s helpful for others
    What you have written may be 'working' but it is potentially castastrophically resource intensive from a database point of view, not to mention being at risk of SQL injection.

    How many files, distinct values, columns and constraints are we talking about here?

    Because the product of those numbers is how many distinct SQL statements you just hard parsed.
    I validate most kind of constraint
    I'll happily concede that most constraints (by way of number) will be simple conditions against single columns, but constraints can and often do refer to multiple columns.

    While we're at it how does this solution return a 'meaningful' error message to the client which appeared to be one of your primary requirements?
  • 19. Re: Fool question about constraint check
    EdStevens Guru
    Currently Being Moderated
    jeneesh wrote:
    EdStevens wrote:
    Just to play devil's advocate, if you depend on the application to guarantee the validity of your data, what's to prevent using sqlplus to create invalid data?
    If I have an answer for this - Another devil's advocate can again ask -> What if somebody disabled/dropped the constraint and loaded data..
    That would be a deliberate and conscious action. Not the same as a programming error that simply overlooked or forgot or failed to properly implement the requirement.

    Anyhow, my intention was different as you could understand..

    I don't believe much in validating business rules at DB level.... Debatable..
    Very debatable. RDBMS stands for Relational Database Management System (emphasis mine). Do we leverage the "management" capability built into the system, or just use it as a data dump?
  • 20. Re: Fool question about constraint check
    jeneesh Guru
    Currently Being Moderated
    I was talking about business rules..

    Currently I am working on Telecom project.

    We have a PRODUCT_PROMOTION table..

    There are so many conditions to be validated before inserting data into this table.. The conditions will change very frequently based on promotion schemes. I can have multiple check constraints to do this validation..

    I prefer to do these validations on the application.. I will never want to have frequent DDL activity on my table to suffice BUSINESS RULES...

    Edited by: jeneesh on Apr 3, 2013 6:12 PM
    Or just as an example take any module in ORACLE E-Business suite.. How many check constraints you can see in the application tables..? I am not saying ORACLE is always right, still...
  • 21. Re: Fool question about constraint check
    367852 Explorer
    Currently Being Moderated
     I don't believe much in validating business rules at DB level.... Debatable.
    Highly recommended for the maintaining data integrity when more than one application share the database tables and source/update the data.
  • 22. Re: Fool question about constraint check
    mcardia Newbie
    Currently Being Moderated
    padders wrote:
    What you have written may be 'working' but it is potentially castastrophically resource intensive from a database point of view, not to mention being at risk of SQL injection.
    How many files, distinct values, columns and constraints are we talking about here?
    Because the product of those numbers is how many distinct SQL statements you just hard parsed.
    That is for the developer to know. I will not use this function for a constraint that i don´t know about. It´s not like i´m triggering this function to everywhere in my database.
    It will be used just where i want and know what will happen. Simple like that. Did you tried a SQL injection in this code? I don´t think you can... unless you are able to create a constraint with the injection and change de code who calls this function to
    execute you "injection" constraint. And i checked. it does not require the catastrophically resource you had mention. Not even a bit.
    I'll happily concede that most constraints (by way of number) will be simple conditions against single columns, but constraints can and often do refer to multiple columns.
    As i said, that is for the developer to know. If this function is use with a constraint he has not evaluated, it´s his mistake.
    While we're at it how does this solution return a 'meaningful' error message to the client which appeared to be one of your primary requirements?
    As said in other post. In forum, we post things simplified. I can´t post all my system here. This function it´s a part of something bigger. Where I´m using it, returns a meaningful message based on result of the function.
  • 23. Re: Fool question about constraint check
    EdStevens Guru
    Currently Being Moderated
    jeneesh wrote:
    I was talking about business rules..

    Currently I am working on Telecom project.

    We have a PRODUCT_PROMOTION table..

    There are so many conditions to be validated before inserting data into this table.. The conditions will change very frequently based on promotion schemes. I can have multiple check constraints to do this validation..

    I prefer to do these validations on the application.. I will never want to have frequent DDL activity on my table to suffice BUSINESS RULES...

    Edited by: jeneesh on Apr 3, 2013 6:12 PM
    Or just as an example take any module in ORACLE E-Business suite.. How many check constraints you can see in the application tables..? I am not saying ORACLE is always right, still...
    I'm sure there is a balance somewhere. I DO believe that apps should edit and scrub the data as much as possible before ever sending it to the db. But there are also cases where you really don't want to depend on the <error prone>(1) app when the db provides a facility to make sure the data is right. Populating a PK with the value retrieved from a sequence is the classic example.

    (1) Actually, the term "error prone app" is a redundancy.
  • 24. Re: Fool question about constraint check
    jeneesh Guru
    Currently Being Moderated
    EdStevens wrote:
    I'm sure there is a balance somewhere. I DO believe that apps should edit and scrub the data as much as possible before ever sending it to the db. But there are also cases where you really don't want to depend on the <error prone>(1) app when the db provides a facility to make sure the data is right. Populating a PK with the value retrieved from a sequence is the classic example.

    (1) Actually, the term "error prone app" is a redundancy.
    I don't have any dislike in using PKs, FKs, Not Null - All those basic validations at DB level.

    But regarding specific business rules ( Like - "GRADE in (1,2,3,4)" ) - I do have a dislike..
  • 25. Re: Fool question about constraint check
    padders Pro
    Currently Being Moderated
    That is for the developer to know
    What is? You aren't the developer? Maybe you shouldn't be developing code then :-D
    Did you tried a SQL injection in this code?
    I don't really need to. You replace the column name in the constraint with parameter wvalue and execute the result. The function is injectable.
    it does not require the catastrophically resource you had mention. Not even a bit.
    I think you need to read about bind variables, hard parsing and library cache contention.

    Also you should be using VARCHAR2 not VARCHAR.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points