Is it possible to call an Oracle stored procedure and pass parameters to them from reporting services?

[Joe]

I've set up a shared datasource to oracle with the option of prompting for
credientals. The report works with just static SQL in the Query designer. I
have been trying for a number of days now to use a call to an oracle stored
procedure or function as my report data set without success.
The function called sp_ListEmp has one parameter of type number. To call the
function I have tried adding {call sp_ListEmp(?)} into the Query designer
and setting the command type to Text. I have added the parameter ? and made
that equal to a Report parameter EMPNO of Integer type that I have added to
the report. So I have in the parameter tab of the Dataset
?=Parameters!EMPNO.Value. I recieve the following error message 'An error
occurred while executing the query. ORA-01036: illegal variable
name/number.'

For the stored procedure curspkg_join.open_join_cursor1 I have not even been
able to call the procedure with recieving an error for the second parameter
which is of type refcursor. Does anyone know if it is possible to call
Oracle stored procedures and pass parameters to them from reporting
services?

I have added the code for the stored procedure and function and also the
tables for which I have been working on below and also the commands I use in
SQL Plus to prove that the actual function and stored procedure work. This
is causing me much woe.

Create DEPT table and insert some rows

CREATE TABLE DEPT
(DEPTNO NUMBER(2,0) NOT NULL,
DNAME VARCHAR2(14) NULL,
LOC VARCHAR2(13) NULL,
PRIMARY KEY (DEPTNO)
);

INSERT INTO Dept VALUES(11,'Sales','Texas');
INSERT INTO Dept VALUES(22,'Accounting','Washington');
INSERT INTO Dept VALUES(33,'Finance','Maine');

Create EMP table and insert some rows

CREATE TABLE EMP
(EMPNO NUMBER(4,0) NOT NULL,
ENAME VARCHAR2(10) NULL,
JOB VARCHAR2(9) NULL,
MGR NUMBER(4,0) NULL,
SAL NUMBER(7,2) NULL,
COMM NUMBER(7,2) NULL,
DEPTNO NUMBER(2,0) NULL,
FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),
PRIMARY KEY (EMPNO)
);

INSERT INTO Emp VALUES(123,'Bob','Sales',555,35000,12,11);
INSERT INTO Emp VALUES(321,'Sue','Finance',555,42000,12,33);
INSERT INTO Emp VALUES(234,'Mary','Account',555,33000,12,22);

Create package for Function example

create or replace package packperson
as
type cursorType is ref cursor;
end;
/

Create Function for Function example

create or replace function sp_ListEmp (n_EMPNO NUMBER) return
packperson.cursortype
as
l_cursor packperson.cursorType;
begin
open l_cursor for select ename as NAME, empno as NUM from emp where
empno = n_EMPNO order by ename;

return l_cursor;
end;

From SQL Plus I call this by

SQL> variable c refcursor
SQL> exec :c := sp_ListEmp(123)
SQL> print c

Create package for sp example

CREATE OR REPLACE PACKAGE curspkg_join AS
TYPE t_cursor IS REF CURSOR ;
Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
t_cursor);
END curspkg_join;
/

Create package body for sp example

CREATE OR REPLACE PACKAGE BODY curspkg_join AS
Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
t_cursor)
IS
v_cursor t_cursor;
BEGIN
IF n_EMPNO <> 0
THEN
OPEN v_cursor FOR
SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND EMP.EMPNO = n_EMPNO;

ELSE
OPEN v_cursor FOR
SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

END IF;
io_cursor := v_cursor;
END open_join_cursor1;
END curspkg_join;
/

From SQL Plus I call this by

SQL> variable c refcursor
SQL>exec curspkg_join.open_join_cursor1(123,:c)
SQL>print :c

[Robert Bruckner [MSFT]]

The cursor in the stored procedure has to be an OUT REF cursor rather than a
IN OUT cursor.
Also make sure you use the managed Oracle provider (i.e. "Oracle" in the
data source dialog) and not OleDB.

You might also want to check this previous posting for further information
and a sample:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=049fe955-cec8-4a79-a5e5-a9c02873e19d&sloc=en-us

--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Joe" <books...@netscape.net> wrote in message
news:%23dZ118w...@TK2MSFTNGP11.phx.gbl...

[Joe]

Thank you for your help,
I'm still unsure of the syntax that I should use in Reporting Services when
calling the sp. Where/how do I reference the out ref cursor? Example I call
the procedure using -

{ call test_package.get_customers(?, ) }

I get the following error 'The data extension Oracle does not support
unnamed parameters. Use named parameters instead.'
So my question is how do I reference the out ref cursor in Reporting
services?

"Robert Bruckner [MSFT]" <rob...@online.microsoft.com> wrote in message
news:e1$dlvyiE...@TK2MSFTNGP09.phx.gbl...

[Robert Bruckner [MSFT]]

Make sure you use the generic text-based query designer with 2 panes, rather
than the visual query designer with 4 panes.
Regarding the OUT cursor - don't reference it, just omit that parameter.

A better practice for stored procedures is to set the command type of the
query to StoredProcedure instead of Text. In that case, the query text is
just the name of the stored procedure "test_package.get_customers". The
parameters will automatically be determined by report designer on clicking
on the refresh fields icon.

--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Joe" <books...@netscape.net> wrote in message

news:uZsHcq%23iEH...@TK2MSFTNGP10.phx.gbl...

[Joe]

Thanks - got it working now, your help is much appreciated.