从网上摘抄下面代码,目的是为了讲查询数据导出为CSV文件create or replace procedure out-to-csv(P_QUERY IN VARCHAR2,P_DIR IN VARCHAR2,P_FILENAME IN VARCHAR2) is L_OUTPUT UTL_FILE.FILE_TYPE;
L_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
L_COLUMNVALUE VARCHAR2(4000);
L_STATUS INTEGER;
L_COLCNT NUMBER := 0;
L_SEPARATOR VARCHAR2(1);
L_DESCTBL DBMS_SQL.DESC_TAB;
P_MAX_LINESIZE NUMBER := 32000;
begin
--OPEN FILE
L_OUTPUT := UTL_FILE.FOPEN(P_DIR, P_FILENAME, 'W', P_MAX_LINESIZE); --DEFINE DATE FORMAT
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS'''; --OPEN CURSOR
DBMS_SQL.PARSE( L_THECURSOR, P_QUERY, DBMS_SQL.NATIVE );
DBMS_SQL.DESCRIBE_COLUMNS( L_THECURSOR, L_COLCNT, L_DESCTBL ); --DUMP TABLE COLUMN NAME
FOR I IN 1 .. L_COLCNT LOOP
UTL_FILE.PUT( L_OUTPUT, L_SEPARATOR || '"' || L_DESCTBL(I).COL_NAME || '"' );
DBMS_SQL.DEFINE_COLUMN( L_THECURSOR, I, L_COLUMNVALUE, 4000 );
L_SEPARATOR := ',';
END LOOP;
UTL_FILE.NEW_LINE( L_OUTPUT ); --EXECUTE THE QUERY STATEMENT
--L_STATUS := DBMS_SQL.EXECUTE(L_THECURSOR); --DUMP TABLE COLUMN VALUE
WHILE ( DBMS_SQL.FETCH_ROWS(L_THECURSOR) > 0 ) LOOP
L_SEPARATOR := '';
FOR I IN 1 .. L_COLCNT LOOP
DBMS_SQL.COLUMN_VALUE( L_THECURSOR, I, L_COLUMNVALUE );
UTL_FILE.PUT( L_OUTPUT, L_SEPARATOR || '"' ||
TRIM(BOTH ' ' FROM REPLACE(L_COLUMNVALUE,'"','""')) || '"');
L_SEPARATOR := ',';
END LOOP;
UTL_FILE.NEW_LINE( L_OUTPUT );
END LOOP; --CLOSE CURSOR
DBMS_SQL.CLOSE_CURSOR(L_THECURSOR); --CLOSE FILE
UTL_FILE.FCLOSE( L_OUTPUT );
EXCEPTION
WHEN OTHERS THEN
RAISE;
end out-to-csv;
我在pl sql里调试时,报:
Compilation errors for PROCEDURE MCDBA.OUT
Error: PLS-00103: 出现符号 "-"在需要下列之一时:
( ; is with authid as cluster
compress order using compiled wrapped external deterministic
parallel_enable pipelined
Line: 1
Text: create or replace procedure out-to-csv(P_QUERY IN VARCHAR2,P_DIR IN VARCHAR2,P_FILENAME IN VARCHAR2) isError: Hint: Variable 'L_STATUS' is declared but never used in 'out'
Line: 5
Text: L_STATUS INTEGER;请问这段代码哪里出错了
L_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
L_COLUMNVALUE VARCHAR2(4000);
L_STATUS INTEGER;
L_COLCNT NUMBER := 0;
L_SEPARATOR VARCHAR2(1);
L_DESCTBL DBMS_SQL.DESC_TAB;
P_MAX_LINESIZE NUMBER := 32000;
begin
--OPEN FILE
L_OUTPUT := UTL_FILE.FOPEN(P_DIR, P_FILENAME, 'W', P_MAX_LINESIZE); --DEFINE DATE FORMAT
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS'''; --OPEN CURSOR
DBMS_SQL.PARSE( L_THECURSOR, P_QUERY, DBMS_SQL.NATIVE );
DBMS_SQL.DESCRIBE_COLUMNS( L_THECURSOR, L_COLCNT, L_DESCTBL ); --DUMP TABLE COLUMN NAME
FOR I IN 1 .. L_COLCNT LOOP
UTL_FILE.PUT( L_OUTPUT, L_SEPARATOR || '"' || L_DESCTBL(I).COL_NAME || '"' );
DBMS_SQL.DEFINE_COLUMN( L_THECURSOR, I, L_COLUMNVALUE, 4000 );
L_SEPARATOR := ',';
END LOOP;
UTL_FILE.NEW_LINE( L_OUTPUT ); --EXECUTE THE QUERY STATEMENT
--L_STATUS := DBMS_SQL.EXECUTE(L_THECURSOR); --DUMP TABLE COLUMN VALUE
WHILE ( DBMS_SQL.FETCH_ROWS(L_THECURSOR) > 0 ) LOOP
L_SEPARATOR := '';
FOR I IN 1 .. L_COLCNT LOOP
DBMS_SQL.COLUMN_VALUE( L_THECURSOR, I, L_COLUMNVALUE );
UTL_FILE.PUT( L_OUTPUT, L_SEPARATOR || '"' ||
TRIM(BOTH ' ' FROM REPLACE(L_COLUMNVALUE,'"','""')) || '"');
L_SEPARATOR := ',';
END LOOP;
UTL_FILE.NEW_LINE( L_OUTPUT );
END LOOP; --CLOSE CURSOR
DBMS_SQL.CLOSE_CURSOR(L_THECURSOR); --CLOSE FILE
UTL_FILE.FCLOSE( L_OUTPUT );
EXCEPTION
WHEN OTHERS THEN
RAISE;
end out-to-csv;
我在pl sql里调试时,报:
Compilation errors for PROCEDURE MCDBA.OUT
Error: PLS-00103: 出现符号 "-"在需要下列之一时:
( ; is with authid as cluster
compress order using compiled wrapped external deterministic
parallel_enable pipelined
Line: 1
Text: create or replace procedure out-to-csv(P_QUERY IN VARCHAR2,P_DIR IN VARCHAR2,P_FILENAME IN VARCHAR2) isError: Hint: Variable 'L_STATUS' is declared but never used in 'out'
Line: 5
Text: L_STATUS INTEGER;请问这段代码哪里出错了
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货