Oracle PL/SQL script generation from excel sheet

Question

Hi, I'm having requirement to generate a insert script file from excel sheet. I'm partly successfully in generating script file. But i got struck in a situation,I need help from any1.

My logic is some thing like this, Read first cell,check if the value in the cell already exists in DB.If not, generate an insert script as follow

declare 
    PK1 integer;
begin
    select tablename_seq.currval into PK1 from dual;
    insert into TableName valuestablename_seq_seq.nextval,'Blagh',1);
end;

Im storing PK1 in hashtable with data has KEY .so that if the same data appears in the next rows,using Hashtable search, I will get the hashtable value for corresponding data key and pass it has parameter to another insert script. But every time i generate new variable like PK1,Pk2...etc.I have keep 'BEGIN' key word after Declare and also add 'END' key word after every insert,If i do so scope of variable goes out off scope.I may be using those declared variables in another insert statements has a parameter. Is there any chance of saving PK1,Pk2..... has session/Global variables for the script execution. So they wil become avialable for entire script execution time.

Answer 1

My inclination is to say that each line of your spreadsheet should just be creating a statement like insert into TableName values (tablename_seq_seq.nextval,'Blagh',1) returning ID into PK1;, then wrap the whole thing in a single DECLARE-BEGIN-END block with the appropriate variables defined, something like:

declare
   pk1 integer;
   pk2 integer;
begin
   insert into TableName 
       values (tablename_seq_seq.nextval,'Blagh',1) 
       returning ID into PK1;
   insert into TableName  
       values (tablename_seq_seq.nextval,'Urgh',2)  
       returning ID into PK2;
   [...]
end;

You could even create the list of variable declarations in one column and the SQL in another, then copy and paste them into the right place in the block.

Answer 2

Is the question about the best way to update a database from a spreadsheet, or the best way to generate a script from a spreadsheet?

I would recommend loading the spreadsheet data into a temporary table and then using a simple INSERT/SELECT statement, unless you're worried about uniqueness collisions in which case I would use a MERGE statement instead. This is much easier than trying to generate a script with logic for each insert statement.

Answer 3

I'd start off with a

DECLARE
  PROCEDURE action (p_val IN INTEGER) IS
  ...
  END action;
BEGIN

Then have each line in the spreadsheet just do a call to the procedure so that a spreadsheet entry of 1 becomes

  action (1);

Then you end up with something like

DECLARE
  PROCEDURE action (p_val IN INTEGER) IS
  ...
  END action;
BEGIN
  action (1);
  action (8);
  action (23);
  action (1);
  action (1);
END;

The action procedure can be as complicated as you like, storing information in tables/arrays whatever.

Answer 4

Hi,Instead of each Line, Consider as each cell. Each cell will generate insert script into corresponding cell. I have created in the same way, problem is If i want to use PK1 variable some where in the row 10 ,column 10 (cell value) insert script,because we are ending 'end' immediately after Begin block, the scope of PK1 always be remain in Begin block.For this i have created Begin with one insert and then create another Begin with another insert and so on.and @ the end im adding end;end;But the problem with above method is,I'm trrying to insert 200 row X 200 columns = 400 cells insert scripts. in this flow when i try to run script, it throws an runtime error ' Stack Overflow'

Answer 5

If you use Oracle E-Business, you might be interested by webadi.

This tool creates a excel files to be populated and then loaded into database via a procedure. You can then validate your data.

Creating custom Web ADI Integrators
WebADI - Using a Custom Integrator