Problem defining a query parameter in an Oracle connection

Hi all...
I have an Oracle Type connection whose connection string is only:
data source=Pluton.
I created a dataset with a query parameter, this way:
SELECT NUMPOL
FROM POLIZA
WHERE NUMPOL = @.NUMERO_POLIZA
Here I have got some problems:
1.- The parameter wasn't created automatically, so I had to go to the query
properties and under Parameters tab I added:
Name: @.NUMERO_POLIZA
Value: =Parameters!NUMERO_POLIZA.Value
NUMERO_POLIZA parameter is defined this way:
Name: NUMERO_POLIZA
Prompt: "Número de Póliza"
Data Type: Integer
Available values: non-queried (list empty)
Default values: None
2.- When I run the query, a popup dialog is shown that lets me to define
Query Parameters. @.NUMERO_POLIZA is listed there with a combobox at the
Parameter Value column. I entered a number in that field and pressed OK.
Immediately an popup error is shown: ORA-00936: missing expression
It seems that the query reachs Oracle provider with the name @.NUMERO_POLIZA,
not the value of it.
I tried using OLEDB provider instead. In that case, query parameters are
specified using "?" (interrrogation mark). When I used it and ran the query,
after specifying the query parameter value, query is executed correctly. No
problem, but when I preview the report, and enter the parameter, #Error word
appears instead of a field resulting from the query.
Any help would be greatly appreciated (I want the Oracle type connection to
work, since I have read this is the most efficient method)
Thanks
JaimeOracle has a few unique things going on. First, my recommendation is to use
the generic data designer (2 panes). The button to switch to this is to the
right of the ...
Second, because the development environment was not designed for managed
providers they got tricky with what is used under the covers (hence my
recommendation to use the generic designer). Here is a description from
Robert Bruckner [MSFT].
/Snip
Note: the behavior of PREVIEW in Report Designer is identical to the
ReportServer behavior! However the DATA view in Report Designer is
different for the visual designer: * the visual query designer with 4 panes
will internally always use OleDB providers for verifying and executing
queries directly in "Data" view. (Main reason: the visual query designer
does not work with managed providers). Example: if you choose "Oracle" in
the data source dialog, the Data view has to use the OleDB provider for
Oracle behind the scenes, but Preview and Server will use the managed Oracle
provider. The generic text-based query designer (2 panes) will _always_ use
the data provider you specified.
/End Snip
Just a little background for you. OK, now, from the generic query designer.
He then had this to say about stored procedures:
/Snip
In addition, how do you return the data from your stored procedure? Note:
only an out ref cursor is supported. Please follow the guidelines in the
following article on MSDN (scroll down to the section where it talks about
"Oracle REF CURSORs") on how to design the Oracle stored procedure:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
To use a stored procedure with regular out parameters, you should either
remove the parameter (if it is possible) or write a little wrapper around
the original stored procedure which checks the result of the out parameter
and just returns the out ref cursor but no out parameter. Finally, in the
generic query designer, just specify the name of the stored procedure
without arguments and the parameters should get detected automatically.
/End Snip
Hope that helps. Definitely not intuitive but it works.
One last thing. The MS managed provider for Oracle need 8.1.7 or higher (8i)
client installed for it to work.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
news:C534517B-A9A4-483E-AB41-2348FC3914AC@.microsoft.com...
> Hi all...
> I have an Oracle Type connection whose connection string is only:
> data source=Pluton.
> I created a dataset with a query parameter, this way:
> SELECT NUMPOL
> FROM POLIZA
> WHERE NUMPOL = @.NUMERO_POLIZA
> Here I have got some problems:
> 1.- The parameter wasn't created automatically, so I had to go to the
> query
> properties and under Parameters tab I added:
> Name: @.NUMERO_POLIZA
> Value: =Parameters!NUMERO_POLIZA.Value
> NUMERO_POLIZA parameter is defined this way:
> Name: NUMERO_POLIZA
> Prompt: "Número de Póliza"
> Data Type: Integer
> Available values: non-queried (list empty)
> Default values: None
> 2.- When I run the query, a popup dialog is shown that lets me to define
> Query Parameters. @.NUMERO_POLIZA is listed there with a combobox at the
> Parameter Value column. I entered a number in that field and pressed OK.
> Immediately an popup error is shown: ORA-00936: missing expression
> It seems that the query reachs Oracle provider with the name
> @.NUMERO_POLIZA,
> not the value of it.
> I tried using OLEDB provider instead. In that case, query parameters are
> specified using "?" (interrrogation mark). When I used it and ran the
> query,
> after specifying the query parameter value, query is executed correctly.
> No
> problem, but when I preview the report, and enter the parameter, #Error
> word
> appears instead of a field resulting from the query.
> Any help would be greatly appreciated (I want the Oracle type connection
> to
> work, since I have read this is the most efficient method)
> Thanks
> Jaime|||Just a few additions to what Bruce said already:
Managed Oracle provider (named parameters):
select * from table where ename = :parameter
OleDB for Oracle (unnamed parameters):
select * from table where ename = ?
The managed Oracle data provider uses a ':' to mark named parameters
(instead of '@.'); the OleDB provider for Oracle only allows unnamed
parameters (using '?'). The following KB article explains more details:
http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
Note: the Visual Data Tools (VDT) query designer (2 panes) actually uses OLE
DB in the preview pane. The text-based generic query designer (GQD; 4 panes)
uses the .NET provider for Oracle. Generally, you will achieve better
results when using GQD with Oracle.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:O4c3omYfFHA.2444@.tk2msftngp13.phx.gbl...
> Oracle has a few unique things going on. First, my recommendation is to
> use the generic data designer (2 panes). The button to switch to this is
> to the right of the ...
> Second, because the development environment was not designed for managed
> providers they got tricky with what is used under the covers (hence my
> recommendation to use the generic designer). Here is a description from
> Robert Bruckner [MSFT].
> /Snip
> Note: the behavior of PREVIEW in Report Designer is identical to the
> ReportServer behavior! However the DATA view in Report Designer is
> different for the visual designer: * the visual query designer with 4
> panes will internally always use OleDB providers for verifying and
> executing queries directly in "Data" view. (Main reason: the visual query
> designer does not work with managed providers). Example: if you choose
> "Oracle" in the data source dialog, the Data view has to use the OleDB
> provider for Oracle behind the scenes, but Preview and Server will use the
> managed Oracle provider. The generic text-based query designer (2 panes)
> will _always_ use the data provider you specified.
> /End Snip
> Just a little background for you. OK, now, from the generic query
> designer. He then had this to say about stored procedures:
> /Snip
> In addition, how do you return the data from your stored procedure? Note:
> only an out ref cursor is supported. Please follow the guidelines in the
> following article on MSDN (scroll down to the section where it talks about
> "Oracle REF CURSORs") on how to design the Oracle stored procedure:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
> To use a stored procedure with regular out parameters, you should either
> remove the parameter (if it is possible) or write a little wrapper around
> the original stored procedure which checks the result of the out parameter
> and just returns the out ref cursor but no out parameter. Finally, in the
> generic query designer, just specify the name of the stored procedure
> without arguments and the parameters should get detected automatically.
> /End Snip
> Hope that helps. Definitely not intuitive but it works.
> One last thing. The MS managed provider for Oracle need 8.1.7 or higher
> (8i) client installed for it to work.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
> news:C534517B-A9A4-483E-AB41-2348FC3914AC@.microsoft.com...
>> Hi all...
>> I have an Oracle Type connection whose connection string is only:
>> data source=Pluton.
>> I created a dataset with a query parameter, this way:
>> SELECT NUMPOL
>> FROM POLIZA
>> WHERE NUMPOL = @.NUMERO_POLIZA
>> Here I have got some problems:
>> 1.- The parameter wasn't created automatically, so I had to go to the
>> query
>> properties and under Parameters tab I added:
>> Name: @.NUMERO_POLIZA
>> Value: =Parameters!NUMERO_POLIZA.Value
>> NUMERO_POLIZA parameter is defined this way:
>> Name: NUMERO_POLIZA
>> Prompt: "Número de Póliza"
>> Data Type: Integer
>> Available values: non-queried (list empty)
>> Default values: None
>> 2.- When I run the query, a popup dialog is shown that lets me to define
>> Query Parameters. @.NUMERO_POLIZA is listed there with a combobox at the
>> Parameter Value column. I entered a number in that field and pressed OK.
>> Immediately an popup error is shown: ORA-00936: missing expression
>> It seems that the query reachs Oracle provider with the name
>> @.NUMERO_POLIZA,
>> not the value of it.
>> I tried using OLEDB provider instead. In that case, query parameters are
>> specified using "?" (interrrogation mark). When I used it and ran the
>> query,
>> after specifying the query parameter value, query is executed correctly.
>> No
>> problem, but when I preview the report, and enter the parameter, #Error
>> word
>> appears instead of a field resulting from the query.
>> Any help would be greatly appreciated (I want the Oracle type connection
>> to
>> work, since I have read this is the most efficient method)
>> Thanks
>> Jaime
>|||Not to confuse things but VDT is 4 panes and GQD is 2 panes.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:%23%23DFHqYfFHA.3584@.TK2MSFTNGP09.phx.gbl...
> Just a few additions to what Bruce said already:
> Managed Oracle provider (named parameters):
> select * from table where ename = :parameter
> OleDB for Oracle (unnamed parameters):
> select * from table where ename = ?
> The managed Oracle data provider uses a ':' to mark named parameters
> (instead of '@.'); the OleDB provider for Oracle only allows unnamed
> parameters (using '?'). The following KB article explains more details:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
> Note: the Visual Data Tools (VDT) query designer (2 panes) actually uses
> OLE DB in the preview pane. The text-based generic query designer (GQD; 4
> panes) uses the .NET provider for Oracle. Generally, you will achieve
> better results when using GQD with Oracle.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:O4c3omYfFHA.2444@.tk2msftngp13.phx.gbl...
>> Oracle has a few unique things going on. First, my recommendation is to
>> use the generic data designer (2 panes). The button to switch to this is
>> to the right of the ...
>> Second, because the development environment was not designed for managed
>> providers they got tricky with what is used under the covers (hence my
>> recommendation to use the generic designer). Here is a description from
>> Robert Bruckner [MSFT].
>> /Snip
>> Note: the behavior of PREVIEW in Report Designer is identical to the
>> ReportServer behavior! However the DATA view in Report Designer is
>> different for the visual designer: * the visual query designer with 4
>> panes will internally always use OleDB providers for verifying and
>> executing queries directly in "Data" view. (Main reason: the visual query
>> designer does not work with managed providers). Example: if you choose
>> "Oracle" in the data source dialog, the Data view has to use the OleDB
>> provider for Oracle behind the scenes, but Preview and Server will use
>> the managed Oracle provider. The generic text-based query designer (2
>> panes) will _always_ use the data provider you specified.
>> /End Snip
>> Just a little background for you. OK, now, from the generic query
>> designer. He then had this to say about stored procedures:
>> /Snip
>> In addition, how do you return the data from your stored procedure? Note:
>> only an out ref cursor is supported. Please follow the guidelines in the
>> following article on MSDN (scroll down to the section where it talks
>> about "Oracle REF CURSORs") on how to design the Oracle stored procedure:
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
>> To use a stored procedure with regular out parameters, you should either
>> remove the parameter (if it is possible) or write a little wrapper around
>> the original stored procedure which checks the result of the out
>> parameter and just returns the out ref cursor but no out parameter.
>> Finally, in the generic query designer, just specify the name of the
>> stored procedure without arguments and the parameters should get detected
>> automatically.
>> /End Snip
>> Hope that helps. Definitely not intuitive but it works.
>> One last thing. The MS managed provider for Oracle need 8.1.7 or higher
>> (8i) client installed for it to work.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
>> news:C534517B-A9A4-483E-AB41-2348FC3914AC@.microsoft.com...
>> Hi all...
>> I have an Oracle Type connection whose connection string is only:
>> data source=Pluton.
>> I created a dataset with a query parameter, this way:
>> SELECT NUMPOL
>> FROM POLIZA
>> WHERE NUMPOL = @.NUMERO_POLIZA
>> Here I have got some problems:
>> 1.- The parameter wasn't created automatically, so I had to go to the
>> query
>> properties and under Parameters tab I added:
>> Name: @.NUMERO_POLIZA
>> Value: =Parameters!NUMERO_POLIZA.Value
>> NUMERO_POLIZA parameter is defined this way:
>> Name: NUMERO_POLIZA
>> Prompt: "Número de Póliza"
>> Data Type: Integer
>> Available values: non-queried (list empty)
>> Default values: None
>> 2.- When I run the query, a popup dialog is shown that lets me to define
>> Query Parameters. @.NUMERO_POLIZA is listed there with a combobox at the
>> Parameter Value column. I entered a number in that field and pressed OK.
>> Immediately an popup error is shown: ORA-00936: missing expression
>> It seems that the query reachs Oracle provider with the name
>> @.NUMERO_POLIZA,
>> not the value of it.
>> I tried using OLEDB provider instead. In that case, query parameters are
>> specified using "?" (interrrogation mark). When I used it and ran the
>> query,
>> after specifying the query parameter value, query is executed correctly.
>> No
>> problem, but when I preview the report, and enter the parameter, #Error
>> word
>> appears instead of a field resulting from the query.
>> Any help would be greatly appreciated (I want the Oracle type connection
>> to
>> work, since I have read this is the most efficient method)
>> Thanks
>> Jaime
>>
>|||Thanks Bruce and Robert for explanations. I use Generic Designer and replaced
@. by :. When I ran the query, I was finally asked to enter parameters. All
that was fine, but when query tried to execute, I got the error "Fetch out of
sequence" :-( as I asked before in this newsgroup.
What you said makes sense for me now, because when I ran the query in Visual
Designer, it works (using unnamed parameters). That was because it is using
OLEDB provider and the others Oracle provider.
In one of the tests I've made (to try to solve "Fetch out of sequence"
error), I have configured the connection to be OLEDB and queries ran, but I
had problems with parameters. I used "?", but I got very confused about the
usage of the "?" given by the designer. I tried to rename those ? (in
Parameters tab of the DataSet) to something more meaningful, but in that way,
parameters didn't work, so I got back to test using Oracle Provider.
Do you know why I get that error when I run the query? I have read that this
error may occur when Autocommit property of the provider is set to true and
SELECT FOR UPDATE instruction is used, but this is not the case.
Jaime
"Bruce L-C [MVP]" wrote:
> Not to confuse things but VDT is 4 panes and GQD is 2 panes.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:%23%23DFHqYfFHA.3584@.TK2MSFTNGP09.phx.gbl...
> > Just a few additions to what Bruce said already:
> >
> > Managed Oracle provider (named parameters):
> > select * from table where ename = :parameter
> > OleDB for Oracle (unnamed parameters):
> > select * from table where ename = ?
> >
> > The managed Oracle data provider uses a ':' to mark named parameters
> > (instead of '@.'); the OleDB provider for Oracle only allows unnamed
> > parameters (using '?'). The following KB article explains more details:
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
> >
> > Note: the Visual Data Tools (VDT) query designer (2 panes) actually uses
> > OLE DB in the preview pane. The text-based generic query designer (GQD; 4
> > panes) uses the .NET provider for Oracle. Generally, you will achieve
> > better results when using GQD with Oracle.
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:O4c3omYfFHA.2444@.tk2msftngp13.phx.gbl...
> >> Oracle has a few unique things going on. First, my recommendation is to
> >> use the generic data designer (2 panes). The button to switch to this is
> >> to the right of the ...
> >>
> >> Second, because the development environment was not designed for managed
> >> providers they got tricky with what is used under the covers (hence my
> >> recommendation to use the generic designer). Here is a description from
> >> Robert Bruckner [MSFT].
> >> /Snip
> >> Note: the behavior of PREVIEW in Report Designer is identical to the
> >> ReportServer behavior! However the DATA view in Report Designer is
> >> different for the visual designer: * the visual query designer with 4
> >> panes will internally always use OleDB providers for verifying and
> >> executing queries directly in "Data" view. (Main reason: the visual query
> >> designer does not work with managed providers). Example: if you choose
> >> "Oracle" in the data source dialog, the Data view has to use the OleDB
> >> provider for Oracle behind the scenes, but Preview and Server will use
> >> the managed Oracle provider. The generic text-based query designer (2
> >> panes) will _always_ use the data provider you specified.
> >> /End Snip
> >>
> >> Just a little background for you. OK, now, from the generic query
> >> designer. He then had this to say about stored procedures:
> >> /Snip
> >> In addition, how do you return the data from your stored procedure? Note:
> >> only an out ref cursor is supported. Please follow the guidelines in the
> >> following article on MSDN (scroll down to the section where it talks
> >> about "Oracle REF CURSORs") on how to design the Oracle stored procedure:
> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
> >> To use a stored procedure with regular out parameters, you should either
> >> remove the parameter (if it is possible) or write a little wrapper around
> >> the original stored procedure which checks the result of the out
> >> parameter and just returns the out ref cursor but no out parameter.
> >> Finally, in the generic query designer, just specify the name of the
> >> stored procedure without arguments and the parameters should get detected
> >> automatically.
> >> /End Snip
> >>
> >> Hope that helps. Definitely not intuitive but it works.
> >>
> >> One last thing. The MS managed provider for Oracle need 8.1.7 or higher
> >> (8i) client installed for it to work.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
> >> news:C534517B-A9A4-483E-AB41-2348FC3914AC@.microsoft.com...
> >> Hi all...
> >>
> >> I have an Oracle Type connection whose connection string is only:
> >> data source=Pluton.
> >>
> >> I created a dataset with a query parameter, this way:
> >>
> >> SELECT NUMPOL
> >> FROM POLIZA
> >> WHERE NUMPOL = @.NUMERO_POLIZA
> >>
> >> Here I have got some problems:
> >>
> >> 1.- The parameter wasn't created automatically, so I had to go to the
> >> query
> >> properties and under Parameters tab I added:
> >>
> >> Name: @.NUMERO_POLIZA
> >> Value: =Parameters!NUMERO_POLIZA.Value
> >>
> >> NUMERO_POLIZA parameter is defined this way:
> >>
> >> Name: NUMERO_POLIZA
> >> Prompt: "Número de Póliza"
> >> Data Type: Integer
> >> Available values: non-queried (list empty)
> >> Default values: None
> >>
> >> 2.- When I run the query, a popup dialog is shown that lets me to define
> >> Query Parameters. @.NUMERO_POLIZA is listed there with a combobox at the
> >> Parameter Value column. I entered a number in that field and pressed OK.
> >> Immediately an popup error is shown: ORA-00936: missing expression
> >>
> >> It seems that the query reachs Oracle provider with the name
> >> @.NUMERO_POLIZA,
> >> not the value of it.
> >>
> >> I tried using OLEDB provider instead. In that case, query parameters are
> >> specified using "?" (interrrogation mark). When I used it and ran the
> >> query,
> >> after specifying the query parameter value, query is executed correctly.
> >> No
> >> problem, but when I preview the report, and enter the parameter, #Error
> >> word
> >> appears instead of a field resulting from the query.
> >>
> >> Any help would be greatly appreciated (I want the Oracle type connection
> >> to
> >> work, since I have read this is the most efficient method)
> >>
> >> Thanks
> >> Jaime
> >>
> >>
> >
> >
>
>|||I remember your previous post. I believe you are going against a 7.x
database? I assume you do have a recent client installed. What client are
you using? MS requires 8i or greater client. It could be something to do
with the combination here. MS managed provider, Oracle 8i or greater client,
but a very old Oracle database. My suggestion is to stop trying to use the
managed provider and either use OLEDB or use ODBC. I use ODBC against
Sybase and the performance is not a problem. In most cases the amount of
time in rendering exceeds the time retrieving the data by a significant
amount.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
news:BCACE9D7-9F57-4C2A-869F-4BC54F61A43D@.microsoft.com...
> Thanks Bruce and Robert for explanations. I use Generic Designer and
> replaced
> @. by :. When I ran the query, I was finally asked to enter parameters. All
> that was fine, but when query tried to execute, I got the error "Fetch out
> of
> sequence" :-( as I asked before in this newsgroup.
> What you said makes sense for me now, because when I ran the query in
> Visual
> Designer, it works (using unnamed parameters). That was because it is
> using
> OLEDB provider and the others Oracle provider.
> In one of the tests I've made (to try to solve "Fetch out of sequence"
> error), I have configured the connection to be OLEDB and queries ran, but
> I
> had problems with parameters. I used "?", but I got very confused about
> the
> usage of the "?" given by the designer. I tried to rename those ? (in
> Parameters tab of the DataSet) to something more meaningful, but in that
> way,
> parameters didn't work, so I got back to test using Oracle Provider.
> Do you know why I get that error when I run the query? I have read that
> this
> error may occur when Autocommit property of the provider is set to true
> and
> SELECT FOR UPDATE instruction is used, but this is not the case.
> Jaime
> "Bruce L-C [MVP]" wrote:
>> Not to confuse things but VDT is 4 panes and GQD is 2 panes.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
>> news:%23%23DFHqYfFHA.3584@.TK2MSFTNGP09.phx.gbl...
>> > Just a few additions to what Bruce said already:
>> >
>> > Managed Oracle provider (named parameters):
>> > select * from table where ename = :parameter
>> > OleDB for Oracle (unnamed parameters):
>> > select * from table where ename = ?
>> >
>> > The managed Oracle data provider uses a ':' to mark named parameters
>> > (instead of '@.'); the OleDB provider for Oracle only allows unnamed
>> > parameters (using '?'). The following KB article explains more details:
>> > http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
>> >
>> > Note: the Visual Data Tools (VDT) query designer (2 panes) actually
>> > uses
>> > OLE DB in the preview pane. The text-based generic query designer (GQD;
>> > 4
>> > panes) uses the .NET provider for Oracle. Generally, you will achieve
>> > better results when using GQD with Oracle.
>> >
>> > -- Robert
>> > This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> >
>> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> > news:O4c3omYfFHA.2444@.tk2msftngp13.phx.gbl...
>> >> Oracle has a few unique things going on. First, my recommendation is
>> >> to
>> >> use the generic data designer (2 panes). The button to switch to this
>> >> is
>> >> to the right of the ...
>> >>
>> >> Second, because the development environment was not designed for
>> >> managed
>> >> providers they got tricky with what is used under the covers (hence my
>> >> recommendation to use the generic designer). Here is a description
>> >> from
>> >> Robert Bruckner [MSFT].
>> >> /Snip
>> >> Note: the behavior of PREVIEW in Report Designer is identical to the
>> >> ReportServer behavior! However the DATA view in Report Designer is
>> >> different for the visual designer: * the visual query designer with 4
>> >> panes will internally always use OleDB providers for verifying and
>> >> executing queries directly in "Data" view. (Main reason: the visual
>> >> query
>> >> designer does not work with managed providers). Example: if you choose
>> >> "Oracle" in the data source dialog, the Data view has to use the OleDB
>> >> provider for Oracle behind the scenes, but Preview and Server will use
>> >> the managed Oracle provider. The generic text-based query designer (2
>> >> panes) will _always_ use the data provider you specified.
>> >> /End Snip
>> >>
>> >> Just a little background for you. OK, now, from the generic query
>> >> designer. He then had this to say about stored procedures:
>> >> /Snip
>> >> In addition, how do you return the data from your stored procedure?
>> >> Note:
>> >> only an out ref cursor is supported. Please follow the guidelines in
>> >> the
>> >> following article on MSDN (scroll down to the section where it talks
>> >> about "Oracle REF CURSORs") on how to design the Oracle stored
>> >> procedure:
>> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
>> >> To use a stored procedure with regular out parameters, you should
>> >> either
>> >> remove the parameter (if it is possible) or write a little wrapper
>> >> around
>> >> the original stored procedure which checks the result of the out
>> >> parameter and just returns the out ref cursor but no out parameter.
>> >> Finally, in the generic query designer, just specify the name of the
>> >> stored procedure without arguments and the parameters should get
>> >> detected
>> >> automatically.
>> >> /End Snip
>> >>
>> >> Hope that helps. Definitely not intuitive but it works.
>> >>
>> >> One last thing. The MS managed provider for Oracle need 8.1.7 or
>> >> higher
>> >> (8i) client installed for it to work.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:C534517B-A9A4-483E-AB41-2348FC3914AC@.microsoft.com...
>> >> Hi all...
>> >>
>> >> I have an Oracle Type connection whose connection string is only:
>> >> data source=Pluton.
>> >>
>> >> I created a dataset with a query parameter, this way:
>> >>
>> >> SELECT NUMPOL
>> >> FROM POLIZA
>> >> WHERE NUMPOL = @.NUMERO_POLIZA
>> >>
>> >> Here I have got some problems:
>> >>
>> >> 1.- The parameter wasn't created automatically, so I had to go to the
>> >> query
>> >> properties and under Parameters tab I added:
>> >>
>> >> Name: @.NUMERO_POLIZA
>> >> Value: =Parameters!NUMERO_POLIZA.Value
>> >>
>> >> NUMERO_POLIZA parameter is defined this way:
>> >>
>> >> Name: NUMERO_POLIZA
>> >> Prompt: "Número de Póliza"
>> >> Data Type: Integer
>> >> Available values: non-queried (list empty)
>> >> Default values: None
>> >>
>> >> 2.- When I run the query, a popup dialog is shown that lets me to
>> >> define
>> >> Query Parameters. @.NUMERO_POLIZA is listed there with a combobox at
>> >> the
>> >> Parameter Value column. I entered a number in that field and pressed
>> >> OK.
>> >> Immediately an popup error is shown: ORA-00936: missing expression
>> >>
>> >> It seems that the query reachs Oracle provider with the name
>> >> @.NUMERO_POLIZA,
>> >> not the value of it.
>> >>
>> >> I tried using OLEDB provider instead. In that case, query parameters
>> >> are
>> >> specified using "?" (interrrogation mark). When I used it and ran the
>> >> query,
>> >> after specifying the query parameter value, query is executed
>> >> correctly.
>> >> No
>> >> problem, but when I preview the report, and enter the parameter,
>> >> #Error
>> >> word
>> >> appears instead of a field resulting from the query.
>> >>
>> >> Any help would be greatly appreciated (I want the Oracle type
>> >> connection
>> >> to
>> >> work, since I have read this is the most efficient method)
>> >>
>> >> Thanks
>> >> Jaime
>> >>
>> >>
>> >
>> >
>>|||Thanks bruce...You were right, I'm using Oracle 9i client trying to connect
to Oracle 7.3.4 database. I have finally solved the problem using OLEDB
provider. But the solution wasn't that trivial. When I deployed the report
to IIS, I got so many different and strange errors. All that errors were due
to permissions problems of the IUSR_machine user to Oracle directory. At
last, I could configure all so that I can view reports both in preview mode
and in web. Thanks again.
Jaime
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OflioZafFHA.3256@.TK2MSFTNGP12.phx.gbl...
>I remember your previous post. I believe you are going against a 7.x
>database? I assume you do have a recent client installed. What client are
>you using? MS requires 8i or greater client. It could be something to do
>with the combination here. MS managed provider, Oracle 8i or greater
>client, but a very old Oracle database. My suggestion is to stop trying to
>use the managed provider and either use OLEDB or use ODBC. I use ODBC
>against Sybase and the performance is not a problem. In most cases the
>amount of time in rendering exceeds the time retrieving the data by a
>significant amount.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
> news:BCACE9D7-9F57-4C2A-869F-4BC54F61A43D@.microsoft.com...
>> Thanks Bruce and Robert for explanations. I use Generic Designer and
>> replaced
>> @. by :. When I ran the query, I was finally asked to enter parameters.
>> All
>> that was fine, but when query tried to execute, I got the error "Fetch
>> out of
>> sequence" :-( as I asked before in this newsgroup.
>> What you said makes sense for me now, because when I ran the query in
>> Visual
>> Designer, it works (using unnamed parameters). That was because it is
>> using
>> OLEDB provider and the others Oracle provider.
>> In one of the tests I've made (to try to solve "Fetch out of sequence"
>> error), I have configured the connection to be OLEDB and queries ran, but
>> I
>> had problems with parameters. I used "?", but I got very confused about
>> the
>> usage of the "?" given by the designer. I tried to rename those ? (in
>> Parameters tab of the DataSet) to something more meaningful, but in that
>> way,
>> parameters didn't work, so I got back to test using Oracle Provider.
>> Do you know why I get that error when I run the query? I have read that
>> this
>> error may occur when Autocommit property of the provider is set to true
>> and
>> SELECT FOR UPDATE instruction is used, but this is not the case.
>> Jaime
>> "Bruce L-C [MVP]" wrote:
>> Not to confuse things but VDT is 4 panes and GQD is 2 panes.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
>> news:%23%23DFHqYfFHA.3584@.TK2MSFTNGP09.phx.gbl...
>> > Just a few additions to what Bruce said already:
>> >
>> > Managed Oracle provider (named parameters):
>> > select * from table where ename = :parameter
>> > OleDB for Oracle (unnamed parameters):
>> > select * from table where ename = ?
>> >
>> > The managed Oracle data provider uses a ':' to mark named parameters
>> > (instead of '@.'); the OleDB provider for Oracle only allows unnamed
>> > parameters (using '?'). The following KB article explains more
>> > details:
>> > http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
>> >
>> > Note: the Visual Data Tools (VDT) query designer (2 panes) actually
>> > uses
>> > OLE DB in the preview pane. The text-based generic query designer
>> > (GQD; 4
>> > panes) uses the .NET provider for Oracle. Generally, you will achieve
>> > better results when using GQD with Oracle.
>> >
>> > -- Robert
>> > This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> >
>> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> > news:O4c3omYfFHA.2444@.tk2msftngp13.phx.gbl...
>> >> Oracle has a few unique things going on. First, my recommendation is
>> >> to
>> >> use the generic data designer (2 panes). The button to switch to this
>> >> is
>> >> to the right of the ...
>> >>
>> >> Second, because the development environment was not designed for
>> >> managed
>> >> providers they got tricky with what is used under the covers (hence
>> >> my
>> >> recommendation to use the generic designer). Here is a description
>> >> from
>> >> Robert Bruckner [MSFT].
>> >> /Snip
>> >> Note: the behavior of PREVIEW in Report Designer is identical to the
>> >> ReportServer behavior! However the DATA view in Report Designer is
>> >> different for the visual designer: * the visual query designer with
>> >> 4
>> >> panes will internally always use OleDB providers for verifying and
>> >> executing queries directly in "Data" view. (Main reason: the visual
>> >> query
>> >> designer does not work with managed providers). Example: if you
>> >> choose
>> >> "Oracle" in the data source dialog, the Data view has to use the
>> >> OleDB
>> >> provider for Oracle behind the scenes, but Preview and Server will
>> >> use
>> >> the managed Oracle provider. The generic text-based query designer (2
>> >> panes) will _always_ use the data provider you specified.
>> >> /End Snip
>> >>
>> >> Just a little background for you. OK, now, from the generic query
>> >> designer. He then had this to say about stored procedures:
>> >> /Snip
>> >> In addition, how do you return the data from your stored procedure?
>> >> Note:
>> >> only an out ref cursor is supported. Please follow the guidelines in
>> >> the
>> >> following article on MSDN (scroll down to the section where it talks
>> >> about "Oracle REF CURSORs") on how to design the Oracle stored
>> >> procedure:
>> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
>> >> To use a stored procedure with regular out parameters, you should
>> >> either
>> >> remove the parameter (if it is possible) or write a little wrapper
>> >> around
>> >> the original stored procedure which checks the result of the out
>> >> parameter and just returns the out ref cursor but no out parameter.
>> >> Finally, in the generic query designer, just specify the name of the
>> >> stored procedure without arguments and the parameters should get
>> >> detected
>> >> automatically.
>> >> /End Snip
>> >>
>> >> Hope that helps. Definitely not intuitive but it works.
>> >>
>> >> One last thing. The MS managed provider for Oracle need 8.1.7 or
>> >> higher
>> >> (8i) client installed for it to work.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:C534517B-A9A4-483E-AB41-2348FC3914AC@.microsoft.com...
>> >> Hi all...
>> >>
>> >> I have an Oracle Type connection whose connection string is only:
>> >> data source=Pluton.
>> >>
>> >> I created a dataset with a query parameter, this way:
>> >>
>> >> SELECT NUMPOL
>> >> FROM POLIZA
>> >> WHERE NUMPOL = @.NUMERO_POLIZA
>> >>
>> >> Here I have got some problems:
>> >>
>> >> 1.- The parameter wasn't created automatically, so I had to go to
>> >> the
>> >> query
>> >> properties and under Parameters tab I added:
>> >>
>> >> Name: @.NUMERO_POLIZA
>> >> Value: =Parameters!NUMERO_POLIZA.Value
>> >>
>> >> NUMERO_POLIZA parameter is defined this way:
>> >>
>> >> Name: NUMERO_POLIZA
>> >> Prompt: "Número de Póliza"
>> >> Data Type: Integer
>> >> Available values: non-queried (list empty)
>> >> Default values: None
>> >>
>> >> 2.- When I run the query, a popup dialog is shown that lets me to
>> >> define
>> >> Query Parameters. @.NUMERO_POLIZA is listed there with a combobox at
>> >> the
>> >> Parameter Value column. I entered a number in that field and pressed
>> >> OK.
>> >> Immediately an popup error is shown: ORA-00936: missing expression
>> >>
>> >> It seems that the query reachs Oracle provider with the name
>> >> @.NUMERO_POLIZA,
>> >> not the value of it.
>> >>
>> >> I tried using OLEDB provider instead. In that case, query parameters
>> >> are
>> >> specified using "?" (interrrogation mark). When I used it and ran
>> >> the
>> >> query,
>> >> after specifying the query parameter value, query is executed
>> >> correctly.
>> >> No
>> >> problem, but when I preview the report, and enter the parameter,
>> >> #Error
>> >> word
>> >> appears instead of a field resulting from the query.
>> >>
>> >> Any help would be greatly appreciated (I want the Oracle type
>> >> connection
>> >> to
>> >> work, since I have read this is the most efficient method)
>> >>
>> >> Thanks
>> >> Jaime
>> >>
>> >>
>> >
>> >
>>
>|||Please refer to my post.I am stuck with the same issue.
As of now I have been able to run Oracle SP in Generic designer.But when I
deploy it on reporting services, does not populate with value at all.
my post is at
http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?pg=2&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&fltr=
"Jaime Stuardo" wrote:
> Thanks bruce...You were right, I'm using Oracle 9i client trying to connect
> to Oracle 7.3.4 database. I have finally solved the problem using OLEDB
> provider. But the solution wasn't that trivial. When I deployed the report
> to IIS, I got so many different and strange errors. All that errors were due
> to permissions problems of the IUSR_machine user to Oracle directory. At
> last, I could configure all so that I can view reports both in preview mode
> and in web. Thanks again.
> Jaime
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OflioZafFHA.3256@.TK2MSFTNGP12.phx.gbl...
> >I remember your previous post. I believe you are going against a 7.x
> >database? I assume you do have a recent client installed. What client are
> >you using? MS requires 8i or greater client. It could be something to do
> >with the combination here. MS managed provider, Oracle 8i or greater
> >client, but a very old Oracle database. My suggestion is to stop trying to
> >use the managed provider and either use OLEDB or use ODBC. I use ODBC
> >against Sybase and the performance is not a problem. In most cases the
> >amount of time in rendering exceeds the time retrieving the data by a
> >significant amount.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
> > news:BCACE9D7-9F57-4C2A-869F-4BC54F61A43D@.microsoft.com...
> >> Thanks Bruce and Robert for explanations. I use Generic Designer and
> >> replaced
> >> @. by :. When I ran the query, I was finally asked to enter parameters.
> >> All
> >> that was fine, but when query tried to execute, I got the error "Fetch
> >> out of
> >> sequence" :-( as I asked before in this newsgroup.
> >>
> >> What you said makes sense for me now, because when I ran the query in
> >> Visual
> >> Designer, it works (using unnamed parameters). That was because it is
> >> using
> >> OLEDB provider and the others Oracle provider.
> >>
> >> In one of the tests I've made (to try to solve "Fetch out of sequence"
> >> error), I have configured the connection to be OLEDB and queries ran, but
> >> I
> >> had problems with parameters. I used "?", but I got very confused about
> >> the
> >> usage of the "?" given by the designer. I tried to rename those ? (in
> >> Parameters tab of the DataSet) to something more meaningful, but in that
> >> way,
> >> parameters didn't work, so I got back to test using Oracle Provider.
> >>
> >> Do you know why I get that error when I run the query? I have read that
> >> this
> >> error may occur when Autocommit property of the provider is set to true
> >> and
> >> SELECT FOR UPDATE instruction is used, but this is not the case.
> >>
> >> Jaime
> >>
> >> "Bruce L-C [MVP]" wrote:
> >>
> >> Not to confuse things but VDT is 4 panes and GQD is 2 panes.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> >> news:%23%23DFHqYfFHA.3584@.TK2MSFTNGP09.phx.gbl...
> >> > Just a few additions to what Bruce said already:
> >> >
> >> > Managed Oracle provider (named parameters):
> >> > select * from table where ename = :parameter
> >> > OleDB for Oracle (unnamed parameters):
> >> > select * from table where ename = ?
> >> >
> >> > The managed Oracle data provider uses a ':' to mark named parameters
> >> > (instead of '@.'); the OleDB provider for Oracle only allows unnamed
> >> > parameters (using '?'). The following KB article explains more
> >> > details:
> >> > http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
> >> >
> >> > Note: the Visual Data Tools (VDT) query designer (2 panes) actually
> >> > uses
> >> > OLE DB in the preview pane. The text-based generic query designer
> >> > (GQD; 4
> >> > panes) uses the .NET provider for Oracle. Generally, you will achieve
> >> > better results when using GQD with Oracle.
> >> >
> >> > -- Robert
> >> > This posting is provided "AS IS" with no warranties, and confers no
> >> > rights.
> >> >
> >> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> >> > news:O4c3omYfFHA.2444@.tk2msftngp13.phx.gbl...
> >> >> Oracle has a few unique things going on. First, my recommendation is
> >> >> to
> >> >> use the generic data designer (2 panes). The button to switch to this
> >> >> is
> >> >> to the right of the ...
> >> >>
> >> >> Second, because the development environment was not designed for
> >> >> managed
> >> >> providers they got tricky with what is used under the covers (hence
> >> >> my
> >> >> recommendation to use the generic designer). Here is a description
> >> >> from
> >> >> Robert Bruckner [MSFT].
> >> >> /Snip
> >> >> Note: the behavior of PREVIEW in Report Designer is identical to the
> >> >> ReportServer behavior! However the DATA view in Report Designer is
> >> >> different for the visual designer: * the visual query designer with
> >> >> 4
> >> >> panes will internally always use OleDB providers for verifying and
> >> >> executing queries directly in "Data" view. (Main reason: the visual
> >> >> query
> >> >> designer does not work with managed providers). Example: if you
> >> >> choose
> >> >> "Oracle" in the data source dialog, the Data view has to use the
> >> >> OleDB
> >> >> provider for Oracle behind the scenes, but Preview and Server will
> >> >> use
> >> >> the managed Oracle provider. The generic text-based query designer (2
> >> >> panes) will _always_ use the data provider you specified.
> >> >> /End Snip
> >> >>
> >> >> Just a little background for you. OK, now, from the generic query
> >> >> designer. He then had this to say about stored procedures:
> >> >> /Snip
> >> >> In addition, how do you return the data from your stored procedure?
> >> >> Note:
> >> >> only an out ref cursor is supported. Please follow the guidelines in
> >> >> the
> >> >> following article on MSDN (scroll down to the section where it talks
> >> >> about "Oracle REF CURSORs") on how to design the Oracle stored
> >> >> procedure:
> >> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
> >> >> To use a stored procedure with regular out parameters, you should
> >> >> either
> >> >> remove the parameter (if it is possible) or write a little wrapper
> >> >> around
> >> >> the original stored procedure which checks the result of the out
> >> >> parameter and just returns the out ref cursor but no out parameter.
> >> >> Finally, in the generic query designer, just specify the name of the
> >> >> stored procedure without arguments and the parameters should get
> >> >> detected
> >> >> automatically.
> >> >> /End Snip
> >> >>
> >> >> Hope that helps. Definitely not intuitive but it works.
> >> >>
> >> >> One last thing. The MS managed provider for Oracle need 8.1.7 or
> >> >> higher
> >> >> (8i) client installed for it to work.
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:C534517B-A9A4-483E-AB41-2348FC3914AC@.microsoft.com...
> >> >> Hi all...
> >> >>
> >> >> I have an Oracle Type connection whose connection string is only:
> >> >> data source=Pluton.
> >> >>
> >> >> I created a dataset with a query parameter, this way:
> >> >>
> >> >> SELECT NUMPOL
> >> >> FROM POLIZA
> >> >> WHERE NUMPOL = @.NUMERO_POLIZA
> >> >>
> >> >> Here I have got some problems:
> >> >>
> >> >> 1.- The parameter wasn't created automatically, so I had to go to
> >> >> the
> >> >> query
> >> >> properties and under Parameters tab I added:
> >> >>
> >> >> Name: @.NUMERO_POLIZA
> >> >> Value: =Parameters!NUMERO_POLIZA.Value
> >> >>
> >> >> NUMERO_POLIZA parameter is defined this way:
> >> >>
> >> >> Name: NUMERO_POLIZA
> >> >> Prompt: "Número de Póliza"
> >> >> Data Type: Integer
> >> >> Available values: non-queried (list empty)
> >> >> Default values: None
> >> >>
> >> >> 2.- When I run the query, a popup dialog is shown that lets me to
> >> >> define
> >> >> Query Parameters. @.NUMERO_POLIZA is listed there with a combobox at
> >> >> the
> >> >> Parameter Value column. I entered a number in that field and pressed
> >> >> OK.
> >> >> Immediately an popup error is shown: ORA-00936: missing expression
> >> >>
> >> >> It seems that the query reachs Oracle provider with the name
> >> >> @.NUMERO_POLIZA,
> >> >> not the value of it.
> >> >>
> >> >> I tried using OLEDB provider instead. In that case, query parameters
> >> >> are
> >> >> specified using "?" (interrrogation mark). When I used it and ran
> >> >> the
> >> >> query,
> >> >> after specifying the query parameter value, query is executed
> >> >> correctly.
> >> >> No
> >> >> problem, but when I preview the report, and enter the parameter,
> >> >> #Error
> >> >> word
> >> >> appears instead of a field resulting from the query.
> >> >>
> >> >> Any help would be greatly appreciated (I want the Oracle type
> >> >> connection
> >> >> to
> >> >> work, since I have read this is the most efficient method)
> >> >>
> >> >> Thanks
> >> >> Jaime
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >>
> >
> >
>
>