# Download an extract IKVM to any folder
# cd C:tempikvmbin
# convert jar to dll with ikvmc: ikvmc -target:library C:jdbcojdbc6.jar


Add-Type -Path "C:tempikvmbinojdbc6.dll"
Add-Type -Path "C:tempikvmbinIKVM.OPenJDK.Jdbc.dll"
Add-Type -Path "C:tempikvmbinIKVM.OPenJDK.Core.dll"

$url = "jdbc:oracle:thin:@localhost:1521/someServiceName"
$driver = [oracle.jdbc.driver.OracleDriver]::new()
$props = [java.util.Properties]::new()
$props.setProperty("user", "someUser")
$props.setProperty("password", "somePassword")
$conn = $driver.connect($url, $props)
$cmd = $conn.createStatement()

$r = $cmd.executeQuery("select * from all_tables where rownum < 20")
Write-Host "column count: $($r.getMetaData().getColumnCount())"

while($r.next()){

$row = foreach($c in 1..20){ $r.getString($c)}
Write-Host ($row -join "|")
}

function Invoke-Jdbc { param(
  $java="Java" # or path to java exe
, $format="EXCEL"
, $driver = "C:jdbcjdbcsql.zip"
, $dbtype, $hostname, $port, $db, $user, $password, $query
)

$data = &$java -jar $driver -f $format -m $dbtype -host $hostname -port $port -d $db -U $user -P $password $query
Write-Output -NoEnumerate ($data | ConvertFrom-Csv -Delimiter "`t" | Out-DataTable)

}


$oracle = @{
 dbtype = "oracle"
 hostname = "localhost"
 port = "1521"
 db = "ServiceNameOrSID"
 user = "user"
 password = "password"
 query = "select owner, table_name from all_tables"
}

$dt = Invoke-JDBC @oracle