Is it possible to call an Oracle stored procedure and pass parameters to them from reporti

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 :cThe 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" <booksnore2@.netscape.net> wrote in message
news:%23dZ118wiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> 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
>|||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]" <robruc@.online.microsoft.com> wrote in message
news:e1$dlvyiEHA.1656@.TK2MSFTNGP09.phx.gbl...
> 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" <booksnore2@.netscape.net> wrote in message
> news:%23dZ118wiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> > 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
> >
> >
>|||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" <booksnore2@.netscape.net> wrote in message
news:uZsHcq%23iEHA.384@.TK2MSFTNGP10.phx.gbl...
> 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]" <robruc@.online.microsoft.com> wrote in message
> news:e1$dlvyiEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > 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" <booksnore2@.netscape.net> wrote in message
> > news:%23dZ118wiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> > > 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
> > >
> > >
> >
> >
>|||Thanks - got it working now, your help is much appreciated.
"Robert Bruckner [MSFT]" wrote:
> 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" <booksnore2@.netscape.net> wrote in message
> news:uZsHcq%23iEHA.384@.TK2MSFTNGP10.phx.gbl...
> > 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]" <robruc@.online.microsoft.com> wrote in message
> > news:e1$dlvyiEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > > 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" <booksnore2@.netscape.net> wrote in message
> > > news:%23dZ118wiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>