Pouvez-vous m'aider à écrire une procédure dans Oracle pour transférer des données d'une table vers un fichier CSV?
j'écris une procédure pour créer un fichier CSV avec les données dans une table Oracle. J'ai utilisé "spool filename;"
mais une erreur arrive. Puis-je utiliser spool
dans PL/SQL?
6 réponses
je pense qu'il y a de meilleures façons de mettre en œuvre ceci sur Oracle 10g / 11g, mais cela devrait fonctionner très bien sur Oracle 9i ou plus:
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;
/
spool est une commande sqlplus. il ne peut pas être utilisé en pl/sql.
il semble que vous avez essayé une variété de façons d'obtenir oracle pour faire votre formatage et la sauvegarde de fichier. pourquoi ne pas demander à votre programme qui appelle le proc de faire ce travail pour vous?
si vous n'avez besoin que des données dans un fichier cvs, vous pouvez le faire:
créer un fichier sql avec la requête comme ceci:
set feedback off verify off heading off pagesize 0
select field1 || ',' || field2 ... from table;
quit;
/
puis appelez sqlplus à partir d'un terminal comme celui-ci:
sqlplus -S user/password @file.sql> cvsfile.cvs
Non, SPOOL est une commande SQL Plus donc vous devez faire ceci en SQL Plus:
spool myfile.txt
exec myproc
spool off
vous auriez probablement aussi besoin de définir quelques valeurs avant de commencer le processus par exemple
set pagesize 0 linesize 1000 trimspool on
... pour obtenir la mise en forme correcte.
création d'un fichier ascii: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:459020243348 et http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:68212348056 et http://www.oracle-developer.net/display.php?id=425 .