Trouble running insert queries with varray variables

Question

I'm using sql*plus 9.2 on Oracle 10g enterprise. I have created some scripts that do basic inserts using parameters that I pass through the command prompt. It seemed logical that I should be able to run a bunch of inserts in a loop. So I tried the following:

--begin

DECLARE

TYPE va_orgs IS TABLE OF nbr.lien_item.lien_item_name%type;

org va_orgs := va_orgs('RTA','RTB','RTE','RTI','RTM','RTT');

BEGIN

FOR i in org.FIRST .. org.LAST

LOOP

INSERT INTO nbr.lien_item (lien_item_sid, excel_row, include_in_calcs, indent, header_level, sort_order, unit, lien_item_status, lien_item_name) VALUES (nbr.lien_item_seq.nextval, 0, 'Y', 1, 0, 1, 'FTE', 'A', 'org(i)');

COMMIT;

END LOOP;

END;

/

--end

When I run the script, I get a message that the pl/sql completed successfully. I tried debugging and using dbms_output to diplay the values of org(i). Everything looks fine. But the rows never get entered into the database. As soon as I do a select, the new rows aren't there. Is there some trick about looping and doing inserts??

(I also tried "IS VARRAY(6) OF" in place of "IS TABLE OF". Same non-result)

Thanks,

arcster

Answer 1

In your insert statement you have org(i) in single quotes. You shouldn't have that, you are probably inserting the words org(i) as values into the table. So your insert statement should be

INSERT INTO nbr.lien_item (lien_item_sid, excel_row, include_in_calcs, indent, header_level, sort_order, unit, lien_item_status, lien_item_name) VALUES (nbr.lien_item_seq.nextval, 0, 'Y', 1, 0, 1, 'FTE', 'A', org(i));