Oracle DB Link in Reporting Services Query String

I am trying to select for a report some data from an Oracle table through a
DB Link, using a select statement:
SELECT a.filed1, a.field2, dbl.field3, dbl.field4
FROM table_name1 a, table_name2@.db_link_name dbl
WHERE a.filed1=dbl.filed5
However, the SQL Report Wizard returns an error â'Invalid characterâ'
(selected data source is ORACLE type). Apparently, it doesnâ't like â'@.â'
character in the string. To find a way around I have tried to create an
Oracle stored procedure which returns Oracle REF Cursor created using the
same select statement. The procedure was compiled and I changed data type in
the dataset to â'Stored Procedureâ' and query string to the procedure name.
But, again, a failure was received since it canâ't refresh the list of
parameters and fields.
Excluding reference to the DB Link from both the Select statement and the
stored procedure solves the problem, but I need the data from it.
Is there another way to retrieve the data for a report through an Oracle DB
Link?Two things to try. First off, did you do this via the generic query
designer. If not try that. Next, what happens if you put double quotes
around it. I.e.
SELECT a.filed1, a.field2, dbl.field3, dbl.field4 FROM table_name1 a,
"table_name2@.db_link_name" dbl WHERE a.filed1=dbl.filed5
Just a wild guess whether the double quote would work.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Anatoliy" <Anatoliy@.discussions.microsoft.com> wrote in message
news:19E95C96-1078-4E89-87D6-95420A8D4184@.microsoft.com...
> I am trying to select for a report some data from an Oracle table through
a
> DB Link, using a select statement:
> SELECT a.filed1, a.field2, dbl.field3, dbl.field4
> FROM table_name1 a, table_name2@.db_link_name dbl
> WHERE a.filed1=dbl.filed5
> However, the SQL Report Wizard returns an error "Invalid character"
> (selected data source is ORACLE type). Apparently, it doesn't like "@."
> character in the string. To find a way around I have tried to create an
> Oracle stored procedure which returns Oracle REF Cursor created using the
> same select statement. The procedure was compiled and I changed data type
in
> the dataset to "Stored Procedure" and query string to the procedure name.
> But, again, a failure was received since it can't refresh the list of
> parameters and fields.
> Excluding reference to the DB Link from both the Select statement and the
> stored procedure solves the problem, but I need the data from it.
> Is there another way to retrieve the data for a report through an Oracle
DB
> Link?
>|||Thanks Bruce,
I am afraid, I canâ't use the generic query designer, since it canâ't access a
table though a db link. I can only add a table or a view in the graphical
schema representation.
After I tried to edit the Select statement and put a double quote the syntax
problem was resolved, however I canâ't even save the query string, because of
an error message:
â'Couldnâ't generate a list of fields for the query.â' A similar message was
received when I was trying to use a stored procedure with ref cursor as OUT
parameter. It looks like because the db link table column list canâ't be
generated a critical run time error is raised and it stops execution of the
procedure.
Is there any way to solve this problem, for example to try to enter the list
of parameters and fields manually?
"Bruce L-C [MVP]" wrote:
> Two things to try. First off, did you do this via the generic query
> designer. If not try that. Next, what happens if you put double quotes
> around it. I.e.
> SELECT a.filed1, a.field2, dbl.field3, dbl.field4 FROM table_name1 a,
> "table_name2@.db_link_name" dbl WHERE a.filed1=dbl.filed5
> Just a wild guess whether the double quote would work.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Anatoliy" <Anatoliy@.discussions.microsoft.com> wrote in message
> news:19E95C96-1078-4E89-87D6-95420A8D4184@.microsoft.com...
> > I am trying to select for a report some data from an Oracle table through
> a
> > DB Link, using a select statement:
> >
> > SELECT a.filed1, a.field2, dbl.field3, dbl.field4
> > FROM table_name1 a, table_name2@.db_link_name dbl
> > WHERE a.filed1=dbl.filed5
> >
> > However, the SQL Report Wizard returns an error "Invalid character"
> > (selected data source is ORACLE type). Apparently, it doesn't like "@."
> > character in the string. To find a way around I have tried to create an
> > Oracle stored procedure which returns Oracle REF Cursor created using the
> > same select statement. The procedure was compiled and I changed data type
> in
> > the dataset to "Stored Procedure" and query string to the procedure name.
> > But, again, a failure was received since it can't refresh the list of
> > parameters and fields.
> >
> > Excluding reference to the DB Link from both the Select statement and the
> > stored procedure solves the problem, but I need the data from it.
> >
> > Is there another way to retrieve the data for a report through an Oracle
> DB
> > Link?
> >
> >
>
>|||There are some unusual things going on with Oracle. When you pick the Oracle
provider it is using Oledb provider when you are in the graphical query
designer. If you are in the generic query designer it is using the dotnet
provider. Plus, when deployed it will be using the dotnet provider. The
reason for all of this is that the graphical query designer component is a
shared component that knows nothing about dotnet. Hopefully with Widbey this
will no longer be true but for now, what I said is try.
Now, the generic query designer should act like a passthrough query. Have
you tried it since you started putting in the double quotes?
You can definitely add the fields manually (right click on the fields list).
The graphical designer does a lot with your query. That is why I suggested
the generic. I would spend some more time in the generic trying to get it to
work (generic plus adding the fields manually is the most likely combination
to work). You can always view the rdl and put the query string in that way.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Anatoliy" <Anatoliy@.discussions.microsoft.com> wrote in message
news:F03A20D6-FE5E-479A-93B0-BBCD0B9DAA3C@.microsoft.com...
> Thanks Bruce,
> I am afraid, I can't use the generic query designer, since it can't access
a
> table though a db link. I can only add a table or a view in the graphical
> schema representation.
> After I tried to edit the Select statement and put a double quote the
syntax
> problem was resolved, however I can't even save the query string, because
of
> an error message:
> "Couldn't generate a list of fields for the query." A similar message was
> received when I was trying to use a stored procedure with ref cursor as
OUT
> parameter. It looks like because the db link table column list can't be
> generated a critical run time error is raised and it stops execution of
the
> procedure.
> Is there any way to solve this problem, for example to try to enter the
list
> of parameters and fields manually?
>
> "Bruce L-C [MVP]" wrote:
> > Two things to try. First off, did you do this via the generic query
> > designer. If not try that. Next, what happens if you put double quotes
> > around it. I.e.
> >
> > SELECT a.filed1, a.field2, dbl.field3, dbl.field4 FROM table_name1 a,
> > "table_name2@.db_link_name" dbl WHERE a.filed1=dbl.filed5
> >
> > Just a wild guess whether the double quote would work.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Anatoliy" <Anatoliy@.discussions.microsoft.com> wrote in message
> > news:19E95C96-1078-4E89-87D6-95420A8D4184@.microsoft.com...
> > > I am trying to select for a report some data from an Oracle table
through
> > a
> > > DB Link, using a select statement:
> > >
> > > SELECT a.filed1, a.field2, dbl.field3, dbl.field4
> > > FROM table_name1 a, table_name2@.db_link_name dbl
> > > WHERE a.filed1=dbl.filed5
> > >
> > > However, the SQL Report Wizard returns an error "Invalid character"
> > > (selected data source is ORACLE type). Apparently, it doesn't like
"@."
> > > character in the string. To find a way around I have tried to create
an
> > > Oracle stored procedure which returns Oracle REF Cursor created using
the
> > > same select statement. The procedure was compiled and I changed data
type
> > in
> > > the dataset to "Stored Procedure" and query string to the procedure
name.
> > > But, again, a failure was received since it can't refresh the list of
> > > parameters and fields.
> > >
> > > Excluding reference to the DB Link from both the Select statement and
the
> > > stored procedure solves the problem, but I need the data from it.
> > >
> > > Is there another way to retrieve the data for a report through an
Oracle
> > DB
> > > Link?
> > >
> > >
> >
> >
> >