Creating Dataset using Stored Procedure Output

Modified on Thu, 14 Mar at 1:12 PM

How to fetch output of Stored Procedure in Smarten during Dataset Creation :

A) MSSQL Server :

1) Stored Procedure Without Parameter :

2) Stored Procedure With Hard coded parameter (e.g. invoice number):

3) Stored Procedure using Global variable :

B) Oracle :

Syntax for executing SP through Oracle Data Source Connection is as below :

{call BI_CUSTOMERCOMPLAINT(:b,3,'a','25/10/2016')}

CREATE OR REPLACE PROCEDURE BI_CUSTOMERCOMPLAINT

(

cv_1 OUT SYS_REFCURSOR,

v_DocumentId IN NUMBER,

v_DocumentId2 IN varchar2,

v_date in date

)

BEGIN

OPEN cv_1 FOR

select * from VIEWCUSTOMERCOMPLAINT;

END;

Note : In case of Oracle it is mandatory to have Sys Ref Cursor. Output has to be captured in SP and then can be used to create Smarten Dataset.

Stored Procedure SP Parameters MS SQL Global variable Oracle Business Intelligence Support Business Intelligence Articles Business Intelligence Training