how to insert sharepoint list to Oracle DB using Powershell

Question

I would like to retrieve some list items from a sharepoint list and then load it into an Oracle table.Is this feasible using powershell. Any thoughts or ideas would be appreciated. Thanks ** What would be the best way to prepare the table in powershell before inserting into Oracle?

Answer 1

I've done this with SharePoint and SQL Server. There's two parts first you'll want to query a SharePoint list as documented in my blog post:

http://sev17.com/2009/02/using-oledb-with-sharepoint-lists/

Second you'll want insert into a table. In SQL Server I would use the SQLBulkCopy class, there's a similar class for Oracle, but its probably just as easy to generate and execute insert statements from the DataTable returned from SharePoint. This piece will require a bit of work. Take a look at $dt variable and generate insert statemens. As an example

$dt | foreach {"INSERT myOracleTable VALUES ('$($.Field1)','$($.Field2)');"}

You could generate a file and use SQLPLus to execute the file:

$dt | foreach {"INSERT myOracleTable VALUES ('$($.Field1)','$($.Field2)');"} >> ./out.sql

Or, I've got a two-part series for querying Oracle from Powershell here:

http://sev17.com/tag/oracle/

Edit addded example:

#Select
$connString = ‘Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes; DATABASE=http://sharepoint.acme.com/IT/DBAdmin/;LIST={a113df9b-e56e-49d2-b786-03d170d18dbc};‘
$spConn = new-object System.Data.OleDb.OleDbConnection($connString)
$spConn.open()
$qry=‘Select * from list’
$cmd = new-object System.Data.OleDb.OleDbCommand($qry,$spConn)
$da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
$dt = new-object System.Data.dataTable
$da.fill($dt) > $null
$dt

Answer 2

You could also use Sharepoint web services to retrieve the information, I've described it somewhat here. I don't have much experience in accessing databases from Powershell, but found a nice blog post here that should fit the bill.