array or list into Oracle using cfprocparam

I have a list of values I want to insert into a table via a stored procedure. I figured I would pass an array to oracle and loop through the array but I don't see how to pass an array into Oracle. I'd pass a list but I don't see how to work with the list to turn it into an array using PL/SQL (I'm fairly new to PL/SQL). Am I approaching this the wrong way?

Using Oracle 9i and CF8.

EDIT

Perhaps I'm thinking about this the wrong way? I'm sure I'm not doing anything new here... I figured I'd convert the list to an associative array then loop the array because Oracle doesn't seem to work well with lists (in my limited observation).

I'm trying to add a product, then add records for the management team.

-- product table

productName = 'foo' productDescription = 'bar' ... ... etc

-- The managementteam table just has the id of the product and id of the users selected from a drop down.

The user IDs are passed in via a list like "1,3,6,20"

How should I go about adding the records to the management team table?

---

Here is where I am code wise

In theory I pass a list "1,2,3,4" to inserts.addProduct.

inserts.addProduct should call tools.listToArray and return an array.

inserts.addProduct recreates a list with a * delim as a test.

CREATE OR REPLACE PACKAGE tools AS

  TYPE array_type is TABLE OF VARCHAR2(225) INDEX BY BINARY_INTEGER;

  FUNCTION listToArray(in_list IN VARCHAR,
                     in_delim IN VARCHAR2 DEFAULT ',') 
  RETURN array_type;

END tools;



CREATE OR REPLACE PACKAGE BODY tools
AS

FUNCTION listToArray(in_list IN VARCHAR,
                         in_delim IN VARCHAR2 DEFAULT ',') 
    RETURN array_type

    IS
    l_token_count BINARY_INTEGER := 0;
    -- l_token_tbl type_array; 
    i pls_integer;
    l_start_pos INTEGER := 1;
    l_end_pos INTEGER :=1;
    p_parsed_table array_type;

    BEGIN -- original work by John Spencer  
       WHILE l_end_pos <> 0 LOOP
          l_end_pos := instr(in_list,in_delim,l_start_pos);
          IF l_end_pos <> 0 THEN
             开发者_如何转开发l_token_count  := l_token_count  + 1;
             p_parsed_table(l_token_count ) :=
                      substr(in_list,l_start_pos,l_end_pos - l_start_pos);
             l_start_pos := l_end_pos + 1;
          END IF;
       END LOOP;
       IF l_token_count = 0 THEN /* We haven't parsed anything so */ 
          l_token_count := 1;
          p_parsed_table(l_token_count) := in_list;
       ELSE  /* We need to get the last token */ 
          l_token_count := l_token_count + 1;
          p_parsed_table(l_token_count) := substr(in_list,l_start_pos);
       END If;
       RETURN p_parsed_table;
    END listToArray;  -- Procedure

END tools;



CREATE OR REPLACE PACKAGE inserts AS
    TYPE array_type is TABLE OF VARCHAR2(225) INDEX BY BINARY_INTEGER;

    PROCEDURE addProduct (inList         IN  VARCHAR2,
                          outList        OUT VARCHAR2
                         );

END inserts;  




CREATE OR REPLACE PACKAGE BODY inserts                      

    AS
    PROCEDURE addProduct (inList         IN  VARCHAR2,
                          outList        OUT VARCHAR2
                         )
    IS
    i NUMBER;
    localArray array_type := tools.listToArray(inList);
    BEGIN       
       outList := '';
       FOR i IN localArray.first .. localArray.last LOOP
          outList := outList || '*' ||localArray(i); -- return a string just to test this mess 
       END LOOP;

    END addProduct;

END inserts;

I'm currently getting an error "PLS-00382: expression is of wrong type" on localArray array_type := tools.listToArray(inList);

---

final working code (thanks so much!)

-- create sql type collection

CREATE OR REPLACE TYPE array_type is TABLE OF VARCHAR2(225);
/



CREATE OR REPLACE PACKAGE tools AS

  FUNCTION listToArray(in_list IN VARCHAR,
                     in_delim IN VARCHAR2 DEFAULT ',') 
  RETURN array_type;

END tools;   
/



CREATE OR REPLACE PACKAGE BODY tools
AS

    FUNCTION listToArray(in_list IN VARCHAR,
                         in_delim IN VARCHAR2 DEFAULT ',') 
    RETURN array_type

    IS
    l_token_count BINARY_INTEGER := 0;
    i pls_integer;
    l_start_pos INTEGER := 1;
    l_end_pos INTEGER :=1;
    p_parsed_table array_type := array_type();

    BEGIN
       WHILE l_end_pos <> 0 LOOP
          l_end_pos := instr(in_list,in_delim,l_start_pos);
          IF l_end_pos <> 0 THEN
             p_parsed_table.extend(1);
             l_token_count  := l_token_count  + 1;
             p_parsed_table(l_token_count ) :=
                      substr(in_list,l_start_pos,l_end_pos - l_start_pos);
             l_start_pos := l_end_pos + 1;
          END IF;

       END LOOP;
       p_parsed_table.extend(1);
       IF l_token_count = 0 THEN /* We haven't parsed anything so */ 
          l_token_count := 1;
          p_parsed_table(l_token_count) := in_list;
       ELSE  /* We need to get the last token */ 
          l_token_count := l_token_count + 1;
          p_parsed_table(l_token_count) := substr(in_list,l_start_pos);
       END If;
       RETURN p_parsed_table;
    END listToArray;  -- Procedure

END tools;
/



CREATE OR REPLACE PACKAGE inserts AS

    PROCEDURE addProduct (inList  IN  VARCHAR2,
                             outList OUT VARCHAR2
                         );

END inserts;
/




CREATE OR REPLACE PACKAGE BODY inserts
AS
    PROCEDURE addProduct (inList  IN  VARCHAR2,
                          outList OUT VARCHAR2
                         )
    IS
    i NUMBER;
    mylist VARCHAR(100);
    localArray array_type := array_type();

    BEGIN     
    localArray := tools.listToArray(inList);
       mylist := '';
       FOR i IN localArray.first .. localArray.last LOOP
          mylist := mylist || localArray(i) || '*';
       END LOOP;
       aList := mylist;
    END addProduct;

END inserts;  
/

---

PL/SQL has supported arrays since Oracle 8.0. They used to be called PL/SQL tables which confused the heck out of everybody, so now they are called collections. Find out more.

The problem is, that they are implemented as User-Defined Types (i.e. objects). My reading of the ColdFusion documents suggests that cfprocparam only supports the "primitive" datatypes (number, varchar2, etc). So UDTs are not supported.

I'm not sure what you mean by this:

I'd pass a list but I don't see how to work with the list to turn it into an array using PL/SQL

If you mean you want to pass a string of comma separated values ....

"Fox in socks, Mr Knox, Sam-I-Am, The Lorax"

then I have a workaround for you. Oracle doesn't provide a built-in Tokenizer. But a long time ago John Spencer published a hand-rolled solution which works in Oracle 9i on the OTN forums. Find it here.

edit

but... Oracle hates me

Do not despair. The OTN forums have been upgraded a few times since John posted that , and the formatting seems to have corrupted the code somewhere along the way. There were a couple of compilation errors which it didn't use to have.

I have rewritten John's code, including a new function. THe main difference is that the nested table is declared as a SQL type rather than a PL/SQL type.

create or replace type tok_tbl as table of varchar2(225) 
/

create or replace package parser is

    function my_parse(
          p_str_to_search in varchar2
            , p_delimiter in varchar2 default ',')
          return tok_tbl;

    procedure my_parse(
          p_str_to_search in varchar2
          , p_delimiter in varchar2 default ','
          , p_parsed_table out tok_tbl);

end parser;
/

As you can see, the function is just a wrapper to the procedure.

create or replace package body parser is

    procedure my_parse ( p_str_to_search in varchar2
                          , p_delimiter in varchar2 default ','
                          , p_parsed_table out tok_tbl)
    is
        l_token_count binary_integer := 0;
        l_token_tbl tok_tbl := tok_tbl();
        i pls_integer;
        l_start_pos integer := 1;
        l_end_pos integer :=1;   
    begin

        while l_end_pos != 0
        loop
            l_end_pos := instr(p_str_to_search,p_delimiter,l_start_pos);

            if l_end_pos  != 0 then
                l_token_count := l_token_count + 1;
                l_token_tbl.extend();
                l_token_tbl(l_token_count ) :=
                    substr(p_str_to_search,l_start_pos,l_end_pos - l_start_pos);
                l_start_pos := l_end_pos + 1;
            end if;
        end loop;

        l_token_tbl.extend();
        if l_token_count = 0 then /* we haven't parsed anything so */
            l_token_count := 1;
            l_token_tbl(l_token_count) := p_str_to_search;
        else /* we need to get the last token */
            l_token_count := l_token_count + 1;
            l_token_tbl(l_token_count) := substr(p_str_to_search,l_start_pos);
        end if;
        p_parsed_table := l_token_tbl;
    end my_parse;

    function my_parse ( p_str_to_search in varchar2
                            , p_delimiter in varchar2 default ',')
                          return tok_tbl
    is
        rv tok_tbl;
    begin
        my_parse(p_str_to_search, p_delimiter, rv);
        return rv;
    end my_parse;

end parser;
/

The virtue of declaring the type in SQL is that we can use it in a FROM clause like this:

SQL> insert into t23
  2  select trim(column_value)
  3  from table(parser.my_parse('Fox in socks, Mr Knox, Sam-I-Am, The Lorax'))
  4  /

4 rows created.

SQL> select * from t23
  2  /

TXT
------------------------------------------------------------------------------
Fox in socks
Mr Knox
Sam-I-Am
The Lorax

SQL>