Home » Developer & Programmer » Reports & Discoverer » Issue to connect to remote database (Report Builder 10.1.2.0.2)
Issue to connect to remote database [message #517205] Sun, 24 July 2011 10:08 Go to next message
averion
Messages: 42
Registered: January 2009
Location: US
Member
Hello Experts,

I am using Report Builder 10.1.2.0.2.

There is a table in a remote database with synonyms/links defined.
I am getting error trying to access those tables. DBA says links/synonyms are correct and I am also able to access that table from SQLTool without error.

I can do a select statement on those tables from 'SQL Qurey statement' in datamodel.

Error is generated only if I try to use those tables in 'Formula'
or in function/procedure.

I don't know if this is a bug or something else.

Thanks a lot in advance for sharing some ideas on this.

Report message to a moderator

Re: Issue to connect to remote database [message #517207 is a reply to message #517205] Sun, 24 July 2011 10:45 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
There is a table in a remote database with synonyms/links defined.
Error is generated only if I try to use those tables in 'Formula'

In which case? When you use those tables through synonyms, or via database link? Or both?

Report message to a moderator

Re: Issue to connect to remote database [message #517210 is a reply to message #517207] Sun, 24 July 2011 11:21 Go to previous messageGo to next message
averion
Messages: 42
Registered: January 2009
Location: US
Member

The error occures on both cases (synonym/databasse link)

Here are some expalinations,

1. User1 in DB1 is trying to access tables in DB2 User2.
2. User1 can access that table in DB2.User2 with no error using SQLTool.
3. This indicates the link/synonym related to the tables are correct.
4. No error is produced if select statement is used only in Report 'SQL query'.
5. If that table is included in Formula/function in a report an error is generated.

"ORA-04052 Error occured when looking up remote object"
ORA-00604 Error occured at recursive SQL level1.

I hope this clears out everything.

Thanks for the reply.

Report message to a moderator

Re: Issue to connect to remote database [message #517211 is a reply to message #517210] Sun, 24 July 2011 12:01 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What are DB1 and DB2 versions?

Report message to a moderator

Re: Issue to connect to remote database [message #517315 is a reply to message #517211] Mon, 25 July 2011 06:36 Go to previous messageGo to next message
averion
Messages: 42
Registered: January 2009
Location: US
Member
Here is the version for db1,db2

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Report message to a moderator

Re: Issue to connect to remote database [message #517327 is a reply to message #517315] Mon, 25 July 2011 07:13 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So both databases are 11g ...

I just tried to create a report that uses a formula column whose SELECT contains a database link, something like
  retval varchar2(200);
begin
  select naziv into retval
    from tomm@dbl_bill
    where sifra = :sifra;
  return (retval);
end;

The same works OK in these cases:
- connected to 10.2.0.4, database link to 8.1.7
- connected to 10.2.0.1, database link to 10.2.0.4

I don't have any 11g available for testing, so there's nothing much I can say about the issue, sorry.

Though: is there a problem with that particular table only? Do other tables behave normally? If so, is there anything special with that table? Is it partitioned or something?

Report message to a moderator

Re: Issue to connect to remote database [message #517335 is a reply to message #517327] Mon, 25 July 2011 08:05 Go to previous messageGo to next message
averion
Messages: 42
Registered: January 2009
Location: US
Member
Thanks for your suggestions,

I have one question though, will that query work if you create a synonym for that link and try to run on a 'Report Builder'.
As mentioned before, there is a synonym for a link that points to a table in a remote DB.

So, a select statement would only be something like 'Select * from TABLE' (This table is acutlly a synonym of a link that points to a table in remote DB).

The query runs perfectly in PLSQL but the problem is only if tried on a report builder.

Thanks again,

Report message to a moderator

Re: Issue to connect to remote database [message #517337 is a reply to message #517335] Mon, 25 July 2011 08:10 Go to previous messageGo to next message
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
Senior Member
It ought to work. Report builder shouldn't care if the table name in the query is actually a table or a synonym.
I think you may need to take this up with oracle support.

Report message to a moderator

Re: Issue to connect to remote database [message #517339 is a reply to message #517337] Mon, 25 July 2011 08:13 Go to previous messageGo to next message
averion
Messages: 42
Registered: January 2009
Location: US
Member
Thanks a lot for your suggestions and have a great day.

Report message to a moderator

Re: Issue to connect to remote database [message #517341 is a reply to message #517339] Mon, 25 July 2011 08:19 Go to previous messageGo to next message
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
Senior Member
Actually, try running a select from the remote table in sqlplus after executing:
SET ROLE NONE;

Report message to a moderator

Re: Issue to connect to remote database [message #517413 is a reply to message #517341] Mon, 25 July 2011 21:33 Go to previous messageGo to next message
averion
Messages: 42
Registered: January 2009
Location: US
Member
Hello Experts,

I am still getting an error while accessing a table in a remote database through Report 10.1.2.0.2.

Here is what the situation is,

1. User1 in DB1 is trying to access a table in DB2 User2.
2. There is a synonym defined for a link to access a table in a remote DB2 User2.
3. User1 in DB1 is running a select statement like 'Select Column1,Column2,Column3,Column4 from TABLENAME.
(TABLENAME is actually a synonym for a link and points to a table in DB2 User2)
4. User1 can access that table in DB2.User2 through that synonym with no error using SQLTool.
5. This indicates the link/synonym related to the tables are correct.
6. No error is produced if select statement is used only in Report 'SQL query'.
7. If that table is included in Formula/function in a report an error is generated.

"ORA-04052 Error occured when looking up remote object"
ORA-00604 Error occured at recursive SQL level1.

I have been trying to get this done but still could not figure out a way.

Thanks again for your help.

Report message to a moderator

Re: Issue to connect to remote database [message #517430 is a reply to message #517413] Tue, 26 July 2011 01:23 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Just to verify:

3. What happens when (in a Formula column) instead of a synonym 
SELECT column1, column2 FROM tablename
you use 
SELECT column1, column2 FROM tablename@DB_LINK

There's the same error again, right?

I wouldn't know how to fix it. A workaround (if possible) might be to create a materialized view (when to refresh it? Depends on what is stored in a remote table and how often it is modified) and use the MV in the report.

Perhaps you should really contact Oracle Support, just like Cookiemonster has said.

Report message to a moderator

Re: Issue to connect to remote database [message #517466 is a reply to message #517341] Tue, 26 July 2011 03:53 Go to previous messageGo to next message
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
Senior Member
Did you try this?
cookiemonster wrote on Mon, 25 July 2011 14:19
Actually, try running a select from the remote table in sqlplus after executing:
SET ROLE NONE;

Report message to a moderator

Re: Issue to connect to remote database [message #517516 is a reply to message #517430] Tue, 26 July 2011 07:29 Go to previous messageGo to next message
averion
Messages: 42
Registered: January 2009
Location: US
Member
Thanks for your suggestions.

Select statement to the remote table 'SELECT column1, column2 FROM tablename' works perfectly.

It also works perfectly if I only include that table in Report 'SQL query'.

The error is generated only if that table is used in a function in a report to calculate something.

Thanks again, I will try out your suggestion cookiemonster and probably contact Oracle.

Have a good day.

Report message to a moderator

Re: Issue to connect to remote database [message #517517 is a reply to message #517516] Tue, 26 July 2011 07:34 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You still didn't say what happens when this
SELECT column1, column2 FROM tablename@DB_LINK
is used in a Formula column.

Report message to a moderator

Re: Issue to connect to remote database [message #517520 is a reply to message #517466] Tue, 26 July 2011 07:39 Go to previous messageGo to next message
averion
Messages: 42
Registered: January 2009
Location: US
Member

Actully, there is no Role set for users.

I tried 'SET ROLE NONE' as well but still get the remote connection error.

Report message to a moderator

Re: Issue to connect to remote database [message #517521 is a reply to message #517517] Tue, 26 July 2011 07:40 Go to previous messageGo to next message
averion
Messages: 42
Registered: January 2009
Location: US
Member

The statement
'SELECT column1, column2 FROM tablename@DB_LINK'
also doesn't work in a formula.

Report message to a moderator

Re: Issue to connect to remote database [message #517523 is a reply to message #517521] Tue, 26 July 2011 07:47 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Too bad. What about materialized view?

Report message to a moderator

Re: Issue to connect to remote database [message #517529 is a reply to message #517520] Tue, 26 July 2011 08:05 Go to previous messageGo to next message
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
Senior Member
averion wrote on Tue, 26 July 2011 13:39

Actully, there is no Role set for users.

I tried 'SET ROLE NONE' as well but still get the remote connection error.


What do mean you still get the error?
According to you it works in sqltools, and if it works in sqltools then it'll work in sqlplus.
I wanted to see if running that command in sqlplus caused the query to then give the error you see in oracle reports.

Report message to a moderator

Re: Issue to connect to remote database [message #517542 is a reply to message #517529] Tue, 26 July 2011 09:33 Go to previous messageGo to next message
averion
Messages: 42
Registered: January 2009
Location: US
Member
Hello again,

Sorry for the delay,

The query runs perfectly in SQLTools but if I use the same query in a Formula in a Report this error is generated.
The select statement aslo works perfectly if only used in Report 'SQL query'.

I hope its clear.

Thanks


Report message to a moderator

Re: Issue to connect to remote database [message #517543 is a reply to message #517542] Tue, 26 July 2011 09:35 Go to previous messageGo to next message
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
Senior Member
Completely clear. It just doesn't answer my question at all.

Report message to a moderator

Re: Issue to connect to remote database [message #517545 is a reply to message #517543] Tue, 26 July 2011 09:44 Go to previous messageGo to next message
averion
Messages: 42
Registered: January 2009
Location: US
Member
Could you please let me know your question again.

Report message to a moderator

Re: Issue to connect to remote database [message #517546 is a reply to message #517545] Tue, 26 July 2011 09:57 Go to previous messageGo to next message
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) Start up sqlplus and connect to the local DB
2) Run the query that accesses the remote DB that works in a report sql query and doesn't work in a formula column
3) Execute
SET ROLE NONE

4) Repeat step 2.

Report message to a moderator

Re: Issue to connect to remote database [message #517557 is a reply to message #517546] Tue, 26 July 2011 10:40 Go to previous messageGo to next message
averion
Messages: 42
Registered: January 2009
Location: US
Member
I just did all the steps. The query works perfectly in SqlPlus.

Report message to a moderator

Re: Issue to connect to remote database [message #517560 is a reply to message #517557] Tue, 26 July 2011 10:55 Go to previous messageGo to next message
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
Senior Member
Both times?

If yes then you're going to have to take this up with oracle support.

Report message to a moderator

Re: Issue to connect to remote database [message #517570 is a reply to message #517560] Tue, 26 July 2011 11:48 Go to previous messageGo to next message
averion
Messages: 42
Registered: January 2009
Location: US
Member
Yes both times. I am going to do that now. thanks a lot for suggestions.

Report message to a moderator

Re: Issue to connect to remote database [message #517622 is a reply to message #517570] Tue, 26 July 2011 16:13 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yet another idea, if I may /forum/fa/5851/0/

As the function code works fine in SQL*Plus (by the way, what is "SQLTools" you mention all the time?), perhaps you could create a stored function (stored in the database). You'd then simply call it from the report, passing parameters you meant to use in a Formula column (these would be all ":item" items or whatever you used).


Furthermore: you never told us what that function does. If we saw the function and report query, maybe someone of the smart guys here could figure out how to incorporate function code within the original query (so that you wouldn't have to use a formula column at all).


Your turn.

Report message to a moderator

Re: Issue to connect to remote database [message #517624 is a reply to message #517622] Tue, 26 July 2011 18:23 Go to previous messageGo to next message
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
Senior Member
SqlTools - kinda like toad.

Report message to a moderator

Re: Issue to connect to remote database [message #517711 is a reply to message #517624] Wed, 27 July 2011 08:10 Go to previous message
averion
Messages: 42
Registered: January 2009
Location: US
Member
Hello 'Littlefoot' and 'Cookiemonster',

That is what I am doing right now. I have created a function that returns a value I was getting from a formula and it works.
SQLtool as 'cookiemonster' mentioned is a free tool and is very flexible for PlSql. I am a big fan of it. I think you should try.

Thanks a lot once again to both of you and have a great day.

Report message to a moderator

Previous Topic: Need help with averages across the columns
Next Topic: please guide me
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Jan 21 13:13:53 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.