Calling a Stored Procedure in Oracle Database using IronPython Script in TIBCO Spotfire throws the error System.Data.OracleClient.OracleException: ORA-00900: invalid SQL statement

Calling a Stored Procedure in Oracle Database using IronPython Script in TIBCO Spotfire throws the error System.Data.OracleClient.OracleException: ORA-00900: invalid SQL statement

book

Article ID: KB0073335

calendar_today

Updated On:

Products Versions
Spotfire Analyst All

Description

Using an IronPython script in TIBCO Spotfire to call a stored procedure in an Oracle database using the following query Exec <ProcedureName>
results in the following error message:
Exception: Unable to execute SQL statement: 'Exec Sample0;' Spotfire.Dxp.Data.Exceptions.ImportException: Unable to execute SQL statement: 'Exec Sample0;' ---> System.Data.OracleClient.OracleException: ORA-00900: invalid SQL statement

Being a SQL command it may work as expected in Oracle SQL Developer but cannot be used in IDE's.

Note: Calling a stored procedure using the same query works in the case of Microsoft SQL Server.
 

Environment

All

Resolution

The command to be used in IronPython Scripting is: 
Begin <ProcedureName>; End;

The following IronPython script could be used: 
from Spotfire.Dxp.Data.Import import *

sqlStr ="Begin Sample0; End;"
dbsettings = DatabaseDataSourceSettings( "System.Data.OracleClient","Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<Server_Name>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=<Service_Name>)));User Id=<User>;Password=<Password>",sqlStr)
ds = DatabaseDataSource(dbsettings)

Issue/Introduction

This article explains how to use an IronPython script to call a stored procedure in Oracle database from Spotfire.
Powered by Wolken Software