Creating Dataset using Stored Procedure Output

Modified on Wed, 12 Feb at 2:46 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.

Tags: Stored Procedure, SP, Parameters, MS SQL, Global variable, Oracle, Business Intelligence Support, Business Intelligence Articles, Business Intelligence Training, Smarten, Dataset Creation, MSSQL Server, Hard Coded Parameters, Invoice Number, Sys Ref Cursor, BI_CUSTOMERCOMPLAINT, Output Capture, Oracle Data Source Connection, Ref Cursor, SQL Syntax, Stored Procedure Execution, Date Parameters, VIEWCUSTOMERCOMPLAINT, SQL Query, Smarten Dataset,