I have a CSV file stored in my table as a blob. (this was uploaded by user as an XLS spreadsheet, and I've converted it into csv using a servlet. I'm using something like this in my procedure to convert it into a clob:
DBMS_LOB.createtemporary (v_content, TRUE);
DBMS_LOB.converttoclob (v_content, v_blob_content, DBMS_LOB.lobmaxsize, v_dest_offset, v_src_offset, 0, v_lang_context, v_warning );
here,
v_blob_content BLOB;
v_dest_offset INTEGER := 1;
v_src_offset INTEGER := 1;
v_lang_context INTEGER := 0;
v_warning INTEGER := 0;
v_blob_content is my csv content from the table.
After this conversion to clob, I'm trying to parse the data into a PLSQL table type collection, to display a preview to the user. The plsql table is something like this:
type my_rec is record(
SEQ_NUM VARCHAR2(200) NULL,
serial_num VARCHAR2(200) NUll,
p_id VARCHAR2(200) NUll,
p_amt VARCHAR2(200) NUll,
p_name VARCHAR2(200) NUll,
p_date date
);
TYPE my_tab IS TABLE OF my_rec INDEX BY BINARY_INTEGER;
The CSV file contains about 20 rows and 26 columns. Out of that, the first 12 rows are header data. The only thing from the header that I need to parse into my PLSQL table is on row 1 column 2 of spreadsheet ("p_id" in plsql table) and row 4 column 24 ("p_date" in plsql table). The rest of the data to be parsed starts from line#13 of the clob, and may not necessarily be in the order of the "my_rec" record type.
Can anyone please help me in achieving this requirement?
Previously, I was using a database object type instead of record type, and was using pipelined function to parse the data. However, requirements have changed and I'm no longer permitted to use object type. I HAVE TO use record and plsql table of records.
I had a similar thread before when I was using object type. Here:
3474583
Any help appreciated.