I think I know the solution to this but I'd like to get a second opinion.
I have a function called Get-Data that retrieves and returns a DataTable from an Oracle database. Now, due to Powershell being SUPER helpful, when only one record is returned from Oracle the function returns a DataRow instead of a DataTable.
When this happens and one of the columns is a LONG DataType, the field is truncated to 100 characters.
The Obvious Solution is to Return , $dt and modify my code to handle that. However, as I said, I wanted a second opinion.
Get-Data:
function Get-Data
{
[Cmdletbinding()]
Param
(
[Parameter(Position=0,Mandatory=$True)]$Conn,
[Parameter(Position=1,Mandatory=$True)]$sql
)
#Open the connection to the DB if closed
if($Conn.state -eq 'Closed')
{
$Conn.open()
}
#Create objects for querying the DB
$readcmd = New-Object system.Data.OleDb.OleDbCommand($sql,$Conn)
$readcmd.CommandTimeout = '300'
$da = New-Object system.Data.OleDb.OleDbDataAdapter($readcmd)
$dt = New-Object system.Data.datatable
#Query the DB and fill the DataTabe with records
[void]$da.fill($dt)
return $dt
}
Okay, so I found the source of the problem.
When utilizing this sql statement to select from a table with a LONG datatype:
Select * from [table]
OLEDB returns the full contents of the LONG field from ORACLE
When utilizing the following query instead for the same table:
Select distinct * from [table]
OLEDB returns only the first 100 characters of the LONG field