0

I have a table that stores all the relative data for a file (blob). From apex, i load into the table csv files only and it accepts it. With that csv file i have stored in there, i want to select the contents from the file which is stored in the column.

In others words, I have a csv file (employees.csv) stored in a table (Table A) column (File_upload). I want to access the contents of the csv file without exporting it but simply from a sql query.

It is an oracle database. The table includes ID (number), file_name (varchar2), file_uploaded (blob)

I have a sample i tried but its not working, this includes:

  select csv.*
  from tableA d, table(csv_util_pkg.clob_to_csv(d.file_uploaded)) csv
  where d.id= 1;

It is not necessary to fix this code, alternatives are very welcomed. Thank you in advance!

Basudev Singh
  • 79
  • 2
  • 13
  • 1
    Did you have a look at [External Tables](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sutil/oracle-external-tables-concepts.html)? – Wernfried Domscheit Apr 08 '21 at 20:44
  • I looked at it but it is not how i want my system to work. I want to do everything right from apex and sqldeveloper. I read that the external table requires you to make a directory on the server which i do not have access to. I may not fully understand it. Can you explain it? – Basudev Singh Apr 08 '21 at 20:54
  • Yes, you need access to the file system of the database server. However, I assume you would need this for any solution. Maybe talk to your DB admin and create some NFS-Share or similar. – Wernfried Domscheit Apr 08 '21 at 20:57
  • What does this have to do with SQL Developer? – William Robertson Apr 08 '21 at 21:40
  • If the file cannot be on the database server then where will it be? – William Robertson Apr 08 '21 at 21:41
  • I am sorry, i dont think you understand my question. I want to store the files in a database table. After the files are stored in a table, I want to access them (access in the sense of what the file contain) and use the contents. I do not want to store them on the server in a directory. I have tried to used the apex_data_parser to solve my main motive of the problem but it did not work that is why I am resorting to this. – Basudev Singh Apr 08 '21 at 23:16

1 Answers1

1

I think your problem ist the data type. You store the file in blob and your table function needs a clob.

I don't know your package csv_util_pkg, but i think the following is a solution for you.

Setup:

I used the Package csv_util_pkg from https://github.com/mortenbra/alexandria-plsql-utils

create table

create table tableA (id number(10), 
                     file_name varchar2(255),
                     file_uploaded blob);

upload data

utl_raw.cast_to_raw() to create blob data

insert into tableA (id,file_name, file_uploaded) 
values (
1,
'employees.csv',
utl_raw.cast_to_raw(
'"EMPLOYEE_ID","FIRST_NAME","LAST_NAME","EMAIL","PHONE_NUMBER","HIRE_DATE","JOB_ID","SALARY","COMMISSION_PCT","MANAGER_ID","DEPARTMENT_ID"
100,"Steven","King","SKING","515.123.4567",17.06.2003,"AD_PRES",24000,,,90
101,"Neena","Kochhar","NKOCHHAR","515.123.4568",21.09.2005,"AD_VP",17000,,100,90
102,"Lex","De Haan","LDEHAAN","515.123.4569",13.01.2001,"AD_VP",17000,,100,90')
);

create helper function

get it from https://stackoverflow.com/a/12854297/12277315

create function clobfromblob(p_blob blob) return clob is
      l_clob         clob;
      l_dest_offsset integer := 1;
      l_src_offsset  integer := 1;
      l_lang_context integer := dbms_lob.default_lang_ctx;
      l_warning      integer;

   begin

      if p_blob is null then
         return null;
      end if;

      dbms_lob.createTemporary(lob_loc => l_clob
                              ,cache   => false);

      dbms_lob.converttoclob(dest_lob     => l_clob
                            ,src_blob     => p_blob
                            ,amount       => dbms_lob.lobmaxsize
                            ,dest_offset  => l_dest_offsset
                            ,src_offset   => l_src_offsset
                            ,blob_csid    => dbms_lob.default_csid
                            ,lang_context => l_lang_context
                            ,warning      => l_warning);

      return l_clob;

   end;
/

use helper function

select d.file_name,  csv.*
  from tableA d, table(csv_util_pkg.clob_to_csv(clobfromblob(d.file_uploaded))) csv
  where d.id= 1;

Result

note, my example is limited to 20 columns. See type t_csv_line in types.sql

christris
  • 31
  • 3