admin管理员组文章数量:1128330
I have logic replacing characters with '':
DECLARE
v_owner VARCHAR2(50) := 'LFS_SURVEY'; -- Replace with your schema/owner name
v_table_name VARCHAR2(50) := 'LFS_FAMILY_TAB'; -- Replace with your table name
v_rp_ids VARCHAR2(100) := '2030, 2031'; -- Replace with your RP_ID values
v_columns CLOB;
v_query CLOB;
BEGIN
-- Get all column names with transformations based on data type
EXECUTE IMMEDIATE 'SELECT RTRIM(XMLAGG(XMLELEMENT(e,
CASE
WHEN COLUMN_NAME = ''F_M_ID'' THEN ''TO_CHAR(t.F_M_ID) AS F_M_ID''
WHEN DATA_TYPE LIKE ''%CHAR%'' THEN ''REPLACE(REPLACE(t.'' || COLUMN_NAME || '', CHR(13), ''''), CHR(10), '''') AS '' || COLUMN_NAME
ELSE ''t.'' || COLUMN_NAME
END || '', '').EXTRACT(''//text()'') ORDER BY COLUMN_ID).GETCLOBVAL(), '', '')
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = ''' || v_table_name || '''
AND OWNER = ''' || v_owner || ''''
INTO v_columns;
DBMS_OUTPUT.PUT_LINE(v_columns);
END;
/
The output shows ' instead of '', e.g.:
REPLACE(REPLACE(t.Q_851, CHR(13), &appos;), CHR(10), &appos;) AS Q_851
How can I force it to print ''?
I have logic replacing characters with '':
DECLARE
v_owner VARCHAR2(50) := 'LFS_SURVEY'; -- Replace with your schema/owner name
v_table_name VARCHAR2(50) := 'LFS_FAMILY_TAB'; -- Replace with your table name
v_rp_ids VARCHAR2(100) := '2030, 2031'; -- Replace with your RP_ID values
v_columns CLOB;
v_query CLOB;
BEGIN
-- Get all column names with transformations based on data type
EXECUTE IMMEDIATE 'SELECT RTRIM(XMLAGG(XMLELEMENT(e,
CASE
WHEN COLUMN_NAME = ''F_M_ID'' THEN ''TO_CHAR(t.F_M_ID) AS F_M_ID''
WHEN DATA_TYPE LIKE ''%CHAR%'' THEN ''REPLACE(REPLACE(t.'' || COLUMN_NAME || '', CHR(13), ''''), CHR(10), '''') AS '' || COLUMN_NAME
ELSE ''t.'' || COLUMN_NAME
END || '', '').EXTRACT(''//text()'') ORDER BY COLUMN_ID).GETCLOBVAL(), '', '')
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = ''' || v_table_name || '''
AND OWNER = ''' || v_owner || ''''
INTO v_columns;
DBMS_OUTPUT.PUT_LINE(v_columns);
END;
/
The output shows ' instead of '', e.g.:
REPLACE(REPLACE(t.Q_851, CHR(13), &appos;), CHR(10), &appos;) AS Q_851
How can I force it to print ''?
3 Answers
Reset to default 2As an empty string '' is equivalent to null in Oracle, in this instance you could avoid the issue by replacing tab/newline with null instead; so rather than this:
WHEN DATA_TYPE LIKE ''%CHAR%''
THEN ''REPLACE(REPLACE(t.'' || COLUMN_NAME || '', CHR(13), ''''), CHR(10), '''') AS '' || COLUMN_NAME
do this:
WHEN DATA_TYPE LIKE ''%CHAR%''
THEN ''REPLACE(REPLACE(t.'' || COLUMN_NAME || '', CHR(13), null), CHR(10), null) AS '' || COLUMN_NAME
You don't need to use dynamic SQL here though, as you can statically query the data dictionary - the target schema/table name are variables here, not identifiers:
DECLARE
v_owner VARCHAR2(50) := 'LFS_SURVEY'; -- Replace with your schema/owner name
v_table_name VARCHAR2(50) := 'LFS_FAMILY_TAB'; -- Replace with your table name
v_rp_ids VARCHAR2(100) := '2030, 2031'; -- Replace with your RP_ID values
v_columns CLOB;
v_query CLOB;
BEGIN
-- Get all column names with transformations based on data type
SELECT RTRIM(XMLAGG(XMLELEMENT(e,
CASE
WHEN COLUMN_NAME = 'F_M_ID'
THEN 'TO_CHAR(t.F_M_ID) AS F_M_ID'
WHEN DATA_TYPE LIKE '%CHAR%'
THEN 'REPLACE(REPLACE(t.' || COLUMN_NAME || ', CHR(13), null), CHR(10), null) AS ' || COLUMN_NAME
ELSE 't.' || COLUMN_NAME
END || ', ').EXTRACT('//text()') ORDER BY COLUMN_ID).GETCLOBVAL(), ', ')
INTO v_columns
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = v_table_name
AND OWNER = v_owner;
DBMS_OUTPUT.PUT_LINE(v_columns);
END;
/
With a dummy table created as:
create table LFS_FAMILY_TAB (f_m_id number, foo date, bar varchar2(30))
that outputs:
TO_CHAR(t.F_M_ID) AS F_M_ID, t.FOO, REPLACE(REPLACE(t.BAR, CHR(13), null), CHR(10), null) AS BAR
fiddle
And that is functionally the same as
TO_CHAR(t.F_M_ID) AS F_M_ID, t.FOO, REPLACE(REPLACE(t.BAR, CHR(13), ''), CHR(10), '') AS BAR
If you really want '' instead of null (or perhaps have to deal with other text literals) then you can use XMLTable, which doesn't escape the entities like extract (and XMLQuery):
DECLARE
v_owner VARCHAR2(50) := 'LFS_SURVEY'; -- Replace with your schema/owner name
v_table_name VARCHAR2(50) := 'LFS_FAMILY_TAB'; -- Replace with your table name
v_rp_ids VARCHAR2(100) := '2030, 2031'; -- Replace with your RP_ID values
v_columns CLOB;
v_query CLOB;
BEGIN
-- Get all column names with transformations based on data type
SELECT RTRIM(x.str, ', ')
INTO v_columns
FROM (
SELECT XMLELEMENT(r, XMLAGG(XMLELEMENT(e,
CASE
WHEN COLUMN_NAME = 'F_M_ID'
THEN 'TO_CHAR(t.F_M_ID) AS F_M_ID'
WHEN DATA_TYPE LIKE '%CHAR%'
THEN 'REPLACE(REPLACE(t.' || COLUMN_NAME || ', CHR(13), ''''), CHR(10), '''') AS ' || COLUMN_NAME
ELSE 't.' || COLUMN_NAME
END || ', ') ORDER BY COLUMN_ID)) AS xmlx
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = v_table_name
AND OWNER = v_owner
) atc
CROSS JOIN XMLTABLE(
'/R'
PASSING atc.xmlx
COLUMNS str CLOB PATH '.'
) x;
DBMS_OUTPUT.PUT_LINE(v_columns);
END;
/
which for the same dummy table generates:
TO_CHAR(t.F_M_ID) AS F_M_ID, t.FOO, REPLACE(REPLACE(t.BAR, CHR(13), ''), CHR(10), '') AS BAR
fiddle
Incidentally, your original code produces ', but that's just one entity-escaped quote '. If you wanted two '' then you need even more in your code:
WHEN DATA_TYPE LIKE ''%CHAR%''
THEN ''REPLACE(REPLACE(t.'' || COLUMN_NAME || '', CHR(13), ''''''''), CHR(10), '''''''') AS '' || COLUMN_NAME
But as that would then produce '' it wouldn't actually solve your issue - though you then manually unescape those entities.
Oracle represents 4 consecutive single quotes as one single quote. So, maybe the most readable way when building some dynamic sql would be to declare a single quote variable
sq VARCHAR2(1) := '''';
like below:
DECLARE
v_table_name VARCHAR2(50) := 'LFS_FAMILY_TAB';
v_query CLOB;
sq VARCHAR2(1) := ''''; -- 4 single quotes are represented as one single quote
BEGIN
v_query := 'SELECT '|| Chr(10) ||
' CASE WHEN COLUMN_NAME = ' || sq || sq || 'F_M_ID' || sq || sq || Chr(10) ||
' THEN ' || sq || sq || 'TO_CHAR(t.F_M_ID)' || sq || sq || ' AS F_M_ID ' || Chr(10) ||
' ELSE t.' || sq || 'COLUMN_NAME ' || sq || Chr(10) ||
' END as SOME_ALIAS ' || Chr(10) ||
'FROM ' || v_table_name || Chr(10) ||
' ... ';
dbms_output.put_line(v_query);
END;
/
-- R e s u l t :
SELECT
CASE WHEN COLUMN_NAME = ''F_M_ID''
THEN ''TO_CHAR(t.F_M_ID)'' AS F_M_ID
ELSE t.'COLUMN_NAME '
END as SOME_ALIAS
FROM LFS_FAMILY_TAB
...
NOTE:
This way you can insert sq (or two, three of them) anywhere in the string and keep the code readable and maintainable...
Why 4 to get 1 ?
-- Some text enclosed within quotes and containing a single quote like
-- "father's house"
-- requires duplication of single quote to result as one single quote
Select '... father''s house ...' as SAMPLE From Dual;
| SAMPLE |
|---|
| ... father's house ... |
-- if you remove all other text but the single quotes
-- you will end up with 2 outer enclosing single quotes and 2 inner ones resulting as just one:
Select '''' as SAMPLE From Dual;
| SAMPLE |
|---|
| ' |
fiddle
There are a couple of pl/sql techniques that will simplify your code and reduce the chances of running into issues like yours. As others have indicated, the EXECUTE IMMEDIATE is not needed in this particular case but if you choose to do so anyway, here are some tips.
alternative quoting aka Q-quote syntax
This allows you to use another string delimiter than single quote - thus eliminating the need for escaping quotes (docs)
Example:
BEGIN
-- the "old way"
dbms_output.put_line('this is a quote: '', and here are 2 quotes: ''''');
-- alternative quoting
dbms_output.put_line(q'!this is a quote: ', and here are 2 quotes: ''!');
END;
/
this is a quote: ', and here are 2 quotes: ''
this is a quote: ', and here are 2 quotes: ''
PL/SQL procedure successfully completed.
use bind variables not concatenation
Contatenating variable values in pl/sql and sql is not a good practice. It makes the code vulnerable to sql injection. Moreover it's hard to read and a nightmare to maintain (as you showed). Try to use bind variables wherever possible. It's better coding and a lot more readable.
Example:
DECLARE
l_ename emp.ename%TYPE := 'KING';
l_statement VARCHAR2(4000);
l_result VARCHAR2(4000);
BEGIN
-- without bind variables
l_statement := q'!
SELECT ename ||' earns '|| sal
FROM emp where ename = !' || '''KING''';
EXECUTE IMMEDIATE l_statement INTO l_result;
dbms_output.put_line(l_result);
-- with bind variables
l_statement := q'!
SELECT ename ||' earns '|| sal
FROM emp where ename = :1!';
EXECUTE IMMEDIATE l_statement INTO l_result USING l_ename;
dbms_output.put_line(l_result);
END;
/
KING earns 5000
KING earns 5000
本文标签： sqlHow to print 3939 instead of ampaposStack Overflow
版权声明：本文标题：sql - How to print '' instead of ' - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1736718313a1949327.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论