自定义行转定函数wm_concat应用(PLS-00530)

项目需要在生产库拉数到开发库,但是开发库的表结构比生产库的表结构新 ,不可直接用

insert into table_name@database_dev select * from table_name@database_sc where partition_key= '201209300000'

;

commit;

所以在以生产库的表结构为准,并且将源分区和目标分区不一致(就是将20120930分区的数据入到20120731分区中),这样就要求列出生产环境的表字段,由于表比较多,需要程序去执行.在这种情况下想到行转列函数wm_concat,由于系统自带的这个函数输出是有限制的varchar2(32767),如果字段过多,输出时会报PLS-00530错误,,所以改了一下输出类型,自定义一下.自定义如下:

定义类型 :
CREATE OR REPLACE TYPE Wm_Concat_Impl AS OBJECT
-- AUTHID CURRENT_USER AS OBJECT
(
CURR_STR nclob, -- 原来为varchar2(32767)

STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
P1 IN nclob) RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,
                                 RETURNVALUE OUT nclob,
                                 FLAGS IN NUMBER)
                     RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,
SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER

)
;

定义类型body:
CREATE OR REPLACE TYPE BODY Wm_Concat_Impl
IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL)
RETURN NUMBER
IS
BEGIN
SCTX := WM_CONCAT_IMPL(NULL) ;
RETURN ODCICONST.SUCCESS;
END;

MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
          P1 IN nclob)
RETURN NUMBER
IS
BEGIN
    IF(CURR_STR IS NOT NULL) THEN
      CURR_STR := CURR_STR || ',' || P1;
    ELSE
      CURR_STR := P1;
    END IF;
    RETURN ODCICONST.SUCCESS;
END;

MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,
                                 RETURNVALUE OUT nclob,
                                 FLAGS IN NUMBER)
    RETURN NUMBER
IS
BEGIN
    RETURNVALUE := CURR_STR ;
    RETURN ODCICONST.SUCCESS;
END;

MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,
                                   SCTX2 IN WM_CONCAT_IMPL)
RETURN NUMBER
IS
BEGIN
    IF(SCTX2.CURR_STR IS NOT NULL) THEN
      SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR ;
    END IF;
    RETURN ODCICONST.SUCCESS;
END;

END;

自定义行变列函数:
CREATE OR REPLACE FUNCTION wm_concat(P1 nclob)
RETURN nclob AGGREGATE USING boc_rdm.WM_CONCAT_IMPL ;

-------------------------行转列应用(拉数)------------------------------------------------

declare
vs_sql long;
begin
for v_tab in
(select table_name from staging_table@boc_sc t
where sys_id in ('GTS','SCF')
and table_level = 2
order by table_name
)
loop
select 'insert into '|| v_tab.table_name||'@boc_sj ( partition_key ,' ||WM_CONCAT(COLUMN_NAME)|| ') select ''201207310000'' partition_key , '|| WM_CONCAT(COLUMN_NAME) ||
' from '||v_tab.table_name||'@boc_sc where partition_key = ''201209300000''' into vs_sql
from all_tab_columns@boc_sc t
where t.OWNER = 'USER_NAME'
and column_name not in ('PARTITION_KEY')
and t.TABLE_NAME = v_tab.table_name
order by column_id asc
; www.zzzyk.com
-- dbms_output.put_line(vs_sql);
execute immediate vs_sql;
commit;
end loop;
end;

补充：综合编程 , 其他综合 ,