Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

CLOB and PLSQL Table and Collections

634176May 21 2009 — edited May 22 2009
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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 19 2009
Added on May 21 2009
6 comments
2,451 views