알모리

create table t_group (
    group_uid number not null,
    group_name varchar2(1000),
    parent_uid number,
    constraint t_group primary key ( group_uid )
)

insert into t_group values ( 1, 'company', NULL );
insert into t_group values ( 2, 'marketing', 1 );
insert into t_group values ( 3, 'sales', 1 );
insert into t_group values ( 4, 'sales1', 3 );
insert into t_group values ( 5, 'sales2', 3 );
insert into t_group values ( 6, 'sales3', 3 );
insert into t_group values ( 7, 'company2', NULL );
insert into t_group values ( 8, 'marketing', 7 );
insert into t_group values ( 9, 'sales', 7 );
insert into t_group values ( 10, 'marketing1', 2 );

select LPAD(' ', 4*(LEVEL-1)) || group_name from t_group
connect by prior group_uid = parent_uid
start with parent_uid is null
order siblings by group_name


LPAD('',4*(LEVEL-1))||GROUP_NAME
--------------------------------------------------------------------------------
company
    marketing
        marketing1
    sales
        sales1
        sales2
        sales3
company2
    marketing
    sales

10 rows selected.

select object_name, subobject_name from user_objects where object_type = 'PACKAGE BODY';

select * from user_objects where object_name = 'PKG_XXX';

select text from user_source where name = 'PKG_XXX';

Friday, January 25 2007

select to_char( sysdate, 'Day, Month DD YYYY' ) from dual;

alter session set NLS_TERRITORY = 'AMERICA';
alter session set nls_date_format = 'Day, Month DD YYYY';
select sysdate from dual;