Link to home
Start Free TrialLog in
Avatar of henderxe
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!

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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?
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
SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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


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;

Open in new window

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
Avatar of henderxe

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.
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, ???
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;

Open in new window

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

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,0000093847), that need to converted to individual rows, as illustrated above by "sdstuber".

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.

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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
I apologize -- simply forgot to close this question.

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?