sql拼接问题,获得建表语句
问题背景:需要获得批量获得建表语句
建表语句sql(类型,表名,用户)
select dbms_metadata.get_ddl ('TABLE','AFC_PRPLCONTENT','PWC') from dual;
###引号拼接,单引号' 需要转换为''才能识别为单引号,或者设置为chr(39)
原始sql
select table_name from dba_tables where OWNER='XXX';
select chr(39)||table_name||chr(39)||', ' from dba_tables where OWNER='XXX';
SQL> select 'select dbms_metadata.get_ddl(''TABLE'','||chr(39)||table_name||chr(39)|| ',''POLICYRP_SEC'') from dual; 'from dba_tables where OWNER='XXX';
'SELECTDBMS_METADATA.GET_DDL(
--------------------------------------------------------------------------------
select dbms_metadata.get_ddl('TABLE','SXTABLE','POLICYRP_SEC') from dual;
select dbms_metadata.get_ddl('TABLE','SXLOB','POLICYRP_SEC') from dual;
select 'select dbms_metadata.get_ddl(''TABLE'','||''||table_name||''|| ',''POLICYRP_SEC'') from dual; 'from dba_tables where OWNER='XXX';
'SELECTDBMS_METADATA.GET_DDL(
--------------------------------------------------------------------------------
select dbms_metadata.get_ddl('TABLE',SXTABLE,'POLICYRP_SEC') from dual;
select dbms_metadata.get_ddl('TABLE',SXLOB,'POLICYRP_SEC') from dual;