Mega Code Archive

 

Categories / Oracle PLSQL / System Packages

 

Outln_pkg drop_by_cat

SQL> SQL> create or replace outline my_outline   2  for category my_category   3  on select * from dual   4  / Outline created. SQL> SQL> SQL> create or replace outline my_other_outline   2  for category "My_Category"   3  on select * from dual   4  / Outline created. SQL> SQL> SQL> select name, category, sql_text from user_outlines; NAME                           CATEGORY                       SQL_TEXT ------------------------------ ------------------------------ -------------------------------------------------------------------------------- MY_OTHER_OUTLINE               My_Category                    select * from dual OUTLINE_1                      CAT_1                          select * from dual OUTLINE_2                      CAT_2                          select * from dual OUTLINE_3                      CAT_2                          select * from dual A MYOUTLINE                      MYCATEGORY                     select empno, ename from emp where empno > 0 MY_OUTLINE                     MY_CATEGORY                    select * from dual 6 rows selected. SQL> SQL> exec outln_pkg.drop_by_cat( 'my_category' ); PL/SQL procedure successfully completed. SQL> SQL> SQL> select name, category, sql_text from user_outlines; NAME                           CATEGORY                       SQL_TEXT ------------------------------ ------------------------------ -------------------------------------------------------------------------------- MY_OTHER_OUTLINE               My_Category                    select * from dual OUTLINE_1                      CAT_1                          select * from dual OUTLINE_2                      CAT_2                          select * from dual OUTLINE_3                      CAT_2                          select * from dual A MYOUTLINE                      MYCATEGORY                     select empno, ename from emp where empno > 0 MY_OUTLINE                     MY_CATEGORY                    select * from dual 6 rows selected. SQL> SQL> SQL> exec outln_pkg.drop_by_cat( 'MY_CATEGORY' ); PL/SQL procedure successfully completed. SQL> SQL> SQL> select name, category, sql_text from user_outlines; NAME                           CATEGORY                       SQL_TEXT ------------------------------ ------------------------------ -------------------------------------------------------------------------------- MY_OTHER_OUTLINE               My_Category                    select * from dual OUTLINE_1                      CAT_1                          select * from dual OUTLINE_2                      CAT_2                          select * from dual OUTLINE_3                      CAT_2                          select * from dual A MYOUTLINE                      MYCATEGORY                     select empno, ename from emp where empno > 0 SQL> SQL> exec outln_pkg.drop_by_cat( 'My_Category' ); PL/SQL procedure successfully completed. SQL> SQL> SQL> select name, category, sql_text from user_outlines; NAME                           CATEGORY                       SQL_TEXT ------------------------------ ------------------------------ -------------------------------------------------------------------------------- OUTLINE_1                      CAT_1                          select * from dual OUTLINE_2                      CAT_2                          select * from dual OUTLINE_3                      CAT_2                          select * from dual A MYOUTLINE                      MYCATEGORY                     select empno, ename from emp where empno > 0 SQL> SQL>