Error ORA-00936 when using Parameters

[tobit800]

Hi Mitharandyr.

I have tried to use Invoke-SqlUpdate with -Parameters to insert into an Oracle-DB. I always get the exception ORA-00936. Is there a problem with the Parameters-Option? Thanks a lot! Tobias

Ausnahme beim Aufrufen von "ExecuteScalar" mit 0 Argument(en): "ORA-00936: Ausdruck fehlt" In C:\Program Files\WindowsPowerShell\Modules\SimplySql\1.6.2\Classes.ps1:38 Zeichen:22 Try { return $cmd.ExecuteScalar() } ~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : OracleException

[mithrandyr]

@tobit800 sorry for the delay in getting back to you -- I am not aware of any issues. Can you include exact code your are using that is causing problems?

[mithrandyr]

Closed as unreproducible

[darthcircuit]

I am getting the same issue. Here is my code snippet:

$sql = "INSERT INTO DB.TABLE(event_id,app_id,app_name,user_id,upn) VALUES ('@event_id','@app_id','@app_name','@user_id','@upn')"                                  
$newEvent = @{                                                                                                                                     
    event_id="test_event_1"
    app_id="test_app_id_1"
    app_name="Test App"
    user_id="test_user_id_1"
    upn="[email protected]"
    }

Invoke-SqlUpdate $sql -Parameters $newEvent

I always get this error, but only when passing parameters. I've tried piping the object in directly:

$newEvent | Invoke-SqlUpdate $sql

Creating a new object as a parameter:

$sql = "INSERT INTO DB.TABLE(event_id,app_id,app_name,user_id,upn) VALUES (@eid, @aid, @an, @uid, @upn)"
$newEvent = @{                                                                                                                                     
    event_id="test_event_1"
    app_id="test_app_id_1"
    app_name="Test App"
    user_id="test_user_id_1"
    upn="[email protected]"
    }

Invoke-SqlUpdate $sql -Parameters @{eid=$newEvent.event_id; aid=$newEvent.app_id; an=$newEvent.app_name; uid=$newEvent.user_id; upn=$newEvent.upn}

But neither has helped. Every scenario I've tried ends in:

Invoke-SqlUpdate: ORA-00936: missing expression

The only inserts I've been able to do successfully are when I add the values directly to the query:

$sql = "INSERT INTO DB.TABLE(event_id,app_id,app_name,user_id,upn) VALUES ('test_event_1','test_app_id_1','Test App','test_user_id_1','[email protected]')" 

Invoke-SqlUpdate $sql

I've tested on Windows and Linux.

Name                           Value
----                           -----
PSVersion                      7.5.0
PSEdition                      Core
GitCommitId                    7.5.0
OS                             Red Hat Enterprise Linux 9.5 (Plow)
Platform                       Unix
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

Name                           Value
----                           -----
PSVersion                      7.4.6
PSEdition                      Core
GitCommitId                    7.4.6
OS                             Microsoft Windows 10.0.17763
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

[mithrandyr]

@darthcircuit -- for Oracle, you have to prefix your variables with ":" instead of "@". so

$sql = "INSERT INTO DB.TABLE(event_id,app_id,app_name,user_id,upn) VALUES (:event_id, :app_id, :app_name, :user_id, :upn)"                                  
$newEvent = @{                                                                                                                                     
    event_id="test_event_1"
    app_id="test_app_id_1"
    app_name="Test App"
    user_id="test_user_id_1"
    upn="[email protected]"
    }

Invoke-SqlUpdate $sql -Parameters $newEvent

[darthcircuit]

That worked!! thank you!