1,897 reputation
731
bio website
location Ottawa.Ontario, Canada
age 56
visits member for 3 years, 3 months
seen yesterday

Oracle DBA and programmer with 10 years experience maintaining mission-critical applications. Lots of PL/SQL, C# and the usual glue, vbscript, DOS


Sep
16
comment Can this business logic be enforced by a conditional database constraint?
Fabulous, I ran into this with another MV but only saw adding a constraint as a problem not a solution
Sep
16
accepted Can this business logic be enforced by a conditional database constraint?
Sep
14
comment Can this business logic be enforced by a conditional database constraint?
I will have to document this internal rule that other databases connecting to this one must use a database link with a custom user who only has rights to execute packages, not to insert directly. Thanks
Sep
14
comment Can this business logic be enforced by a conditional database constraint?
Excellent point, now I see why the application does not allow deletes on entries in CASE_STAGE or CASE_RECOMMENDATION after the stage is closed.
Sep
14
revised Can this business logic be enforced by a conditional database constraint?
clarification
Sep
14
comment Can this business logic be enforced by a conditional database constraint?
For all values of CASE_STAGE.STAGE_ID the value for PREVIOUS_STAGE_ID must be found in CASE_STAGE.ID
Sep
13
comment Can this business logic be enforced by a conditional database constraint?
I'm a great believer in packages and already have one that does CRUD operations and logging on these tables. However I cannot enforce the use of the package if another database attempts inserts through a link. I have to rely on the due diligence of the programmer. A constraint is cleaner, easier to discover and document but your answer would work if a package was used.
Sep
13
comment Can this business logic be enforced by a conditional database constraint?
1646 represents the only stage that has this requirement for a case recommendation. Splitting the table would break the ORM and require an extensive rewrite of the application so it is not feasible. @Erwin- no deletes are allowed from CASE_STAGE or CASE_RECOMMENDATION
Sep
13
comment Can this business logic be enforced by a conditional database constraint?
Thank you, a great answer that I will be able to use for other "rules", just not this one.
Sep
13
comment Can this business logic be enforced by a conditional database constraint?
Unfortunately not. A case stage can have one or more recommendations at multiple stages. There can be zero to many stages with CASE_STAGE.STAGE_ID=1646
Sep
13
revised Can this business logic be enforced by a conditional database constraint?
added for clarity
Sep
13
asked Can this business logic be enforced by a conditional database constraint?
Sep
13
answered How can I ensure that only one copy of a procedure is running in Oracle?
Sep
13
answered How do I use the IF…ELSE condition in a WHERE clause?
Sep
12
revised How to I interleave logs from two different Oracle tables?
updated after a helpful comment
Sep
11
revised How to I interleave logs from two different Oracle tables?
added 459 characters in body
Sep
11
revised How to I interleave logs from two different Oracle tables?
added 4 characters in body
Sep
11
answered How to I interleave logs from two different Oracle tables?
Jul
24
answered Oracle database link to MySQL database on RedHat Linux
Jul
24
comment ORA-00379 no free buffers
You could tell us what version of Oracle you are using and what DB_BLOCK_BUFFERS is set to. That would help.