Re: HOW TO GET ROWS FROM EXCEL TO ORACLE

You can achieve this with SQL*XL. http://www.oraxcel.com

There are 2 ways to do this:
You can directly call you PLSQL from the worksheet. The easiest would be if you would create a small anonymous PL/SQL block in each row to call your stored procedure. Example:
begin my_pack.my_proc('col1',col2,col3); end;

You can use Excel to create this for you inserting the variables. Example
=concatenate("begin my_pack.my_proc('"

                     ,A1 ,"',",B1,",",C1,"); end;")

You can now copy this for each row so the formulae change to
=concatenate("begin my_pack.my_proc('",A2,"',",B2,",",C2,"); end;")
=concatenate("begin my_pack.my_proc('",A3,"',",B3,",",C3,"); end;")
etc.

You can select the whole block of PL/SQL statements and execute them in one go with SQL*XL. Use the Execute PL/SQL feature.

The second way to achieve this is by inserting the data first into a temporary table and then do it in a PL/SQL loop in the database. To bulk insert data into the database from a spreadsheet is very simpel. Please read the help file for SQL*XL (which is also available online) on how to do this: http://www.oraxcel.com/projects/sqlxl
http://www.oraxcel.com/projects/sqlxl/help

Hope this helps,

Gerrit-Jan Linker Received on Fri Feb 22 2002 - 12:14:11 CET