Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Location
    Sochaux (France)
    Posts
    55

    Unanswered: PL/SQL Insert value in collection

    Hi,
    i've got a collection adn i want to insert a new record in this collection.
    I found an insert into table statement, but it seems not working.

    PHP Code:
    declare
      
    type emp_table is table of emp%row_type;
      
    my_emp_table  emp_table;

    begin

      select 
    from emp
      into my_emp_table
    ;

      -- 
    doing something

      insert into table my_emp_table
      values
    (....);
    end
    i just want to add a new record into my_emp_table, but i don't know how to do this. I'm looking for something like my_emp_table.add(record).

    Anyone can help me ?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: PL/SQL Insert value in collection

    PL/SQL tables don't work like that. You would do it more like this:
    PHP Code:
    declare
      
    type emp_table is table of emp%rowtype index by binary_integer;
      
    my_emp_table  emp_table;
      
    idx integer;
    begin

      
    -- Build collection from table
      
    for r in (select from emploop
        idx 
    := my_emp_table.count+1;
        
    my_emp_table(idx).empno := r.empno;
        
    my_emp_table(idx).ename := r.ename;
      
    end loop;

      -- 
    Add another row
      idx 
    := my_emp_table.count+1;
      
    my_emp_table(idx).empno := 1234;
      
    my_emp_table(idx).ename := 'Harry';

      -- 
    Display the collection
      
    for i in 1..my_emp_table.count loop
        dbms_output
    .put_linemy_emp_table(i).empno || ' ' || my_emp_table(i).ename );
      
    end loop;
    end;
    /
    7369 SMITH
    7499 ALLEN
    7521 WARD
    7566 JONES
    7654 MARTIN
    7698 BLAKE
    7782 CLARK
    7788 SCOTT
    7839 KING
    7844 TURNER
    7876 ADAMS
    7900 JAMES
    7902 FORD
    7934 MILLER
    1234 Harry

    PL
    /SQL procedure successfully completed
    Despite the "table" name, collections are not like database tables, they are more like "arrays".

  3. #3
    Join Date
    Jan 2004
    Location
    Sochaux (France)
    Posts
    55
    thanx a lot for your answer.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •