I currently have an Excel spreadsheet that I dumped to a .csv file. It has about 7 items in it. I take the first 3 items (varchar2 fields) and use it in a regular cursor to check against some tables in our database.
Now one of the things that I want to do is to have those 3 fields loaded into memory (not a physical table) and it looks like nested tables are the way to go. I'm reading out of the .csv file, and the number of items in that file can change. I also want an "array" of the first 3 items in memory. I want to read a record out of the database tables I have, and read the nested table file to find that record (or not). I want to do this in PL/SQL, in a procedure.
I found on the forum at
3537733 something close to what I'm looking at.
I have:
CREATE OR REPLACE TYPE crane_data AS OBJECT
(field1 VARCHAR2(30),
field2 VARCHAR2(50),
field3 VARCHAR2(150));
CREATE TYPE crane_data_type AS TABLE OF crane_data;
The problem is, at step 3 of the above URL, I don't want to create a table. I just want to have the object/nested table in memory. A lot of the examples I see put the data into a table.
For the next couple of steps, I'm not sure how the insert and select would be done, since it looks like they're done out of the table, and not out of memory/nested table. Any examples any one is willing to give? Or some further explanation if I'm not understanding nested tables correctly?
Thanks!
Victoria