Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Connecting oracle server to SQL server

M.broMay 17 2022 — edited May 18 2022

Dear Team,
Oracle Database Version : 12.0.1
Sql Server Connectivity using ODBC
I am using oracle Database and trying to connect SQL Server using ODBC for the below steps but I am facing a different issues
ORA - 12638:Credential Retrieval failed.
ORA-28545 ERROR DIAGNOSED BY NET8 WHEN CONNECTING TO AN AGENT UNABLE TO RETRIEVE TEXT OF NETWORK/NCR MESSAGE 65553 ORA-02063 preceding 2lines from sqldb ORA-28545 ERROR DIAGNOSED BY NET8 WHEN CONNECTING

Below are the steps I followed to connect.
SEARCH ODBC Datasource 64 bit
image.png
FOR SERVER IP ADD IS (LOCAL MACHINE IP ADDRESS

S)
ask the user name password for the client
image.png
CLICK NEXT
image.png
CLICK NEXT
CLICK FINISH
image.png
CLICK --- CHECK DATA SOURCE
-------------------------------------------ODBC CONNECTION COMPLETED----------------------------------------------------
GO TO LOCATION ---> E:\app\rndbi\product\12.1.0\dbhome_1\hs\admin (IN SERVER)
TAKE BACK THE FILE(copy a file initdg4odbc.ora)
and edit the copy file and rename (add sql) like this
image.png
and right click and go to text pad in that copied file and add(#) in that line and change hs_fds_connetc_info name as (odbc database name)
image.png
image.png
and go to the loaction----> Go: E:\app\rndbi\product\12.1.0\dbhome_1\NETWORK\ADMIN
copy and paste the 2 file(ie is backup) like this
image.png
and edit listner.ora file
edit like this (add the code in that file (use the document for that particular code))
and check the location name and sid_name =sql
image.png
and edit tnsnames.ora file
edit like this (add the code in that file (use the document for that particular code))
and check the location name and sid=sql and check the db name (HOST=DB NAME)
image.png
GO TO CMD PROMPT
TYPE : LSNRCTL
START AND STOP THE LISTNER
GO TO CMD PROMPT
tnsping sqlconnection
tnsping sql_connection

image.png
check the marked name as tnsping as (tnsnames.ora)
and create a dblink in (toad)
user name as (sql server db name and paswword ask client) ('sql_connection' is in tnsnames.ora name file) create in live
CREATE PUBLIC DATABASE LINK sqldb CONNECT TO
"username" IDENTIFIED BY "password" using 'sql_connection'

-after that restart db service and listerner
get the table name in sql servers...without (dbo) use select

image.png
after creation check in select statement
Select *from table_name@sqldb

How to use where condition in db link:

select *from table_name@sqldb where "column_name"=' ';

Thanks & regards,
M.bro

Comments

Steve Yeung

Excellent article!!!! Disk IO is always very hard to tackle and explain even to IT.

1 person found this helpful
Carl wan

Hi Everyone,I meet the performance problem from obiee 11g.
I was using a complex view in RPD from EBS, this is a gl account balance,about 40 thousands rows.
I seperately query the view in client tool is very fast(1s),but run the report in web cost almost (47s) and I using the sql that biee generated in client is also fast.this report pass through three layer from rpd to request db.I used to try using hints to change the query optimizer execution plan but didn't work.

please provide me your advise. thanks so much.I'm looking forward to your reply!

is there any a good idea to realize a realtime report using biee rpd.

rmoff

Hi Everyone,I meet the performance problem from obiee 11g.
I was using a complex view in RPD from EBS, this is a gl account balance,about 40 thousands rows.
I seperately query the view in client tool is very fast(1s),but run the report in web cost almost (47s) and I using the sql that biee generated in client is also fast.this report pass through three layer from rpd to request db.I used to try using hints to change the query optimizer execution plan but didn't work.

please provide me your advise. thanks so much.I'm looking forward to your reply!

is there any a good idea to realize a realtime report using biee rpd.

Hi Carl,

This kind of question is best posted to the forum.

thanks, Robin.

1 person found this helpful
user11440683

Very informant and detailed, many thanks for this.

One question, and an issue which every OBIEE developer runs into all too often - you allude to 'running very large volumes of data only to export to excel' - what would be your best practise recommendation on this, given that 'Do not do it' is rarely listened to by the client?

Many thanks,

Robert.

Christian Berg-0racle

Very informant and detailed, many thanks for this.

One question, and an issue which every OBIEE developer runs into all too often - you allude to 'running very large volumes of data only to export to excel' - what would be your best practise recommendation on this, given that 'Do not do it' is rarely listened to by the client?

Many thanks,

Robert.

"Best Practice" is "do it with the right tool for the job and don't drag things DB -> network -> BI Server -> BI Presentation Server -> network -> browser -> desktop -> Excel".

rmoff

Very informant and detailed, many thanks for this.

One question, and an issue which every OBIEE developer runs into all too often - you allude to 'running very large volumes of data only to export to excel' - what would be your best practise recommendation on this, given that 'Do not do it' is rarely listened to by the client?

Many thanks,

Robert.

Hi @"Robert Angel",

I cover some of this in my No Silver Bullets talk. Generally my advice is:

  1. Don't do it - but rather than just prohibiting users, understanding what it is they're doing with the data once it's in Excel and helping them maybe do that in OBIEE instead
  2. Do it smarter. The very worst thing for performance is to as the Presentation Services to generate the XLSX, because of the overhead. Use CSV instead, or use BI Publisher for larger volumes. See 1558070.1 p.13 for more details
  3. You can pull data directly from the BI Server into Excel using the ODBC interface (see this blog by @"Andrew Fomin." for more info).
  4. If you don't mind bypassing OBIEE entirely (and losing the benefit of your common enterprise information model, the RPD) then just hit the DB directly for the data.

It comes down to what the data is, the size of it, how frequently you need the extract, and so on.

1 person found this helpful
user11440683

On 2 I have advised clients to do this very thing myself, but curiously on one site ran into a problem where excel exports could handle more data than csv, which given the overhead you mention I did find very odd.

Thanks for 3 - I had not heard of this previously!

What would your comment be on 5 -  get them to use Smart View?

rmoff

On 2 I have advised clients to do this very thing myself, but curiously on one site ran into a problem where excel exports could handle more data than csv, which given the overhead you mention I did find very odd.

Thanks for 3 - I had not heard of this previously!

What would your comment be on 5 -  get them to use Smart View?

Good point - SmartView can be useful, if they've already got it installed and are using it. It does have it's own limitations though, that can sometimes defeat the point of using it. But - certainly worth a try.

1 - 8

Post Details

Added on May 17 2022
#connection, #dba, #general-database-discussions, #odbc, #server
0 comments
293 views