^[pRoUd]^ 작업실

SQL> create table tab_ampersand_test
  2  as
  3  select 'OK' as name
  4    from dual
  5  ;

Table created.

SQL> select name
  2    from tab_ampersand_test
  3  ;

NA
--
OK

1 row selected.

Elapsed: 00:00:00.00
SQL> set escape |
SQL> select name
  2    from tab_&__tab|_test
  3  ;
Enter value for __tab: ampersand

NA
--
OK

1 row selected.

Elapsed: 00:00:00.01

16:49:53 SQL> create table check_dml_time     
16:50:22   2  (
16:50:24   3   fix_column varchar2(10)
16:50:38   4  ,test_column varchar2(10)
16:50:49   5  )
16:50:50   6  ;

Table created.

Elapsed: 00:00:00.02
16:50:51 SQL> insert into check_dml_time values ('FIX','FIRST');

1 row created.

Elapsed: 00:00:00.01
16:51:21 SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
16:51:23 SQL> select created, last_ddl_time from user_objects where object_name = upper('check_dml_time');

CREATED           LAST_DDL_TIME
----------------- -----------------
20100726 16:50:51 20100726 16:50:51

1 row selected.

Elapsed: 00:00:00.02
16:52:01 SQL> select ora_rowscn from check_dml_time;

          ORA_ROWSCN
--------------------
      11466381392945

1 row selected.

Elapsed: 00:00:00.01
16:52:31 SQL> select scn_to_timestamp(ora_rowscn) from check_dml_time;

SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------------------------------------------------------------------------
2010/07/26 16:51:23

1 row selected.

Elapsed: 00:00:00.01
16:53:31 SQL> update check_dml_time 
16:53:43   2     set test_column = 'UPDATED'
16:53:50   3  ;

1 row updated.

Elapsed: 00:00:00.01
16:53:55 SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
16:54:00 SQL> select scn_to_timestamp(ora_rowscn) from check_dml_time;

SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------------------------------------------------------------------------
2010/07/26 16:53:59

1 row selected.

Elapsed: 00:00:00.00

CREATE PUBLIC DATABASE LINK db_link_nm
CONNECT TO id IDENTIFIED BY "password!"
USING db_service_nm;

WITH test AS
(
SELECT '40' code, 30 cnt FROM dual
UNION ALL SELECT '30', 50 FROM dual
UNION ALL SELECT '10', 20 FROM dual
)
SELECT code
, SUM(cnt) cnt
, RATIO_TO_REPORT(SUM(cnt)) OVER(PARTITION BY GROUPING(code)) * 100 ratio
FROM test
GROUP BY ROLLUP(code)
;

column viewname format a100

with tab
as
(
select '0001' code, 'A' name from dual union all
select '0001' code, 'B' name from dual union all
select '0001' code, 'C' name from dual union all
select '0002' code, 'D' name from dual union all
select '0002' code, 'A' name from dual union all
select '0003' code, 'F' name from dual union all
select '0003' code, 'E' name from dual union all
select '0003' code, 'B' name from dual union all
select '0003' code, 'C' name from dual
)
select x.code
      ,substr(max(sys_connect_by_path(x.name,', ')),2) viewname
  from (
       select code
             ,name
             ,row_number() over (partition by code order by code ,name) rn
         from tab
       ) x
 start with x.rn = 1
connect by prior x.code = x.code
   and prior x.rn = x.rn - 1
 group by x.code
/

with tab
as
(
select '0001' code, 'A' name from dual union all
select '0001' code, 'B' name from dual union all
select '0001' code, 'C' name from dual union all
select '0002' code, 'D' name from dual union all
select '0002' code, 'A' name from dual union all
select '0003' code, 'F' name from dual union all
select '0003' code, 'E' name from dual union all
select '0003' code, 'B' name from dual union all
select '0003' code, 'C' name from dual
)
select x.code
      ,substr(xmlagg(xmlelement(x,' ,',x.name).extract('//text()')),3) viewnames
  from tab x
 group by x.code
/

Caution: This is a very powerful system privilege, as it lets the grantee bypass application-driven security policies. Database administrators should use caution when granting this privilege.

grant exempt access policy to dw;
revoke exempt access policy from dw;

CREATE OR REPLACE FUNCTION CLOB2BLOB (p_clob in CLOB)
return BLOB
is
-- transforming CLOB a BLOB
p_blob blob;
l_off number default 1;
l_amt number default 4096;
l_offWrite number default 1;
l_amtWrite number;
l_str varchar2(4096 char);
begin
DBMS_LOB.CREATETEMPORARY(p_blob, TRUE);
begin
loop
dbms_lob.read ( p_clob, l_amt, l_off, l_str );

l_amtWrite := utl_raw.length ( utl_raw.cast_to_raw( l_str) );
dbms_lob.write( p_blob, l_amtWrite, l_offWrite,
utl_raw.cast_to_raw( l_str ) );

l_offWrite := l_offWrite + l_amtWrite;

l_off := l_off + l_amt;
l_amt := 4096;
end loop;
exception
when no_data_found then
NULL;
end;
return p_blob;
end CLOB2BLOB;
/