Home » SQL & PL/SQL » SQL & PL/SQL » Clob, PLSQL table type, Collections (Oracle 10g, TOAD, Windows XP)
Clob, PLSQL table type, Collections [message #404374] Thu, 21 May 2009 10:11 Go to next message
scorpio4frenz
Messages: 48
Registered: October 2008
Member
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:

http://www.orafaq.com/forum/m/402565/131319/#msg_402565


Any help appreciated.

[Updated on: Thu, 21 May 2009 10:35] by Moderator

Report message to a moderator

Re: Clob, PLSQL table type, Collections [message #404375 is a reply to message #404374] Thu, 21 May 2009 10:20 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
> 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.
Who/what determines the actual order of the data?
How does the data get into the proper data types?
Re: Clob, PLSQL table type, Collections [message #404379 is a reply to message #404375] Thu, 21 May 2009 10:28 Go to previous message
scorpio4frenz
Messages: 48
Registered: October 2008
Member
BlackSwan wrote on Thu, 21 May 2009 10:20
> 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.
Who/what determines the actual order of the data?
How does the data get into the proper data types?


The problem is that the "my_rec" and the subsequent table have been designed for a text file upload. The text file is in a fixed format and simple string functions can be used to grab each data field using the start position and end position for each field. In case of excel, the format is not the same as the text file. I could revise my template to match the structure of "my_rec", but still there are 2 fields in "my_rec" that appear only once in the excel file in the header part, and the rest of the fields are in the data area.

I'm confused about how I can use the "my_rec" in my situation.

Sorry to be so confusing..please let me know if you have questions and I'll try to explain better!
Previous Topic: Float
Next Topic: Fifo , Lifo and Average Method Inventory
Goto Forum:
  


Current Time: Fri Dec 09 09:20:02 CST 2016

Total time taken to generate the page: 0.14302 seconds