I am writing a script in PowerShell Core 7.2. I get a list of files from a folder that I check against Oracle db. I need the data of Description, and NC_Name column if the file is in db.
The issue is that even when the file is not in db it still returns the data but of some other file.
For example: I have a list of files, File#1, File#2,File#3. If File#2 is not in the db it still returns the data of File#1.

I have tried counting the number of rows and putting it as a condition. As in

 $rowNum = $connection.count

The issue with this is that $rowNum is never zero because it returns data for some other file; because the variable $fileName is never empty.

I also tried checking for the file name in the query itself but it gave a lot of errors. The query was

$query="DECLARE record_exists INTEGER; BEGIN SELECT COUNT(*) INTO record_exists FROM NC_PROGRAMS WHERE NC_PROGRAMS.NC_NAME  = '$fileName' AND ROWNUM = 1; IF record_exists = 1 THEN Select DESCRIPTION, NC_NAME"

The code is:

#Get all files
$result = $start.EnumerateDirectories() | ForEach-Object -Parallel {
    $_.GetFiles('*.EIA', $using:enum)
}
$result | Format-Table -AutoSize 

foreach($item in $result){
    $fileName = $item.BaseName
    #Oracle connection 
    Add-Type -Path C:\lib\netstandard2.1\Oracle.ManagedDataAccess.dll
    $query = "Select DESCRIPTION, NC_NAME From NC_PROGRAMS WHERE 
    NC_PROGRAMS.NC_NAME  = '$fileName' "
    $connectionString = "connectionString"
    $connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)
    $connection.Open()
    $command = $connection.CreateCommand()
    $command.CommandText = $query
    $reader = $command.ExecuteReader()
    $rowNum = $connection.count
    Write-host "Number of rows-"$rowNum
    while($reader.Read()) {   
        $description=$reader.GetString(0)
        $fastemsFileName = $reader.GetString(1)
    }
  
    $connection.Close()
}