Automation Designer Import Table Data Parent Child

Toad for Oracle
1

I've been struggling with this and when I think I've found a way, I seem to always find a road block. So instead of heading down the wrong road yet again, has anyone done a parent child relationship in Automation Designer? Below is my loop (2 and 3 below just aren't working for me):

  1. Check to see if an Excel file is already been imported by checking against a file table
  2. If not already imported, insert a row into the file table and get the FILE_ID from the insert.
  3. Import the Excel file and use the FILE_ID from #2 to insert in a foreign key column.

What I have tried is:

  1. The file row insert in an Actionable Query PL/SQL block. If I choose the OUT type, the interface won't let me go any further. Everything grays out.
  2. Execute Script Text...can't figure out how to map the parameters. I've read examples of how to do with the File-List but that option doesn't appear to be there for text.
  3. I finally got it working using a SQL Iterator to select from the sequence and then use an Actionable Query to do the file insert. But now I cannot use the FILE_ID in the Import Table Data action as an expression on a column.

Just feel like I must be missing something.

Thanks for any help.

2

To help the unfortunate soul that needs to do this in the future, have Automation Designer do the least it can (import the file data with very little modification) and let Oracle do the rest. Here is how I got it to work:

  1. Create a global temporary table and have the Table Import put the file's data here. Just make sure the Table Import is set to NOT commit if your global temporary table is set to ON COMMIT DELETE ROWS. I did it this way so that on the next loop around the table would be ready for another file.
  2. I used an Actionable Query (PL/SQL) to do this next part but you can use other actions if you wish, you just need the database to do 3 things to finish off. First, insert the file row and RETURNING FILE_ID INTO a local variable in a DECLARE section. Second, INSERT INTO SELECT from the global temporary table using the file_id returned above. Lastly, COMMIT.

The primary issue, in the end, was the Table Import not allowing but rudimentary expressions. So all the work trying to get the file_id back for use was wasted in the end because I couldn't use it on the Table Import anyway.

Fell free to chime in if you find a better way.