Creating Dataset using Stored Procedure Output

Modified on Thu, 14 Mar 2024 at 01: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

)

AS

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

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select atleast one of the reasons

Feedback sent

We appreciate your effort and will try to fix the article