Why does dbms not print all the lines in a single line in plsql

Question

I have a filename book.txt which has lines as below:

$ cat book.txt

English
French
Russian

In pl sql code, I need to read this file and produce the output in a single line with comma separated as below:

English, French, Russian

Kindly help!

Answer 1

The best way to do this is to create an external table, then just process the data in the file using normal DML:

CREATE OR REPLACE DIRECTORY YOUR_DIR AS '/directory/where/your/file/is/located';

CREATE TABLE BOOK_TABLE
  (BOOK_LANGUAGE  VARCHAR2(2000))
  ORGANIZATION EXTERNAL
    (DEFAULT DIRECTORY YOUR_DIR
     LOCATION ('book.txt'));

DECLARE
  bFirst_row   BOOLEAN := TRUE;
BEGIN
  FOR aRow IN (SELECT * FROM BOOK_TABLE)
  LOOP
    IF NOT bFirst_row THEN
      DBMS_OUTPUT.PUT(', ');
    END IF;

    bFirst_row := FALSE;

    DBMS_OUTPUT.PUT(aRow.BOOK_LANGUAGE);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('');
END;

You'll need to change the directory specified in the creation of YOUR_DIR as I didn't know where this file might be located on your computer. Or just add a comment below this answer with the name of the directory.

Best of luck!