Form a new table from CSV data stored as a BLOB - Oracle DB

Question

Is there a way to create a new table(T1) from the CSV_data(BLOB) column of table(T2) in oracle SQL ?

I am able to do this with java, but it has performance issues as I have to first get the blob contents to java layer parse it and then create table and do batch insert. So, I want to know if we can do this directly at the database side itself ?

Many thanks in advance!

You could do the same in the JVM running in the database (which would prevent to copy the data between the database and your client). You need only get the appropriate privileges from your DBA! — Marmite Bomber
– Marmite Bomber, Commented May 27, 2021 at 12:55
@jarlh I need to copy it to a table so that I can do additional inserts when next time same csv file is uploaded with next set of data. Also I will do operations like read/update. — crazy_code
– crazy_code, Commented May 27, 2021 at 12:56
With a CREATE PROCEDURE privilege you can CREATE JAVA stored procedure where you can set up a JDBC connection to the database (internal driver) and read the BLOB and insert to the new table without passing the data to client and back. Your user gets the privs once, you can execute it any times... — Marmite Bomber
– Marmite Bomber, Commented May 27, 2021 at 13:26
Why is the csv file being pulled into the database and saved as a blob in the first place? If you are going to parse it out and save the data in a conventional table, why not just use sqlldr to load that conventional table straight from the external csv file? — EdStevens
– EdStevens, Commented May 27, 2021 at 14:07

Vahram Danielyan · Accepted Answer · 2021-05-27 14:56:04Z

1

Also you can work with external tables.

It means that you can create a directory in Oracle, And use your CSV file such a table WITHOUT DML.

OracleDocsExternalTable

The basic command is:

CREATE OR REPLACE DIRECTORY EMP_DIR
AS 'E:\external';

SELECT * FROM ALL_DIRECTORIES
WHERE DIRECTORY_NAME='EMP_DIR';

CREATE TABLE EMP_LOAD_ext
     (EMPLOYEE_NUMBER      NUMBER,
      FNAME   VARCHAR2(100),
      LNAME   VARCHAR2(100)
      )
    ORGANIZATION EXTERNAL
      (TYPE ORACLE_LOADER
      DEFAULT DIRECTORY EMP_DIR
      ACCESS PARAMETERS
        (RECORDS DELIMITED BY NEWLINE
         FIELDS TERMINATED BY ','
        )
      LOCATION ('old_emp_data.csv')
     )
     reject limit unlimited;

I am not sure if you need something more to manage Clob an Blobs.

I found this other information.

ClobsAndBlobs

answered May 27, 2021 at 14:56

Vahram Danielyan

3094 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

crazy_code Over a year ago

Thanks for your response! Unfortunately I need DML(update) for my usecase.

Vahram Danielyan Over a year ago

Maybe from this point you can copy it into table of your Instance... I think you can get some solution. Good Luck!

Collectives™ on Stack Overflow

Form a new table from CSV data stored as a BLOB - Oracle DB

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related