henderxe
asked on
Oracle Stored Procedure -- How to add additional INPUT
Hello:
I use the following Oracle stored procedure to process large files, and it takes only one INPUT parameter (e.g. ID_NO), which works fine.
What I'd like it to do, however, is have it accept multiple INPUT parameters (e.g. ID_NO, ID_NO_2, ID_NO_3, etc), but leave the rest of the process in tact..
Stored procedure follows:
------------------ START STORED PROCEDURE -------------------------- -----
create or replace
procedure AddIdLists (id_no in clob) is
id_insert_val varchar2(8000);
id_pos int := 1;
id_pos1 int := 1;
begin
EXECUTE IMMEDIATE ' truncate table tempIdlists';
if dbms_lob.instr(id_no, ',', id_pos) = 0 then
execute immediate ' insert into tempIdlists(vals) values(:v)' using to_char(id_pos);
else
while id_pos < dbms_lob.getlength(id_no) loop
id_pos1 := dbms_lob.instr(id_no, ',', id_pos + 1);
if id_pos1 = 0 then -- last value in list
id_pos1 := dbms_lob.getlength(id_no) + 1;
end if;
id_insert_val := dbms_lob.substr(id_no, id_pos1 - id_pos, id_pos);
id_pos := id_pos1 + 1;
execute immediate ' insert into tempIdlists(vals) values(:v)' using id_insert_val;
end loop;
end if;
commit;
end AddIdLists;
------------------ END STORED PROCEDURE -------------------------- -----
Thanks!
I use the following Oracle stored procedure to process large files, and it takes only one INPUT parameter (e.g. ID_NO), which works fine.
What I'd like it to do, however, is have it accept multiple INPUT parameters (e.g. ID_NO, ID_NO_2, ID_NO_3, etc), but leave the rest of the process in tact..
Stored procedure follows:
------------------ START STORED PROCEDURE --------------------------
create or replace
procedure AddIdLists (id_no in clob) is
id_insert_val varchar2(8000);
id_pos int := 1;
id_pos1 int := 1;
begin
EXECUTE IMMEDIATE ' truncate table tempIdlists';
if dbms_lob.instr(id_no, ',', id_pos) = 0 then
execute immediate ' insert into tempIdlists(vals) values(:v)' using to_char(id_pos);
else
while id_pos < dbms_lob.getlength(id_no) loop
id_pos1 := dbms_lob.instr(id_no, ',', id_pos + 1);
if id_pos1 = 0 then -- last value in list
id_pos1 := dbms_lob.getlength(id_no) + 1;
end if;
id_insert_val := dbms_lob.substr(id_no, id_pos1 - id_pos, id_pos);
id_pos := id_pos1 + 1;
execute immediate ' insert into tempIdlists(vals) values(:v)' using id_insert_val;
end loop;
end if;
commit;
end AddIdLists;
------------------ END STORED PROCEDURE --------------------------
Thanks!
What are you asking? It looks like the code already handles a CSV list of input values.
I'm not sure what you're asking either
if you don't want to change the processing, what do you want to do with the extra input parameters if you do add them?
if you don't want to change the processing, what do you want to do with the extra input parameters if you do add them?
just a side note, for performance
you might want to parse your clob into large varchar2 chunks then split the varchar2 pieces with instr/substr
the reason being, lob operations are slow compared to varchar2.
you have to be careful that you don't split a string into pieces though by doing that
you might want to parse your clob into large varchar2 chunks then split the varchar2 pieces with instr/substr
the reason being, lob operations are slow compared to varchar2.
you have to be careful that you don't split a string into pieces though by doing that
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
An extended version of the post above
this will parse a CLOB into a table collection it recognizes lines as well as fields.
so, if you have data like this..
f1,f2,f3
f4,f5,f6
f7
you'll get results like this...
f1
f2
f3
f4
f5
f6
f7
this will parse a CLOB into a table collection it recognizes lines as well as fields.
so, if you have data like this..
f1,f2,f3
f4,f5,f6
f7
you'll get results like this...
f1
f2
f3
f4
f5
f6
f7
CREATE OR REPLACE FUNCTION SDS.clob2tbl(p_clob IN CLOB,
p_field_delimiter IN VARCHAR2 DEFAULT ',',
p_line_delimiter IN VARCHAR2 DEFAULT CHR(13) || CHR(10)
)
RETURN vcarray
PIPELINED
IS
c_chunk_limit CONSTANT INTEGER := 32767;
v_clob_length INTEGER := DBMS_LOB.getlength(p_clob);
v_clob_index INTEGER;
v_chunk VARCHAR2(32767);
v_chunk_length INTEGER;
v_field_start INTEGER;
v_field_index INTEGER;
v_delim_index INTEGER;
v_line_index INTEGER;
v_common_delimiter VARCHAR2(32767)
:= CASE
WHEN LENGTH(p_field_delimiter) <= LENGTH(p_line_delimiter) THEN p_field_delimiter
ELSE p_line_delimiter
END;
BEGIN
v_clob_length := DBMS_LOB.getlength(p_clob);
v_clob_index := 1;
WHILE v_clob_index <= v_clob_length
LOOP
/*
Pull one 32K chunk off the clob at a time.
This is because it's MUCH faster (approx 25 times faster) to use built in functions
on a varchar2 type than to use dbms_lob functions on a clob.
Within a chunk, find the last delimiter and use that as the end
of our chunk. This avoids breaking a field between delimiters at the end
of a chunk where possible. If a chunk has no delimiters, then we must
break it on 32K boundaries due to varchar2 limitations.
*/
v_chunk := DBMS_LOB.SUBSTR(p_clob, c_chunk_limit, v_clob_index);
IF v_clob_index > v_clob_length - c_chunk_limit
THEN
-- if we walked off the end the clob,
-- then the chunk is whatever we picked up at the end
-- delimited or not
v_clob_index := v_clob_length + 1;
ELSE
v_delim_index := INSTR(v_chunk, p_field_delimiter, -1);
v_line_index := INSTR(v_chunk, p_line_delimiter, -1);
IF v_delim_index > v_line_index
THEN
-- Field delimiter found after line delimiter,
-- so use chunk up to last field delimiter
v_chunk := SUBSTR(v_chunk, 1, v_delim_index - 1);
v_clob_index := v_clob_index + v_delim_index + LENGTH(p_field_delimiter) - 1;
ELSIF v_delim_index <= v_line_index AND v_line_index != 0
THEN
-- Line delimiter found after field delimiter,
-- so use chunk up to last line delimiter
v_chunk := SUBSTR(v_chunk, 1, v_line_index - 1);
v_clob_index := v_clob_index + v_line_index + LENGTH(p_line_delimiter) - 1;
ELSE
-- Neither field delimiter nor end of line found in chunk
-- so use entire chunk
v_clob_index := v_clob_index + c_chunk_limit;
END IF;
END IF;
/*
Given a varchar2 chunk, split it into fields
To keep things simple, change all delimiters to be the same.
The common delimiter will be the shorter of the two or field
if they are the same length
*/
IF p_line_delimiter != v_common_delimiter
THEN
v_chunk := REPLACE(v_chunk, p_line_delimiter, v_common_delimiter);
ELSIF p_field_delimiter != v_common_delimiter
THEN
v_chunk := REPLACE(v_chunk, p_field_delimiter, v_common_delimiter);
END IF;
v_field_start := 1;
v_chunk_length := NVL(LENGTH(v_chunk), 0);
WHILE (v_field_start <= v_chunk_length)
LOOP
v_field_index := INSTR(v_chunk, v_common_delimiter, v_field_start);
IF v_field_index = 0 OR v_field_index > 4000
THEN
PIPE ROW (SUBSTR(v_chunk, v_field_start, 4000));
v_field_start := v_field_start + 4000;
ELSE
PIPE ROW (SUBSTR(v_chunk, v_field_start, v_field_index - v_field_start));
v_field_start := v_field_index + LENGTH(v_common_delimiter);
END IF;
END LOOP;
END LOOP;
RETURN;
EXCEPTION
WHEN no_data_needed
THEN
NULL;
END clob2tbl;
as with the str2tbl above, clob2tbl also requires a collection type to be defined
create type vcarray as table of varchar2(4000)
i use 4000 because it's the largest varchar2 supported in SQL
create type vcarray as table of varchar2(4000)
i use 4000 because it's the largest varchar2 supported in SQL
ASKER
Tremendous response, and great comments -- thank you very much!
But first, let me clarify what I'm seeking , and sorry for the confusion!
Currently, the stored procedure takes the output from one big file (contains a list of 10-digit numbers), and stores them in a temporary table (tempidlists). The key word here is "one".
I need to have up to three files processed, and each looped through and added to the table. I'll remove the "truncate table" statement so each file can be appended to the table.
Also, performance of the application in question is an issue, particularly when loading/processing large files, so appreciate the comments.
Not sure if it's even possible to do what I'm suggesting in the stored procedure as written, but was hoping for assistance.
But first, let me clarify what I'm seeking , and sorry for the confusion!
Currently, the stored procedure takes the output from one big file (contains a list of 10-digit numbers), and stores them in a temporary table (tempidlists). The key word here is "one".
I need to have up to three files processed, and each looped through and added to the table. I'll remove the "truncate table" statement so each file can be appended to the table.
Also, performance of the application in question is an issue, particularly when loading/processing large files, so appreciate the comments.
Not sure if it's even possible to do what I'm suggesting in the stored procedure as written, but was hoping for assistance.
What is the format of the one big file?
Is it one long single CSV line, multiple CSV lines, every number on a single line, ???
Is it one long single CSV line, multiple CSV lines, every number on a single line, ???
how about something like this...
PROCEDURE addidlists(id1 IN CLOB,
id2 IN CLOB DEFAULT NULL,
id3 IN CLOB DEFAULT NULL,
id4 IN CLOB DEFAULT NULL,
id5 IN CLOB DEFAULT NULL
)
IS
BEGIN
EXECUTE IMMEDIATE ' truncate table tempIdlists';
IF id1 IS NOT NULL
THEN
INSERT INTO tempidlists(vals)
SELECT COLUMN_VALUE FROM TABLE(clob2tbl(id1));
END IF;
IF id2 IS NOT NULL
THEN
INSERT INTO tempidlists(vals)
SELECT COLUMN_VALUE FROM TABLE(clob2tbl(id2));
END IF;
IF id3 IS NOT NULL
THEN
INSERT INTO tempidlists(vals)
SELECT COLUMN_VALUE FROM TABLE(clob2tbl(id3));
END IF;
IF id4 IS NOT NULL
THEN
INSERT INTO tempidlists(vals)
SELECT COLUMN_VALUE FROM TABLE(clob2tbl(id4));
END IF;
IF id5 IS NOT NULL
THEN
INSERT INTO tempidlists(vals)
SELECT COLUMN_VALUE FROM TABLE(clob2tbl(id5));
END IF;
COMMIT;
END addidlists;
in my code I assumed fields delimited by "," (comma)
and either all data on a single line,
or lines separated by CRLF pairs (chr(13) || chr(10))
if these assumptions are not true then adjust the calls to clob2tbl accordingly
and either all data on a single line,
or lines separated by CRLF pairs (chr(13) || chr(10))
if these assumptions are not true then adjust the calls to clob2tbl accordingly
ASKER
slightwv:
To answer your question, it is a CSV file, as you suggest, with comma delimited values in a single row (e.g. 0000023456,0000938273,0000
0000023456
0000938273
0000093847
sdstuber:
Your assumptions are accurate as well, and it appears that your code may possibly solve my problem. Question: Should I run it as a function, rather than a stored procedure, although I realize a function can be called within a stored procedure?
>>> Should I run it as a function
entirely up to you, there is no significant difference either way.
if you use a function then you'd return a collection type and not use the table
or you'd write to the table but return a reference cursor that queried the table after populating.
entirely up to you, there is no significant difference either way.
if you use a function then you'd return a collection type and not use the table
or you'd write to the table but return a reference cursor that queried the table after populating.
also, minor cosmetic note, the IF NULL checks aren't really needed.
if you pass in a null value to the clob2tbl it simple returns nothing so the inserts won't really do anything.
if you pass in a null value to the clob2tbl it simple returns nothing so the inserts won't really do anything.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Add new parameters with DEFAULT Value, This way the other procedures which calls this will not get affected and you can make the necessary changes in this procedure to act differently if it gets more than one input.
RRamyaR,
please read previous posts, all of the example code submitted yesterday uses DEFAULTs for the additional parameters
please read previous posts, all of the example code submitted yesterday uses DEFAULTs for the additional parameters
ASKER
I apologize -- simply forgot to close this question.
I was able to resolve the problem with a couple of the suggested solutions.
Many thanks!
I was able to resolve the problem with a couple of the suggested solutions.
Many thanks!
if you used the suggestions, then why grade with penalty B?