I have an CSV file with WE8ISO8859P1
encoding generated from Oracle Stored Procedure. Is there any script line to define the output of CSV
file in UTF-8 encoded format?
CREATE OR REPLACE
PROCEDURE export_main(dir VARCHAR2, file_name VARCHAR2)
IS
select_stmt VARCHAR2(100) := 'SELECT MTYPE || '','' || MNO FROM MAIN';
cur INTEGER;
file UTL_FILE.FILE_TYPE;
row_value VARCHAR2(4000);
ret INTEGER;
BEGIN
-- Open a cursor for the specified SELECT statement
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur, select_stmt, DBMS_SQL.NATIVE);
ret := DBMS_SQL.EXECUTE(cur);
-- All columns were concatenated into single value in SELECT
DBMS_SQL.DEFINE_COLUMN(cur, 1, row_value, 4000);
-- Open the file for writing
--file := UTL_FILE.FOPEN(UPPER(dir), file_name, 'w', 32767);
file := UTL_FILE.FOPEN_NCHAR(UPPER(dir), file_name, 'w', 32767);
-- Export rows one by one
LOOP
ret := DBMS_SQL.FETCH_ROWS(cur);
EXIT WHEN ret = 0;
-- Get the value
row_value := NULL;
DBMS_SQL.COLUMN_VALUE(cur, 1, row_value);
-- Write the row to the file
-- UTL_FILE.PUT_LINE(file, row_value);
UTL_FILE.PUT_LINE_NCHAR(file, TO_NCHAR(row_value));
END LOOP;
UTL_FILE.FCLOSE(file);
DBMS_SQL.CLOSE_CURSOR(cur);
EXCEPTION WHEN NOT_LOGGED_ON THEN
DBMS_OUTPUT.PUT_LINE ('A program issues a database call without being connected to Oracle.');
END;
Oracle database is encoded with WE8ISO8859P1
and postgres DB is encoded with UTF-8
I need to import CSV file into Postgres DB table using stored procedure where in postgres by default encoding is UTF-8
Best Answer
UTL_FILE.PUT_LINE_NCHAR Procedure
So you could try something like this: