Home » Developer & Programmer » Reports & Discoverer » Reports are not connecting to the database (forms and reports developer 10G)
Reports are not connecting to the database [message #563484] Tue, 14 August 2012 05:39 Go to next message
daisy78
Messages: 9
Registered: August 2012
Junior Member
I have an oracle related question for you.

I am running a OC4J instance on my laptop, so that I can test out oracle forms

I can login to the applications and forms runs ok.

On one of the forms there is a button to run an oracle report from that form.

When I run the report is fails. I have put tracing on the report engine and it is failing as it cannot connect to the database.

I have also put tracing in sqlnet.ora

When I originally login to the application via oracle forms it puts data in the trace file, but when I try and run the report no data goes to the sqlnet.ora trace file.

Do you know why the oracle report does not connect to the database and / or why there is nothing in the sqlnet trace file for the report.
Re: Reports are not connecting to the database [message #563494 is a reply to message #563484] Tue, 14 August 2012 07:40 Go to previous messageGo to next message
Littlefoot
Messages: 19536
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What does the button do? How do you call the report?
Re: Reports are not connecting to the database [message #563567 is a reply to message #563494] Wed, 15 August 2012 01:38 Go to previous messageGo to next message
daisy78
Messages: 9
Registered: August 2012
Junior Member
we use rp2rro.rp2rro_run_product to call the report, from within a form

this works on two other laptops / pcs, but doesn't work on mine.
Re: Reports are not connecting to the database [message #563573 is a reply to message #563567] Wed, 15 August 2012 04:18 Go to previous messageGo to next message
Littlefoot
Messages: 19536
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm unfamiliar with "rp2rro.rp2rro_run_product", sorry.

However, if it works fine elsewhere, are your pop-ups blocked? Do you use firewall (if so, maybe you could check its settings)?
Re: Reports are not connecting to the database [message #563582 is a reply to message #563573] Wed, 15 August 2012 07:19 Go to previous messageGo to next message
daisy78
Messages: 9
Registered: August 2012
Junior Member
Hi, Thanks for your reply, I have disabled the firewall and the pop-up blocker and the report still fails to connect to the database
Re: Reports are not connecting to the database [message #563599 is a reply to message #563582] Wed, 15 August 2012 15:04 Go to previous messageGo to next message
Littlefoot
Messages: 19536
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Compare TNSNAMES.ORA & SQLNET.ORA files on your computer with the ones that are located on a computer that works OK; maybe you'll notice a difference.
Re: Reports are not connecting to the database [message #563647 is a reply to message #563599] Thu, 16 August 2012 05:24 Go to previous messageGo to next message
daisy78
Messages: 9
Registered: August 2012
Junior Member
HI, I have already compared most of the configuration files that I can think of and they are the same as the computers that work
Re: Reports are not connecting to the database [message #563679 is a reply to message #563647] Thu, 16 August 2012 08:23 Go to previous messageGo to next message
Littlefoot
Messages: 19536
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What happens when you start Reports Builder, connect to the database, open that report and run it? Do you get the result? If YES, then there *might* be issues with "rp2rro.rp2rro_run_product" (whatever it is and whatever it does) but, as I said, I know nothing about it.
Re: Reports are not connecting to the database [message #563690 is a reply to message #563679] Thu, 16 August 2012 08:59 Go to previous messageGo to next message
daisy78
Messages: 9
Registered: August 2012
Junior Member
HI When I run the URL
http ://localhost:8889/reports/rwservlet/showjobs?server=repsrv

it shows the report running
and then shows it as failed 'could not connect to the database'

in the rwservlet.trc file we have the following line:

[2012/8/16 2:48:31:38] Debug 50103 (ConnectionImpl:runJob): jobid = 111 Failed with exceptionoracle.reports.RWException: IDL:oracle/reports/RWException:1.0
[2012/8/16 2:48:31:38] Exception 501 (): Unable to connect to the specified database.

in the rwEng-0.trc

we have
[2012/8/16 2:48:10:585] Info 50132 (EngineImpl:setCommandLine): Get command line: expiredays=0 report="testreport" jobname="testreport" userid=testuser@testdb desformat=html authid=ANONYMOUS p_status="2" destype=cache
[2012/8/16 2:48:10:585] Debug 50103 (EngineImpl:setCommandLine): Quit
[2012/8/16 2:48:10:585] Debug 50103 (EngineImpl:run): Running Jobid=111
[2012/8/16 2:48:31:22] Error 50103 (C Engine): 14:48:31 ERR REP-0501: Unable to connect to the specified database.
ORA-12154: TNS:could not resolve the connect identifier specified


I also have SQLNET tracing on

and there is no entry in the sqlnet.trc file for when the report tries to connect in


Re: Reports are not connecting to the database [message #563696 is a reply to message #563690] Thu, 16 August 2012 09:12 Go to previous messageGo to next message
Littlefoot
Messages: 19536
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
See ORA-12154 interpreted by BlackSwan.
Re: Reports are not connecting to the database [message #563703 is a reply to message #563696] Thu, 16 August 2012 09:44 Go to previous messageGo to next message
daisy78
Messages: 9
Registered: August 2012
Junior Member
HI,
From SQLplus and toad I can login with user testuser to testdb from my laptop

When I run the form I use the same connect string from my laptop

when the form calls the report to run, it uses the same connect string that has been passed internally by oracle from the form to the report and it shows this in the trace file so I don't know why the report cannot resovle the name.

Do reports use a different tnsnames file to forms and sqlplus ? or is there a setting somewhere that oracle reports reads to enable it to find where the tnsnames directory is ?

Thanks
Re: Reports are not connecting to the database [message #563752 is a reply to message #563703] Thu, 16 August 2012 14:25 Go to previous messageGo to next message
Littlefoot
Messages: 19536
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you installed several Oracle software products (for example, Oracle 10g database, Oracle 11g XE database, Oracle Developer Suite 10g), you'd find 3 TNSNAMES.ORA files on your disk - one for every product you installed. It caused headache for many people (possibly, you are among them). In order to fix that situation, I'd suggest you to use (and maintain) a single copy of the TNSNAMES.ORA file. In order to do that, you need to set the TNS_ADMIN environment variable. Have a look here for some more info. Try to follow that example and see what happens.
Re: Reports are not connecting to the database [message #563794 is a reply to message #563752] Fri, 17 August 2012 02:58 Go to previous messageGo to next message
daisy78
Messages: 9
Registered: August 2012
Junior Member
HI, I have set the TNS_ADMIN variable to C:\DevSuiteHome_1\NETWORK\ADMIN

both on a user level and the system level, and re-booted my laptop

I have retried to run the report and I still get

[2012/8/17 8:46:20:296] Error 50103 (C Engine): 08:46:20 ERR REP-0501: Unable to connect to the specified database.
ORA-12154: TNS:could not resolve the connect identifier specified
Re: Reports are not connecting to the database [message #563804 is a reply to message #563794] Fri, 17 August 2012 04:18 Go to previous messageGo to next message
Littlefoot
Messages: 19536
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I suppose you verified that database alias (report is connecting to) exists in TNSNAMES.ORA located in "C:\DevSuiteHome_1\NETWORK\ADMIN" directory, didn't you?
Re: Reports are not connecting to the database [message #563807 is a reply to message #563804] Fri, 17 August 2012 04:37 Go to previous messageGo to next message
daisy78
Messages: 9
Registered: August 2012
Junior Member
Hi Yes, I can connect to the data base using oracle forms with that connect string and it is in the tnsnames.ora file, When you run a report from a form oracle internally should use the same connect string, which from the debug output it appears to do, however for some reason the report is not able to connect to the database. Also I have put tracing in the sqlnet.ora file, but when the report tries to connect no trace output is being produced.
Re: Reports are not connecting to the database [message #563831 is a reply to message #563807] Fri, 17 August 2012 06:37 Go to previous messageGo to next message
Littlefoot
Messages: 19536
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How exactly do you call that report from a form? I know - you use "rp2rro.rp2rro_run_product" but ... how does your code look like? Does it (rp2rro...) accept any parameters? If so, which ones? What is written in that procedure? I suppose it contains "username/password@database"; does it? In which form (i.e. how does it look like)?
Re: Reports are not connecting to the database [message #563837 is a reply to message #563831] Fri, 17 August 2012 07:20 Go to previous messageGo to next message
daisy78
Messages: 9
Registered: August 2012
Junior Member
Hi, Basically below is how we run the report, the internal oracle code should be able to work out the connection string as the report is already connected and in the trace output of the report engine it has the correct username and database name (but no password - I assume for security purposes)

PROCEDURE RunReport IS
rep_name varchar2(9) := 'TESTRPT';
repid REPORT_OBJECT;
begin
repid := find_report_object('TESTRPT');
SET_REPORT_OBJECT_PROPERTY(repid, REPORT_DESTYPE, CACHE);
/* This should let you preview the report on your screen */
rp2rro.rp2rro_run_product(REPORTS,REP_NAME,SYNCHRONOUS,RUNTIME,FILESYSTEM,NULL,NULL);
destroy_parameter_list( param_id ) ;
end ;
Re: Reports are not connecting to the database [message #563840 is a reply to message #563837] Fri, 17 August 2012 07:27 Go to previous message
Littlefoot
Messages: 19536
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm out of ideas, sorry. It still *smells* like "rp2rro.rp2rro_run_product", but I can't tell for sure.

Hopefully, someone else will be able to assist.
Previous Topic: How to handle no data found in rdf query- slightly complex requirement
Next Topic: frames/borders do not print when empty
Goto Forum:
  


Current Time: Mon Sep 01 15:00:25 CDT 2014

Total time taken to generate the page: 0.11801 seconds