你能帮我在 Oracle 中编写一个过程来将数据从表中假脱机到 CSV 文件吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1028368/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can you help me write a procedure in Oracle to spool data from a table to a CSV file?
提问by DCookie
I am writing a procedure to create a CSV file with the data in an Oracle table. I used "spool filename;"but an error is coming. Can I use spoolin PL/SQL?
我正在编写一个过程，用 Oracle 表中的数据创建一个 CSV 文件。我用过，"spool filename;"但出现错误。我可以spool在 PL/SQL 中使用吗？
回答by Andrew not the Saint
I think that there are better ways to implement this on Oracle 10g/11g, but this should work fine on Oracle 9i or higher:
我认为有更好的方法可以在 Oracle 10g/11g 上实现这一点，但这在 Oracle 9i 或更高版本上应该可以正常工作：
CREATE OR REPLACE PROCEDURE prc_file_mult_column_generate(
p_file_dir VARCHAR2, -- mandatory (Oracle directory name)
p_file_name VARCHAR2, -- mandatory
p_sql_query VARCHAR2, -- Multiple column SQL SELECT statement that needs to be executed and processed
p_delimiter CHAR -- column delimiter
)
AS
l_cursor_handle INTEGER;
l_dummy NUMBER;
l_col_cnt INTEGER;
l_rec_tab DBMS_SQL.DESC_TAB;
l_current_col NUMBER(16);
l_current_line VARCHAR2(2047);
l_column_value VARCHAR2(300);
l_file_handle UTL_FILE.FILE_TYPE;
l_print_text VARCHAR2(100);
l_record_count NUMBER(16) := 0;
BEGIN
/* Open file for append*/
l_file_handle := UTL_FILE.FOPEN(p_file_dir, p_file_name, 'a', 2047); --Append Mode, 2047 chars per line max, possibly increasable
l_cursor_handle := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(l_cursor_handle, p_sql_query, DBMS_SQL.native);
l_dummy := DBMS_SQL.EXECUTE(l_cursor_handle);
/* Output column names and define them for latter retrieval of data */
DBMS_SQL.DESCRIBE_COLUMNS(l_cursor_handle, l_col_cnt, l_rec_tab); -- get column names
/* Append to file column headers */
l_current_col := l_rec_tab.FIRST;
IF (l_current_col IS NOT NULL) THEN
LOOP
DBMS_SQL.DEFINE_COLUMN(l_cursor_handle, l_current_col, l_column_value, 300);
l_print_text := l_rec_tab(l_current_col).col_name || p_delimiter;
UTL_FILE.PUT (l_file_handle, l_print_text);
l_current_col := l_rec_tab.NEXT(l_current_col);
EXIT WHEN (l_current_col IS NULL);
END LOOP;
END IF;
UTL_FILE.PUT_LINE (l_file_handle,' ');
/* Append data for each row */
LOOP
EXIT WHEN DBMS_SQL.FETCH_ROWS(l_cursor_handle) = 0; -- no more rows to be fetched
l_current_line := '';
/* Append data for each column */
FOR l_current_col IN 1..l_col_cnt LOOP
DBMS_SQL.COLUMN_VALUE (l_cursor_handle, l_current_col, l_column_value);
l_print_text := l_column_value || p_delimiter;
l_current_line := l_current_line || l_column_value || p_delimiter;
END LOOP;
l_record_count := l_record_count + 1;
UTL_FILE.PUT_LINE (l_file_handle, l_current_line);
END LOOP;
UTL_FILE.FCLOSE (l_file_handle);
DBMS_SQL.CLOSE_CURSOR(l_cursor_handle);
EXCEPTION
WHEN OTHERS THEN
-- Release resources
IF DBMS_SQL.IS_OPEN(l_cursor_handle) THEN
DBMS_SQL.CLOSE_CURSOR(l_cursor_handle);
END IF;
IF UTL_FILE.IS_OPEN (l_file_handle) THEN
UTL_FILE.FCLOSE (l_file_handle);
END IF;
--RAISE ;
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.format_error_stack);
END;
/
回答by akf
spool is a sqlplus command. it cannot be used in pl/sql.
spool 是一个 sqlplus 命令。它不能在 pl/sql 中使用。
it seems that you have been trying a variety of ways to get oracle to do your formatting and file saving. why not have your program that is calling the proc do this work for you?
您似乎一直在尝试各种方法来让 oracle 进行格式化和文件保存。为什么不让调用 proc 的程序为您完成这项工作？
回答by Jonathan
If you only need the data in a cvs file you can do this:
如果您只需要 cvs 文件中的数据，您可以这样做：
create a sql file with the query like this:
使用如下查询创建一个 sql 文件：
set feedback off verify off heading off pagesize 0
select field1 || ',' || field2 ... from table;
quit;
/
then call sqlplus from a terminal like this:
然后从这样的终端调用 sqlplus：
sqlplus -S user/password @file.sql> cvsfile.cvs
回答by tuinstoel
Creating an ascii file: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:459020243348and http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:68212348056and http://www.oracle-developer.net/display.php?id=425.
创建一个 ascii 文件：http: //asktom.oracle.com/pls/asktom/f ?p=100:11:0 ::::P11_QUESTION_ID: 459020243348和http://asktom.oracle.com/pls/asktom/ f?p=100:11:0::::P11_QUESTION_ID:68212348056和http://www.oracle-developer.net/display.php?id=425。
回答by Tony Andrews
No, SPOOL is a SQL Plus command so you would have to do this in SQL Plus:
不，SPOOL 是 SQL Plus 命令，因此您必须在 SQL Plus 中执行此操作：
spool myfile.txt
exec myproc
spool off
You would probably also need to set some values before starting the process e.g.
您可能还需要在开始该过程之前设置一些值，例如
set pagesize 0 linesize 1000 trimspool on
... to get the correct formatting.
...以获得正确的格式。
回答by DCookie
Here are a couple of links you might find helpful:
以下是您可能会觉得有用的几个链接：
