Trying to insert into a table while looping through pl SQL

Question

I have a view that I need to loop through with all the inventory ids from another table. I keep receiving the following error: Error report -

ORA-06550: line 31, column 31:
PLS-00357: Table,View Or Sequence reference 'MISSINGINVENTORY' not allowed in this context
ORA-06550: line 31, column 2:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

This is my current code I am not sure what the problem would be? I ma fairly new to pl sql

 DECLARE
    CURSOR inventory_ids_c IS
      SELECT DISTINCT fzrfbth_status       status,
                      fzbfbth_inventory_id id
      FROM   fzbfbth@develop_sw,
             fzrfbth@develop_sw
      WHERE  fzbfbth_inventory_id = fzrfbth_inventory_id
      ORDER  BY fzrfbth_status,
                fzbfbth_inventory_id;
    inventory_ids_rec inventory_ids_c%ROWTYPE;
BEGIN
    OPEN inventory_ids_c;

    FETCH inventory_ids_c INTO inventory_ids_rec;

    LOOP
        EXIT WHEN inventory_ids_c%NOTFOUND;

        fzkfims.P_set_inventory_id@develop_sw(inventory_ids_rec.id);

        DECLARE
            CURSOR inventory_items IS
              SELECT *
              FROM   fzvfims@develop_sw;
        BEGIN
            OPEN inventory_items;

            LOOP
                FETCH inventory_items INTO Missinginventory(last_inventory_date,
                atype_title
                ,
                owner, orgn_code, orgn_title, locn_code, room, bldg, sort_room,
                ptag
                ,
                manufacturer, model, serial_num, description, custodian, po,
                acq_date,
                amount,
                ownership, schev_year, tag_type, inventory_id, condition,
                asset_type
                );

                EXIT WHEN inventory_items%NOTFOUND;
            END LOOP;

            CLOSE inventory_items;
        END;
    END LOOP;
END;

What type of object is Missinginventory? Table, view, or ? — Acroyear
– Acroyear, Commented Oct 14, 2019 at 17:04

Littlefoot · Accepted Answer · 2019-10-14 17:17:24Z

It seems that you are trying to fetch from cursor directly into Missinginventory table (if it is a table). Well, that won't work. First fetch into cursor variables, then insert those variables into a table.

I don't know what this:

fzkfims.P_set_inventory_id@develop_sw(inventory_ids_rec.id);

does; looks like some procedure call. I guess it sets some kind of an "environment", but I don't know how it reflects to code you wrote.

Anyway: why do you use cursor loops? Can't you directly insert data into the table? That would be faster and - hopefully - simpler.

Also, if you declare something, do that at the same place, at the beginning of that PL/SQL procedure. If you scatter declarations all over your code, it is difficult to maintain it. Furthermore, see whether cursor FOR loops are an option as they are simpler to use - Oracle does a lot of things for you. How? Although you still have to write cursor's SELECT statement, you don't have to declare cursor variables, open cursor, fetch, take care about exiting the loop, close the cursor. I'll try to rewrite your code, have a look (as you can see, no declare section at all!).

 begin
    for cur_1 in (select distinct fzrfbth_status       status,
                                  fzbfbth_inventory_id id
                  from   fzbfbth@develop_sw
                         join fzrfbth@develop_sw
                           on fzbfbth_inventory_id = fzrfbth_inventory_id
                  order  by fzrfbth_status,
                            fzbfbth_inventory_id
                 ) 
    loop
        fzkfims.p_set_inventory_id@develop_sw(cur_1.id);

        for cur_2 in (select *
                      from   fzvfims@develop_sw
                     ) 
        loop
            -- I shortened it to just a few columns
            insert into missinginventory
                        (last_inventory_date,
                         atype_title,
                         asset_type)
            values      (cur_2.last_inventory_date,
                         cur_2.atype_title,
                         cur_2.asset_type);
        end loop;
    end loop;
end;

I am still testing out your suggested solution but as some additional detail, The fzkfims.p_set_inventory_id@develop_sw(cur_1.id); is used to set an id which must be set to use the FZVFIMS view. the purpose of this was to cycle through the ids from the fzbfbth table and insert all the returned information from each one of those ids in a new table. For example if the ids are 1-5 it would set the id to 1 run the fzvfims view pull all the information into the missinginventory table go to the next id do the same and so on. Thank you very much for your response I will let you know how it goes!

Collectives™ on Stack Overflow

Trying to insert into a table while looping through pl SQL

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related