1. Home
  2. Support
Article - CS264912

Failing to return valid result from Oracle's Stored Procedure with OUT parameter to SQL Query service in ThingWorx

Modified: 24-Nov-2017   


Applies To

  • ThingWorx Platform 7.3

Description

  • Failing to return valid result from Oracle's Stored Procedure with OUT parameter to SQL Query service in ThingWorx
  • Oracle's stored procedure without OUT Parameter returns correct result, these can be executed with correct result set returning to the ThingWorx's SQL Query / Command service
  • For e.g. following stored procedure created in Oracle's schema
Create or replace procedure demo1 (col1 in <DataType> , col2 in <DataType>) AS 
Begin 
Insert into <tableName> values (col1, col2);
End;
  • Returns valid result when executed via a SQL Command under a Thing in a ThingWorx Service, e.g.
BEGIN
demo1 (
col1, col2)
End;
  • Values for Col1 and Col2 can be substituted by using an Input parameter in the ThingWorx Service or values for those parameter can be entered directly
  • However the Stored procedure fail to return required result when OUT parameter is used to output the result e.g.
  • Stored procedure in Oracle is created something like this with OUT parameter
create or replace PROCEDURE GET_ROW_COUNT 
(
 Result OUT NUMBER 
) AS 
BEGIN
  select count(col1) into result from DEMOTABLE;
END GET_ROW_COUNT;
  • Returns following output when called from SQLQuery service under a Thing in ThingWorx
"Unable to Invoke Service Demo1Proc on OracleDB12C : null"
  • Executing that same procedure in Oracle's SQL Developer / SQL*Plus returns required result as table does contain 5 rows 
User-added image​
This is a printer-friendly version of Article 264912 and may be out of date. For the latest version click CS264912