Zell
Zell

Reputation: 43

Error when passing Oracle SYSTEM.DATA.DATATABLE result as parameter to another powershell script

I'm working on a script that is expected to perform the following:

  1. Connect to Oracle DB and run main query to extract raw data. - Already working as unit tested
  2. Import raw data to a Microsoft access database template (.mdb) - Working as unit tested but encountering an error when integrated with script 1.
  3. Connect to MS Access data, run filtered (sub)queries and export results into separate excel tabs. - Already working as unit tested

*Directly running the (sub)queries on the Oracle database was out of option since the query being performed on the Oracle database (raw data) takes about 5 hours so raw data is imported to Microsoft access first for processing, and then running the (sub)queries.

I have already created the Powershell script for each one of the items and one master script which calls and supplies the arguments for each. They have been unit tested.

1 - 01.dbquery.ps1 [Parameter: NONE; Returns: RAW DATA]

2 - 02.mdbimport.ps1 [Parameter: RAW DATA(from 1),MDB FILE Return: NONE]

3 - 03.mdb export.ps1 [Parameter: EXCEL FILE, MDB FILE; Return: NONE]

master script.ps1

However, when integrating script 1 and 2, Im getting the error System.Management.Automation.ParameterBindingException: A positional parameter cannot be found that accepts argument 'System.Data.DataRow'. Note that script 1 returns System.Data.Datatable value from the queried result on the Oracle DB and I'm trying to figure out why script 2 does not seem to accept this value. I've also tried stating the type System.data.datarow on the parameter declaration but still to no avail as I'm getting the error

Cannot process argument transformation on parameter 'RawData'. Cannot convert the "System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow" value of type "System.String" to type "System.Data.DataRow".

Below are the code snippets for each script

master script.ps1

## Setup reusable variables (e.g. MDB, excel templates)
$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition
$Date=(get-date).tostring('MMddyyy')
$MdbFile =  $scriptPath+"\Pts on Spans Analysis "+$date+".mdb" 
$ExcelFile = $scriptPath+'\Pts on Spans Action '+$date+'.xls'

## 01. Connect to Oracle DB and run main query for PS raw data
write("**Starting Oracle database query and storing results... -- "+(get-date))
$01dbquery = $scriptPath+"\01dbquery.ps1"
$01ret = Invoke-Expression "& `"$01dbquery`""
write("**Finished querying Oracle DB. -- "+(get-date)); write-host

## 02. Import raw data to MDB template (Microsoft access) -- ImportedPtsonSpans
write("**Starting import of PS raw data to MDB... -- "+(get-date))
$02mdbimport = $scriptPath+"\02mdbimport.ps1"
Invoke-Expression "& `"$02mdbimport`" -RawData $01ret -MdbFile `"$MdbFile`""
write("**Finished import of PS raw data. -- "+(get-date)); write-host

## 03. Process MS Access data and export into excel
write("**Starting export of processed PS data into Excel... -- "+(get-date))
$03mdbexport = $scriptPath+"\03mdbexport.ps1"
Invoke-Expression "& `"$03mdbexport`" -ExtractName `"$ExcelFile`" -MdbFile `"$MdbFile`""
write("**Finished data export. Pts and Span Extract Completed -- "+(get-date)); write-host

01dbquery.ps1

$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition    
function Get-DataTable
{
    Param(
    [Parameter(Mandatory=$true)]
    [System.Data.OracleClient.OracleConnection]$conn,
    [Parameter(Mandatory=$true)]
    [string]$sql
    )
    $cmd = New-Object System.Data.OracleClient.OracleCommand($sql,$conn)
    $da = New-Object System.Data.OracleClient.OracleDataAdapter($cmd)
    $dt = New-Object System.Data.DataTable
    [void]$da.Fill($dt)
    return ,$dt
}

## Initiate database credential variables
<more codes here>

## Setup database connection
<more codes here>

## Perform database queries and store results
$QueryPath = $scriptPath+"\queries\main\Pts Span Qry.txt"
$Query = get-content $QueryPath | out-string

# *** Entry Point to Script ***
$RawData = Get-DataTable $conn $Query ; $RawData

02mdbimport.ps1

param
(
    [Parameter(Mandatory=$true)]$RawData,
    [Parameter(Mandatory=$true)]$MdbFile
)
$ErrorActionPreference = 'Stop'

$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition    

Function Connect-Database($MdbFile, $Tables)
{
  <codes>
} #End Update-Records

Function Update-ImportedPtsonSpans
{
 <codes>
} #End Update-ImportedPtsonSpans

# *** Entry Point to Script ***
## Perform import of raw data into Microsoft Access Database (MDB)
$Tables = "ImportedPtsonSpans"
# Test if raw data is passed
write-host Start Test raw data and MdbFile
$RawData
$MdbFile
write-host End Test raw data and MdbFile
#
Connect-DataBase -MdbFile $MdbFile -tables $Tables

03mdbexport.ps1

param
(
    [Parameter(Mandatory=$true)]$ExtractName,
    [Parameter(Mandatory=$true)]$MdbFile
)
$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition
Function Export-Data($Queries,$MdbFile,$ExtractName)
{
    <codes>        }
} #End Export-Data

Function Export-To-Excel($ExtractName,$QueryName,$CurResult)
{
    <codes>    } # End Export-To-Excel

# *** Entry Point to Script ***
$QueryPath = $scriptPath+"\queries\*.txt"
$Queries = Get-ChildItem $QueryPath
Export-Data -Queries $Queries -MdbFile $MdbFile -ExtractName $ExtractName

Thanks in advance!

Upvotes: 1

Views: 408

Answers (0)

Related Questions