0

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

  • Is your DB really ASCII (i.e. US7ASCII)? ASCII I s strict subset of UTF-8, so each ASCII File (or string) is identical to its UTF-8 encoded file. – Wernfried Domscheit May 29 '17 at 12:31
  • Oracle NLS_CHARACTERSET is WE8ISO8859P1 – Ajay Takur May 30 '17 at 5:40
1

UTL_FILE.PUT_LINE_NCHAR Procedure

This procedure writes the text string stored in the buffer parameter to the open file identified by the file handle. With this function, you can write a text file in Unicode instead of in the database character set.

So you could try something like this:

-- Write the row to the file
UTL_FILE.PUT_LINE_NCHAR(file, TO_NCHAR(row_value));
  • Error Procedure execution failed ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at "SYS.UTL_FILE", line 745 – Ajay Takur May 25 '17 at 8:47
  • @Ajay Takur Did you replace UTL_FILE.FOPEN with UTL_FILE.FOPEN_NCHAR? – Balazs Papp May 25 '17 at 8:50
  • Yes i did replaced please check the above changed script – Ajay Takur May 25 '17 at 9:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.