Page 181
TY_REVIEW (T_REVIEW_DATES(SYSDATE), T_REVIEW_DATES(SYSDATE-30), T_REVIEW_DATES(SYSDATE-60), T_REVIEW_DATES(SYSDATE-90) ), TY_REVIEW (T_REVIEW_DATES(SYSDATE), T_REVIEW_DATES(SYSDATE-30), T_REVIEW_DATES(SYSDATE-60), T_REVIEW_DATES(SYSDATE-90) ), TY_REVIEW (T_REVIEW_DATES(SYSDATE), T_REVIEW_DATES(SYSDATE-30), T_REVIEW_DATES(SYSDATE-60), T_REVIEW_DATES(SYSDATE-90) ), SYSDATE, SYSDATE); -- Now, query using the in operator COLUMN review_date FORMAT A20 COLUMN salary_last_increase FORMAT A20 SQL> SELECT emp_id FROM t_emp_info WHERE promotion_date IN (review_date, salary_last_increase); EMP_ID ---------- 2 SQL> SELECT emp_id FROM t_emp_info WHERE promotion_date NOT IN (review_date, salary_last_increase); EMP_ID ---------- 1
A number of new operators are available for use with collections in Oracle Database 10g. Table 7-3 lists these new operators and describes their use. For examples and usage, see the Oracle Database 10g SQL Reference Guideor the Oracle Database 10g PL/SQL Users Guide and Reference.
There have been changes to the PL/SQL compiler in Oracle Database 10g. This includes a new version of the PL/SQL compiler, changes to native compilation of PL/SQL, new fine-grained debugging capabilities, and new PL/SQL compiler warnings. Let's look at each of these features in more detail in the next few sections.
Oracle Database 10g comes with a new, faster PL/SQL compiler. The compiler is more efficient than the previous PL/SQL compilers, and thus generates code that runs more efficiently. In fact, Oracle reports that the new PL/SQL compiler is 50 to 75 percent faster than the Oracle9i compiler. Associated with the new compiler is a new parameter, plsql_optimize_level. When this parameter is set to 2 (the default