We have a reporting instance of our production data that gets created every night. This means that our data is always a day behind, but for 99% of our reports that is fine. There are several reasons we do not hit the production system, but last week we received a request for real-time data in one of our reports.

I created a database link in oracle that hits the correct data and in my normal SQL tools, it works great. However, when I try to use the link in WebFOCUS in a SQL Report, it get the following error:

(FOC1400) SQLCODE IS 2019 (HEX: 000007E3)
: ORA-02019: connection description for remote database not found
L (FOC1405) SQL PREPARE ERROR.
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT
BYPASSING TO END OF COMMAND

My SQL statement is very basic, and works on the report table, but not with the link.
Here is the SQL that does NOT work:

ENGINE SQLORA SET DEFAULT_CONNECTION BANCLONE
SQL SQLORA PREPARE SQLOUT FOR
select *
from course_term_schedule@prod_db t
where t.term_code = '&TERM_CODE'
END

Here is the SQL that DOES work:

ENGINE SQLORA SET DEFAULT_CONNECTION BANCLONE
SQL SQLORA PREPARE SQLOUT FOR
select *
from course_term_schedule t
where t.term_code = '&TERM_CODE'
END

I guess the real question is how can I use a database link with a SQL report in WebFocus?

THanks

WA

This message has been edited. Last edited by: Kerry,

Have you tried a Cross Server Query?

ENGINE SQLORA SET DEFAULT_CONNECTION BANCLONE
SQL SQLORA
select *
from OpenQuery("255.255.255.0"   <--- replace this with your server's IP address
             , 'select *
                  from course_term_schedule t
                 where t.term_code = '&TERM_CODE'
;
TABLE FILE SQLOUT
PRINT
END

I use this syntax in an Oracle table insert from an MS SQL Server. CHeck out OpenQuery via your favorite search engine.

T

quote:

ORA-02019: connection description for remote database not found

I think the error is clear enough and I think this may be related to underlying privileges on the Oracle user id.

Have you checked that the Oracle user that makes the connection from WebFOCUS to your Oracle instance can "see" and "use" your PROD_DB database link in Oracle?

quote:

I created a database link in oracle that hits the correct data and in my normal SQL tools

Which Oracle user did you connect as when testing the DB links through the SQL tools? Make sure that user is the same one defined in your WebFOCUS connection.

The Reporting user on Oracle did not have access to hit the production system, so I created a view on my reporting database that just pulled the data using the database link from the production database. Then created a synonym of the view and it is working.

Thank you to both of you for your response...

You're welcome, TSU. I'm glad the suggestions worked.