...
In the next example we use the CASE statement, built-in function and PL/SQL user function to only update those employees who
- have salary high enough (based on their location) or
- are with the company long enough or
- fulfill the conditions defined by PL/SQL user function
| No Format | ||
|---|---|---|
| ||
case when deptno in (select deptno from scott.dept where loc='NEW YORK') then 3000
when deptno in (select deptno from scott.dept where loc='DALLAS') then 2000
else 1500 end < sal
or months_between(sysdate, hiredate) > 436
or scott.my_plsql_function(ename) = 1 |
| No Format | ||
|---|---|---|
| ||
dbvrep> FILTER OFFLINE TABLE scott.emp ON PRE-UPDATE SET TO case when deptno in (select deptno from scott.dept where loc='NEW YORK') then 3000 when deptno in (select deptno from scott.dept where loc='DALLAS') then 2000 else 1500 end < sal or months_between(sysdate, hiredate) > 436 or scott.my_plsql_function(ename) = 1 |
| Note |
|---|
User defined function scott.my_plsql_function must be called using the schema name. User dbvrep must be granted an execute privilege on this function. |
| Note |
|---|
User defined function scott.my_plsql_function only needs to be defined on target database. There's no need to create it on source DB too. |
| Note |
|---|
When target is not oracle database then avoid using inbuilt functions in filtering as same functions might not be available on target environment. |
...