Geek Answers Handbook

How to populate a variable of my own created data type in Oracle PL / SQL?

In Oracle, I created a data type:

TABLE of VARCHAR2(200)

I want to have a variable of this type in a stored procedure (defined locally, and not as an actual table in the database) and populate it with data.

Some online samples show how I will use my type if it was populated and passed as a parameter to a stored procedure:

SELECT column_value currVal FROM table(pMyPassedParameter)

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

Does anyone know the syntax of this?

EDIT: I had to clarify: my original data is entered as the VARCHAR2 parameter passed to the stored procedure: a separator string (e.g. a comma). I am already iterating through a delimited string to get each individual value - I would like to INSERT each into its own type so that I can consider it as a TABLE for the rest of the logic.

0
source share
2 answers

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

This is like populating any other PL / SQL variable: we must use INTO. Just because we fill out a few lines, 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;
/

, LIMIT . , PL/SQL - PL/SQL - . , , PGA. .

" , , "

sigh - . SO. 9i , . ( , ).

2

" , " "( ), . , ?"

. ?

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>

SQL, , TOK_TBL PL/SQL.

+4

, , , SQL PL/SQL. Pl/SQL, , SQL :

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

Type created

. PL/SQL , :

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
+3

More articles:


All Articles