Convert oracle query to stored procedure for SQL Server Reporting Services (SSRS)

Question

How can I convert this query to a stored procedure that returns also the same(fields) table:

SELECT   R.REGION_NAME,
           TP.TERRITORY_NAME,
           LSC.LOOKUP_NAME,
           CAST (WM_CONCAT (CM.CUST_NAME) AS VARCHAR2 (500)) AS CUST_NAMES,
           TP.VIEWER_PROFILE,
           TP.CASTING_PREFERENCE,
           TP.PROG_TYPE_PREFERENCE,
           STN_LIST.STN_NAMES,
           STN_LIST.LIC_RIGHTS
    FROM               TERRITORY_PROFILE_MSTR TP
                    INNER JOIN
                       REGION_MSTR R
                    ON TP.REGION_ID = R.REGION_ID
                 LEFT OUTER JOIN
                    LOOKUP_SUB_CATEGORY LSC
                 ON TP.BROADCAST_STD_CD = LSC.LOOKUP_SUB_CAT_ID
              LEFT OUTER JOIN
                 CUST_MSTR CM
              ON CM.TERRITORY_ID = TP.TERRITORY_ID
           LEFT OUTER JOIN
              (SELECT   CAST (WM_CONCAT (SLR.CUST_STN_NAME) AS VARCHAR2 (500))
                             AS STN_NAMES,
                          CAST (WM_CONCAT (LRM.LIC_RIGHTS_ID) AS VARCHAR2 (500))
                             AS LIC_RIGHTS,
                          SLR.CUST_COMPETITOR_ID AS CUST_ID
                   FROM   STN_LIC_RIGHTS SLR, LIC_RIGHTS_MSTR LRM
                  WHERE   SLR.STN_LIC_ID = LRM.LIC_RIGHTS_ID
               GROUP BY   SLR.CUST_COMPETITOR_ID) STN_LIST
           ON STN_LIST.CUST_ID = CM.CUST_ID
   WHERE   TP.TERRITORY_ID <> 0
GROUP BY   TP.TERRITORY_NAME,
           R.REGION_NAME,
           LSC.LOOKUP_NAME,
           STN_LIST.STN_NAMES,
           STN_LIST.LIC_RIGHTS,
           TP.VIEWER_PROFILE,
           TP.CASTING_PREFERENCE,
           TP.PROG_TYPE_PREFERENCE
ORDER BY   R.REGION_NAME, TP.TERRITORY_NAME

I think I don't need to specify the table structure. Please take note that the query is fully functional and the values are correctly return the way I expect it to return.

Thank you.

UPDATE:

The stored procedure must be compatible and executable in ASP.NET(C#) through SQL Server Reporting Services or SSRS.

score 3 · Answer 1 · edited May 23 '17 at 11:50

3

There are a few ways to do what you're asking:

By using a function / procedure which returns a sys_refcursor as described here
By using a "parameterized view" as described here
By using a table functions as described here

and probably more...

edited May 23 '17 at 11:50

Community

1
1

answered May 27 '13 at 08:37

A.B.Cade

16,735
1
37
53

vc 74 · Answer 2 · 2013-05-27T10:12:46.393

1

You can create a PL SQL procedure that returns a ref cursor.

CREATE OR REPLACE
PROCEDURE foo(p_terr_id IN TERRITORY_PROFILE_MSTR.TERRITORY_ID%TYPE, 
  p_recordset out SYS_REFCURSOR) 
AS 
BEGIN 
    OPEN p_recordset FOR
    /* Your SQL here */
END foo;
/

More details and examples here

edited May 27 '13 at 10:12

answered May 27 '13 at 08:21

vc 74

37,131
7
73
89

I don't get it. How can I execute this? – Mark May 27 '13 at 09:02
How about what if I don't need a parameter? – Mark May 27 '13 at 09:04
Sir I'm going to use the stored procedure in ReportViewer (SSRS) in ASP.NET. Your solution don't work. – Mark May 27 '13 at 09:31
@ChristianMark If you don't need the parameter, remove it – vc 74 May 27 '13 at 09:48
@ChristianMark This block creates a function that returns the record set as a ref cursor which, I believe, is what you wanted – vc 74 May 27 '13 at 09:49
Sir I want it to be compatible to SSRS in ASP.NET wherein if you execute the sp it will return a table or result like you are running a query. Is that possible? – Mark May 27 '13 at 09:52
I have updated my question. I have successfully created what you answered but I want it to be specific to SSRS function (+1 for that) – Mark May 27 '13 at 09:54
According to the following article, ssrs only accepts procedures with out parameters, not function, updating sample code... http://database.ittoolbox.com/groups/technical-functional/sql-server-l/getting-data-from-an-oracle-sp-ref-cursor-3304405 – vc 74 May 27 '13 at 10:11

score 1 · Accepted Answer · answered May 28 '13 at 06:31

Nobody mentioned any alternative to my question like the use of Oracle VIEWS.

Instead of creating a stored procedure for my SSRS reports, I can use VIEWS though they don't have any parameters, it answers my question.

For future reference here's the code:

CREATE VIEW RV_TERRITORY_PROFILE
AS
SELECT   R.REGION_NAME,
           TP.TERRITORY_NAME,
           LSC.LOOKUP_NAME,
           CAST (WM_CONCAT (CM.CUST_NAME) AS VARCHAR2 (500)) AS CUST_NAMES,
           TP.VIEWER_PROFILE,
           TP.CASTING_PREFERENCE,
           TP.PROG_TYPE_PREFERENCE,
           STN_LIST.STN_NAMES,
           STN_LIST.LIC_RIGHTS
    FROM               TERRITORY_PROFILE_MSTR TP
                    INNER JOIN
                       REGION_MSTR R
                    ON TP.REGION_ID = R.REGION_ID
                 LEFT OUTER JOIN
                    LOOKUP_SUB_CATEGORY LSC
                 ON TP.BROADCAST_STD_CD = LSC.LOOKUP_SUB_CAT_ID
              LEFT OUTER JOIN
                 CUST_MSTR CM
              ON CM.TERRITORY_ID = TP.TERRITORY_ID
           LEFT OUTER JOIN
              (SELECT   CAST (WM_CONCAT (SLR.CUST_STN_NAME) AS VARCHAR2 (500))
                             AS STN_NAMES,
                          CAST (WM_CONCAT (LRM.LIC_RIGHTS_ID) AS VARCHAR2 (500))
                             AS LIC_RIGHTS,
                          SLR.CUST_COMPETITOR_ID AS CUST_ID
                   FROM   STN_LIC_RIGHTS SLR, LIC_RIGHTS_MSTR LRM
                  WHERE   SLR.STN_LIC_ID = LRM.LIC_RIGHTS_ID
               GROUP BY   SLR.CUST_COMPETITOR_ID) STN_LIST
           ON STN_LIST.CUST_ID = CM.CUST_ID
   WHERE   TP.TERRITORY_ID <> 0
GROUP BY   TP.TERRITORY_NAME,
           R.REGION_NAME,
           LSC.LOOKUP_NAME,
           STN_LIST.STN_NAMES,
           STN_LIST.LIC_RIGHTS,
           TP.VIEWER_PROFILE,
           TP.CASTING_PREFERENCE,
           TP.PROG_TYPE_PREFERENCE
ORDER BY   R.REGION_NAME, TP.TERRITORY_NAME

then you can run it like

SELECT * FROM RV_TERRITORY_PROFILE

But how will you send parameters to the view ? actually you did get that answer - it's in the ["parameterized view" link](http://stackoverflow.com/questions/9024696/creating-parameterized-views-in-oracle11g) — A.B.Cade, May 28 '13 at 06:50
Aha. I see.. Sorry. I'm focused on my own research. But if the answer is more specific and not just links, I can accept it. — Mark, May 28 '13 at 06:55

Convert oracle query to stored procedure for SQL Server Reporting Services (SSRS)

3 Answers3

Linked