Inserting clob-data into different table-columns

Question

I have a table named 'uploaded_files' that stores *csv.files which are uploaded via dropzone (https://github.com/Dani3lSun/apex-plugin-dropzone) by a user in my webapplication. The content of that files is stored in a tablecolumn of datatype blob.

To insert the file-content I am using a package called 'csv_util_pkg' (https://github.com/mortenbra/alexandria-plsql-utils). I managed to convert the blob-datatype to clob-datatype and store the clob-data into another table called x_dump. X_Dump Table As you can see in the picture the clob_values are separated and stored in 'c001' and 'c002'. This is done by a function of the csv_util_pkg.

Now I want to take those two values and store them in a table called 'fahrzeug' but this doesn't work and I really don't know why. I am getting the following error code

ORA-01400: Einfügen von NULL in ("RESSOURCE_U"."FAHRZEUG"."VNUMMER") nicht möglich
ORA-06512: in Zeile 14
01400. 00000 -  "cannot insert NULL into (%s)"
*Cause:    An attempt was made to insert NULL into previously listed objects.
*Action:   These objects cannot accept NULL values.

And here is my code:

DECLARE
    file_content   NUMBER (10);
    l_clob         CLOB;
BEGIN
    SELECT TO_CLOB (
               UTL_RAW.CAST_TO_VARCHAR2 (DBMS_LOB.SUBSTR (FILE_BLOB, 2000)))
      INTO l_clob
      FROM UPLOADED_FILES
     WHERE UPLOADED_FILES.FILENAME LIKE 'A_%_Fahrzeug.csv';

    INSERT INTO X_DUMP (CLOB_VALUE, DUMP_ID)
         VALUES (l_clob, 1);

    INSERT INTO fahrzeug (vnummer, baureihe)
        SELECT c001, c002
          FROM x_dump d, TABLE (csv_util_pkg.clob_to_csv (d.clob_value, ';'));

    COMMIT;
END;

vnummer is the primary key of my table 'fahrzeug' and thus we can not insert null. But I don't understand that because c001 and c002 contain values so it can't be null..

Thanks for your help!

kara kara · Accepted Answer · 2018-10-11T14:55:48

Did you debug or output the c001 during runtime? Try this:

for tmp IN (select c001 from x_dump d, table(csv_util_pkg.clob_to_csv(d.clob_value, ';')))
LOOP
    DBMS_OUTPUT.put_line ('vnummer: ' || tmp.c001);
END LOOP;

Inserting clob-data into different table-columns

1 Answers