Parsing CSV (Comma Separated Values) in Oracle
I would like to pass a set of record IDs to an Oracle procedure using a comma separated string. I want to put this in a temp table and then join this in further processing. How can i do this?
Better approaches than CSV, it would be great to hear about this. I am using ODP.Net for data access.
a source to share
I don't know anything about ODP.net, but why concatenate all post IDs into a CSV string?
A better approach might be to create an array parameter in your procedure and pass an array of identifiers. I suppose it depends where you get your list of ids from and whether they start as an array or CSV string?
Here's an example of using an array type and joining it to your query without using a temporary table (you can do something similar with an IN list, but it's harder to link):
create or replace type my_test_type as object (id integer);
create or replace type my_test_type_a as table of my_test_type;
create or replace procedure my_test_proc(i_ids in my_test_type_a)
is
begin
for row in (
select a.l from
(
select level l from dual connect by level <= 10
) a, table(i_ids) b
where a.l = b.id
) loop
dbms_output.put_line(row.l);
end loop;
end;
/
Note that the query in the test procedure uses the "table" function to convert the passed array to a table in the query that you can actually join with other real tables.
Also not that the "a" subquery uses a trick to create a fake test table containing 10 rows with values from 1 to 10 (this is just test data to prove it works).
Using the following block, we can test how the code works:
declare
v_id my_test_type;
v_ids my_test_type_a;
begin
v_id := my_test_type(1);
v_ids := my_test_type_a();
for i in 1.. 5 loop
v_id := my_test_type(i);
v_ids.extend;
v_ids(i) := v_id;
end loop;
my_test_proc(v_ids);
end;
/
As long as dbms_output is on, this should print 1 - 5 since the test data table is connected to your array!
a source to share
How about using IN? How in select ... where id IN (...list-of-ids-here...)
?
Also, I suggest manually inserting the values into the temp table before you call the procedure, or write a custom procedure that does the insertion for you, so you can call the original procedure in the second step. Thus, you are much more flexible than when you do everything in one method.
a source to share
SQL LOADER is great for this task
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96652/ch03.htm#1656
You can specify several formats for dates and there are many useful options for importing data into Oracle from CSV.
a source to share