88850

Loop over list and insert into table

Question:

I want to take value from one table and throw into another table. I have function where Im doing a bulk collect into a list. A List of beans.

FUNCTION get_things_info ( p_part_id IN NUMBER) RETURN bean_list IS thing_list bean_list; BEGIN SELECT thing_bean (id, file_name, file_type, dbms_lob.getlength(thing), auditable) BULK COLLECT INTO thing_list FROM part_things WHERE part_id = p_part_id; RETURN thing_list; END get_things_info_by_id;

I want to take that list, iterate over it and put in a deleted table with the same data types. I have a procedure that does an insert based off some java code:

PROCEDURE insert_thing(p_thing_bean IN OUT NOCOPY file_thing_bean, p_user_id IN NUMBER) IS BEGIN INSERT INTO deleted_part_things (id, part_id, file_name, file_type, thing, editable) VALUES ( p_thing_bean.id, p_thing_bean.parent_id, p_thing_bean.file_name, p_thing_bean.file_type, p_thing_bean.attachment, p_thing_bean.editable); END insert_thing;

It does not have to use this procedure. I just need to know how to loop over the list I got back from the first function and insert into the deleted_part_thing table

Answer1:

You could use <a href="http://www.oracle-base.com/articles/11g/PlsqlNewFeaturesAndEnhancements_11gR1.php#forall_restriction_removed" rel="nofollow">FORALL</a> This would then iterate through the collection supplied as a parameter inserting the records as required, I have given you a similar example you can amend to suit your needs:

PROCEDURE insert_from_list ( p_bean_list IN bean_list ) IS BEGIN FORALL x IN INDICES OF p_bean_list INSERT INTO deleted_hot_part_attachments (id, hot_part_id, file_name, file_type, attachment, auditable) VALUES ( p_bean_list(x).id, p_bean_list(x).parent_id, p_bean_list(x).file_name, p_bean_list(x).file_type, p_bean_list(x).attachment, p_bean_list(x).auditable ); -- COMMIT; END insert_from_list;

Hope it helps...

EDIT: If you are using 10g or earlier, you'll need to pull the records into a collection that is the same structure as the table to insert into before then calling:

FORALL x IN INDICES OF <new_collection> INSERT INTO deleted_hot_part_attachments VALUES <new_collection>(x);

So if the structure of deleted_hot_part_attachments matches exactly the collection type bean_list then your FORALL would be:

FORALL x IN INDICES OF p_bean_list INSERT INTO deleted_hot_part_attachments VALUES p_bean_list(x);

If it does not then you'll need something like:

PROCEDURE insert_from_list ( p_bean_list IN bean_list ) IS -- Declare collection to hold table values TYPE dhpa_tabtype IS TABLE OF deleted_hot_part_attachments%ROWTYPE INDEX BY PLS_INTEGER; dhpa_tab dhpa_tabtype; BEGIN -- Loop through the bean list collection populating the -- new dhpa_tab collection with the required values FOR i IN p_bean_list.FIRST .. p_bean_list.LAST LOOP dhpa_tab(i).id := p_bean_list(i).id; dhpa_tab(i).parent_id := p_bean_list(i).parent_id; dhpa_tab(i).file_name := p_bean_list(i).file_name; dhpa_tab(i).file_type := p_bean_list(i).file_type; dhpa_tab(i).attachment := p_bean_list(i).attachment; dhpa_tab(i).auditable := p_bean_list(i).auditable; END LOOP; -- Populate the table using the new dhpa_tab collection values FORALL x IN INDICES OF dhpa_tab INSERT INTO deleted_hot_part_attachments VALUES dhpa_tab(x); -- COMMIT; END insert_from_list;

Answer2:

A more old school approach to looping through collections would be something like:

declare type t_tab is table of varchar2(10); tab t_tab; tab_idx pls_integer; begin -- populate tab (dense at first) select 'val' || level bulk collect into tab from dual connect by level <= 20; -- make tab sparse (just for fun) tab.delete(3); -- remove 3rd element tab.extend(5); -- make space for 5 more elements tab(25) := 'val25'; -- LOOP through table contents tab_idx := tab.first; loop exit when tab_idx is null; dbms_output.put_line('Element ' || tab_idx || ' is ' || tab(tab_idx)); tab_idx := tab.next(tab_idx); end loop; end;

Nice thing is that you don't have the same restrictions with <a href="http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/forall_statement.htm" rel="nofollow">forall</a>

Recommend

  • Pass varray variables into stored procedure - basic
  • Prevent SQL Injection in Dynamic column names
  • How to hide or deactivate a TextField and a Label with javafx
  • Direct insert data in mysql data in android
  • JQgrid : Change entire row's font color if one column is filled
  • Migrating row from one table to another with conditions [MYSQL]
  • python struct.pack(): pack multiple datas in a list or a tuple
  • Max of several columns
  • Chrome firing onClick when space typed in contenteditable button
  • Django Migrations fail during django initialization
  • Compile error while declaring close
  • Update a record where _id = :id with Mongoose
  • Insert Statement
  • Select from table that does not exist
  • Cassandra 2.1: Recursion by nesting UDT's
  • Can't see some objects' internals in Qt Creator's Locals and Expressions debug window
  • Recreate the Oracle DUAL table
  • Insert records if not exist SQL Server 2005
  • How do you remove the JComboBox 'click and see dropdown' functionality?
  • Is there a way to disable a specific event in kendo ui scheduler?
  • how to query for min or max inet/cidr with postgres
  • d3.js selection conditional rendering
  • Use default value of a column in stored procedures
  • INSERT EXEC Statement cannot be nested [duplicate]
  • Returning the auto incrementing value after an insert using slick
  • How can Delete be both a DDL and a DML statement
  • Enabling DTD support in Sql Server
  • SAXReader not re-ecape characters
  • Adding elements to a huge XML file
  • close() was never explicitly called on database
  • Eliminate partial duplicate rows from result set
  • Transactional Create with Validation in ServiceStack Redis Client
  • PHP - How to update data to MySQL when click a radio button
  • Join two tables and save into third-sql
  • How to handle AllServersUnavailable Exception
  • Convert array of 8 bytes to signed long in C++
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Understanding cpu registers
  • Recursive/Hierarchical Query Using Postgres
  • Running Map reduces the dimensions of the matrices