sql - 在 oracle 文件中添加引号
问题描述
我有一个 oracle proc,它向表数据添加制表符空格并将其打印到 oracle 文件。但现在我还需要一些列周围的引号,一些列不需要任何引号。
这个过程看起来像这样..
create or replace PROCEDURE usp_create_nestor AS
v_column_name VARCHAR2(4000);
TYPE t_bulk_collect_test_tab IS TABLE OF VARCHAR2(4000);
v_tab t_bulk_collect_test_tab;
clob_var CLOB;
v_file utl_file.file_type;
ref_cur SYS_REFCURSOR;
v_qry VARCHAR2(500);
v_flag NUMBER := 0;
v_year VARCHAR2(80);
BEGIN
SELECT LISTAGG ( column_name,'||CHR(9)||' ) WITHIN GROUP (ORDER BY column_id)
INTO v_column_name FROM all_tab_cols WHERE owner = 'schema' AND table_name LIKE '%tablename%';
v_qry := 'SELECT '|| v_column_name || ' from tablename';
v_flag := 1;
v_file := utl_file.fopen('filename.txt','w');
OPEN ref_cur FOR v_qry;
LOOP
FETCH ref_cur BULK COLLECT INTO v_tab LIMIT 100;
FOR i IN 1..v_tab.count LOOP
utl_file.put_line(v_file,v_tab(i)); -- WRITE INTO FILE
END LOOP;
EXIT WHEN v_tab.count = 0;
END LOOP;
CLOSE ref_cur;
utl_file.fclose(v_file); -- CLOSE FILE
v_flag := 2;
dbms_output.put_line('Success');
UTL_FILE.FCLOSE_ALL;
END;
文件输出如下所示:
ABCDEF
文件输出应如下所示:
AB "C" D "E" "F"
解决方案
将双引号括在单引号中并连接。
select '"'||'test'||'"' test from dual;
输出:
"test"