How can fill a variable of my own created data type within Oracle PL/SQL?

Question

In Oracle I've created a data type:

TABLE of VARCHAR2(200)

I want to have a variable of this type within a Stored Procedure (defined locally, not as an actual table in the DB) and fill it with data.

Some online samples show how I'd use my type if it was filled and passed as a parameter to the stored procedure:

SELECT column_value currVal FROM table(pMyPassedParameter)

However what I want is to fill it during the PL/SQL code itself, with INSERT statements.

Anyone knows the syntax of this?

EDIT: I should have clarified: my source data is entered as a VARCHAR2 parameter passed to the stored procedure: a separator (like comma) delimited string. I'm already iterating through the delimited string to get every separate value - I would like to INSERT each one into my type so I can treat it as a TABLE for the rest of the logic.

Answer 1

Hi Frankie,

you don't mention if the type you created is a SQL type or a PL/SQL type. They are used similarly in Pl/SQL so I will assume you created a SQL type with a command like this:

SQL> CREATE TYPE tab_varchar IS TABLE of VARCHAR2(200);
  2  /

Type created

This is a nested-table. Find out how to manipulate collections in PL/SQL it in the documentation, for example:

SQL> DECLARE
  2     lt tab_varchar;
  3  BEGIN
  4     /* initialization */
  5     lt := tab_varchar('a', 'b', 'c');
  6     /* adding elements */
  7     lt.extend(1);
  8     lt(4) := 'd';
  9     FOR i IN lt.FIRST .. lt.LAST LOOP
 10        dbms_output.put_line('lt(' || i || ')=' || lt(i));
 11     END LOOP;
 12  END;
 13  /

lt(1)=a
lt(2)=b
lt(3)=c
lt(4)=d

Answer 2

"I want is to fill it during the PL/SQL code itself, with INSERT statements"

It's like populating any other PL/SQL variable: we have to use INTO. Only because we're populating multiple rows we need to use the BULK COLLECT syntax.

declare
    l_array your_nested_table_type;
begin
    select col1
    bulk collect into l_array
    from t72;
end;
/

If the result set returns a lot of records it is a good idea to use the LIMIT clause inside a loop. This is because PL/SQL collections - just like every other PL/SQL variable - are held in session memory. So we don't want the array to get too big, otherwise it might blow the PGA. Find out more.

edit

"I edited the question to clarify specifically what I want"

sigh Tokenizing a string is an altogether different issue. I have previously posted solutions in two SO threads. If you're using 9i or earlier use this approach. Otherwise use this regex solution (actually this splits the string into numeric tokens, but it is easy enough to convert to characters).

edit 2

"I only want to be able to use the type "internally" (within the Stored Procedure) by adding values to it. Is this something I can do with the first link?"

Sure. Why not?

SQL> declare
  2      local_array tok_tbl;
  3  begin
  4      local_array := parser.my_parse('Keith Pellig,Peter Wakeman,Ted Bentley,Eleanor Stevens');
  5      local_array.extend();
  6      local_array(5) := 'Reese Verrick';
  7      for i in local_array.first()..local_array.last()
  8      loop
  9          dbms_output.put_line(local_array(i));
 10      end loop;
 11  end;
 12  /
Keith Pellig
Peter Wakeman
Ted Bentley
Eleanor Stevens
Reese Verrick

PL/SQL procedure successfully completed.

SQL>

Here I have re-used my SQL type, but it would work just as well if TOK_TBL were declared in the PL/SQL package instead.