-- 列转行
-- 通过表名生成查询语句:
SELECT 'select ' || listagg(case when u.DATA_TYPE = 'DATE' then 'to_char(' || u.COLUMN_NAME || ', ' || '''' || 'yyyy-mm-dd hh24:mi:ss' || '''' || ')' when u.DATA_TYPE = 'TIMESTAMP(0)' then 'to_char(' || u.COLUMN_NAME || ', ' || '''' || 'yyyy-mm-dd hh24:mi:ss' || '''' || ')' when u.DATA_TYPE = 'TIMESTAMP(6)' then 'to_char(' || u.COLUMN_NAME || ', ' || '''' || 'yyyy-mm-dd hh24:mi:ss' || '''' || ')' when u.DATA_TYPE in ('CLOB', 'BLOB') then '1' when u.DATA_TYPE in ('VARCHAR2', 'CHAR') then 'replace(replace(replace(' || u.COLUMN_NAME || ',chr(10),' || '''' || '''' || '),chr(13),' || '''' || '''' || '),' || '''' || '^' || '''' || ',' || '''' || '#' || '''' || ')' else u.COLUMN_NAME end, ',') WITHIN GROUP(ORDER BY u.COLUMN_ID) || ' from T_INFO PARTITION (SYS_P10793) ' FROM user_tab_columns u where table_name = 'T_INFO' order by u.COLUMN_ID
错误原因:由于oracle对字符串长度有限制,长度不能超过4000。
解决办法:使用oracle的另外一个函数xmlagg,生成CLOB格式
SELECT 'select ' ||trim(',' from XMLAGG(XMLPARSE(CONTENT case when u.DATA_TYPE = 'DATE' then 'to_char(' || u.COLUMN_NAME || ', ' || '''' || 'yyyy-mm-dd hh24:mi:ss' || '''' || ')' when u.DATA_TYPE = 'TIMESTAMP(0)' then 'to_char(' || u.COLUMN_NAME || ', ' || '''' || 'yyyy-mm-dd hh24:mi:ss' || '''' || ')' when u.DATA_TYPE = 'TIMESTAMP(6)' then 'to_char(' || u.COLUMN_NAME || ', ' || '''' || 'yyyy-mm-dd hh24:mi:ss' || '''' || ')' when u.DATA_TYPE in ('CLOB', 'BLOB') then '1' when u.DATA_TYPE in ('VARCHAR2', 'CHAR') then 'replace(replace(replace('||u.COLUMN_NAME||',chr(10),'||''''||''''||'),chr(13),'||''''||''''||'),'||''''||'^'||''''||',' ||''''||'#'||''''||')' else u.COLUMN_NAME end || ',' WELLFORMED)ORDER BY u.COLUMN_ID).getClobVal()) || ' from T_INFO PARTITION (SYS_P10793) ' FROM user_tab_columns u where table_name = 'T_INFO' order by u.COLUMN_ID
python3.6 代码调用以上生成SQL:
代码如下:
Sql_N=""" SELECT 'select ' ||trim(',' from XMLAGG(XMLPARSE(CONTENT case when u.DATA_TYPE = 'DATE' then 'to_char(' || u.COLUMN_NAME || ', ' || '''' || 'yyyy-mm-dd hh24:mi:ss' || '''' || ')' when u.DATA_TYPE = 'TIMESTAMP(0)' then 'to_char(' || u.COLUMN_NAME || ', ' || '''' || 'yyyy-mm-dd hh24:mi:ss' || '''' || ')' when u.DATA_TYPE = 'TIMESTAMP(6)' then 'to_char(' || u.COLUMN_NAME || ', ' || '''' || 'yyyy-mm-dd hh24:mi:ss' || '''' || ')' when u.DATA_TYPE in ('CLOB', 'BLOB') then '1' when u.DATA_TYPE in ('VARCHAR2', 'CHAR') then 'replace(replace(replace('||u.COLUMN_NAME||',chr(10),'||''''||''''||'),chr(13),'||''''||''''||'),'||''''||'^'||''''||','| |''''||'#'||''''||')' else u.COLUMN_NAME end || ',' WELLFORMED)ORDER BY u.COLUMN_ID).getClobVal()) || ' from %s %s ' FROM user_tab_columns u where table_name = '%s' order by u.COLUMN_ID """% (table_name, part_name, table_name) Sql=Sql_N Res_Sql = self.db.query(Sql,'only') Res_Sql = Res_Sql.read() self.logger.info('导出SQL:%s'%Res_Sql) Res = self.db.query(Res_Sql,'all') self.logger.info('返回查询 %s:%s:结果'%(table_name, part_name)) return (Res)
以下连接为python 实现 oracle 到 greenplum ETL 脚本,涉及以上代码,供参考
PyETL2.0_oracle_gp.rar-Linux脚本