动态脚本创建预言机

Question

我有一个表T1，其中包含基表名称及其列名。 我想为每个表生成脚本，其中列标题由管道分隔符分隔。

create table t1 (table_nm varchar2(10) , col_nm varchar2(20));
INSERT INTO T1  SELECT 'AAA' , 'FNAME' FROM DUAL UNION ALL
SELECT 'AAA' , 'LNAME' FROM DUAL UNION ALL
SELECT 'AAA' , 'PH_NO' FROM DUAL UNION ALL
SELECT 'BBB' , 'LAST_NM' FROM DUAL UNION ALL
SELECT 'BBB' , 'EMAIL' FROM DUAL ;
COMMIT;

我正在尝试的代码。

SELECT * FROM
(
WITH ABC AS (SELECT TABLE_NM, RTRIM(XMLAGG(XMLELEMENT(E,COL_NM,'|').EXTRACT('//text()') ORDER BY COL_NM).GETCLOBVAL(),'|') AS COL 
from t1 group by table_nm)
select
'set termout off '||chr(10)|| 
'set timing off '||chr(10)||
'set echo off '||chr(10)||
'set feedback off '||chr(10)||
'set linesize 104 '||chr(10)||
'set pagesize 0 '||CHR(10)||
'spool /tmp/'||abc.TABLE_NM||'.csv '||CHR(10)||
'SELECT '||''''||COL||''''||' FROM DUAL;'||CHR(30)||
'select '||COL||' from '||abc.table_nm||';'||chr(10)||
'spool off' EXTRACT
FROM ABC
);

我在实际数据中遇到的错误。

1( ORA-00996：连接运算符是 ||，而不是 |

预期文件/输出

AAA.dat
FNAME|LNAME|PH_NO
.... some values with pipe delimiter
...
BBB.dat
LAST_NM|EMAIL
some values with pipe delimiter

你能帮忙吗？

我已经修改了问题并删除了第一个问题。 是的，我不需要 ||最后..我也在查询中更改了。在sql开发人员中执行上述代码后，我得到的输出如下

"set termout off 
set timing off 
set echo off 
set feedback off 
set linesize 104 
set pagesize 0 
spool /tmp/AAA.dat
SELECT 'FNAME|LNAME|PH_NO' FROM DUAL;
SELECT  FNAME|LNAME|PH_NO FROM AAA;
spool off"   

第二个选择语句给出的错误是：

SELECT  FNAME|LNAME|PH_NO FROM AAA;
ORA-00996: the concatenate operator is ||, not |
00996. 00000 -  "the concatenate operator is ||, not |" 

还有其他方法可以解决这个问题吗？

Answer 1

第二个生成的查询使用了错误的连接运算符(|而不是||(：

SELECT  FNAME|LNAME|PH_NO FROM AAA;

由于您需要将分隔符本身包含在输出中，因此您需要变成以下内容：

SELECT  FNAME || '|' || LNAME || '|' || PH_NO FROM AAA;

为此，您可以将分隔符字符 (|( 替换为连接字符串 (||'|'||(。您还需要在此处转义字符串文本中的单引号 (||''|''||(。

您可以直接在生成查询中执行此操作：

...
'select '|| REPLACE(COL, '|', '||''|''||') ||' from '||abc.table_nm||';'||chr(10)||
...

更新后的整个查询：

SELECT * FROM
(
WITH ABC AS (SELECT TABLE_NM, RTRIM(XMLAGG(XMLELEMENT(E,COL_NM,'|').EXTRACT('//text()') ORDER BY COL_NM).GETCLOBVAL(),'|') AS COL 
from t1 group by table_nm)
select
'set termout off '||chr(10)|| 
'set timing off '||chr(10)||
'set echo off '||chr(10)||
'set feedback off '||chr(10)||
'set linesize 104 '||chr(10)||
'set pagesize 0 '||CHR(10)||
'spool /tmp/'||abc.TABLE_NM||'.csv '||CHR(10)||
'SELECT '||''''||COL||''''||' FROM DUAL;'||CHR(30)||
'select '||REPLACE(COL, '|', '||''|''||')||' from '||abc.table_nm||';'||chr(10)||
'spool off' EXTRACT
FROM ABC
);