Facing issue with executestoreprocedure()

HI All,

I am calling store proc which is having one input and output parameter. When i giving call to SP it is returning null even though there is record for input.

local!result: fn!executestoredprocedure(
dataSourceName:"jdbc/#####DS",
procedureName: "###APPIAN.TEMP",
inputs:
{
{name: "caseId", value: ri!caseId}

}
),

if(local!result.success,
local!result.result,
local!result.error
)

Any prompt help will highly appreciated.

Thanks,

  Discussion posts and replies are publicly visible

Parents
  • Hi ,

    IT is working fine when ran from sqldeveloper but same when tried to call Appian with executestoredprocedure function it is returing o items.. Please see below.

    with(
    /* local!resultSet:*/fn!executestoredprocedure(
    dataSourceName: cons!DATA_SOURCE,
    procedureName: "SP_#####_REPORT",
    inputs: {
    {
    name: "fromDate",
    value: todate(
    ri!fromDate
    )
    },
    {
    name: "toDate",
    value: todate(
    ri!toDate
    )
    }
    }
    )

    ),

    I am passing input to SP like below:
    create or replace PROCEDURE sp_###_REPORT(
    fromDate IN timestamp, toDate IN timestamp)


    select * from ABC where createdDate between TO_DATE(fromDate,'DD/MM/YYYY') and TO_DATE(toDate,'DD/MM/YYYY')

    SP is very complex , i am just hinghlighting required part where seems I am doing some mistake.

    Thanks.
  • Hi Vinod,

    Please check if your stored procs queries are performing any create,update, delete operations in DB because AFAIK executestoredprocedure function only works for Select scripts.
Reply Children