Auditing Oracle Database from my application

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Auditing Oracle Database from my application

Author

Topic

a_r_satish
Yak Posting Veteran

84 Posts

Posted - 2006-02-07 : 07:08:20

Hi,

In my application, i am using Ms-SQL.
I need to audit check the Oracle database from my application and save the changes in my tables and maintain the backups for changed data.

Can any one help me with the Queries for the above.Or any hint

Thanks in advance.
Regards,
satish.r

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-07 : 07:18:36

create a linked server to the Oracle DB from MS SQL Server. Refer to Books Online on sp_addlinkedserver

----------------------------------
'KH'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-07 : 08:39:11

or In your Application, have two connections one for Oracle and other for SQL Server and do the task you want

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-02-07 : 08:41:04

Sounds like it would help if the Oracle table has an UpdateDate column, or some such, so that you could restrict you checks to rows that have "changed" since last time you checked.

Kristen

a_r_satish
Yak Posting Veteran

84 Posts

Posted - 2006-02-07 : 08:44:59

Hello Sir,
Thanks for ur help.

Regards,
satish.r

a_r_satish
Yak Posting Veteran

84 Posts

Posted - 2006-02-07 : 08:49:45

Ya i have connections to multiple databases in my application.
But my database in MS-SQL.
So i wanted to include everything in MS-SQL.
So i was confused.

Now i have a doubt

After using the stored procedure to connect to oracle, ll any of the
oracle sys tables details be fetched and stored in a temp table in
MS-SQL??
Can u help with some queries???

Thanks in advance.

Regards,
satish.r

a_r_satish
Yak Posting Veteran

84 Posts

Posted - 2006-02-07 : 08:51:48

But i dont need to know the database table structure nor i should change, as i am just maintaining a db dictionary in my database.

quote:
Originally posted by Kristen

Sounds like it would help if the Oracle table has an UpdateDate column, or some such, so that you could restrict you checks to rows that have "changed" since last time you checked.

Kristen

Regards,
satish.r

Kristen
Test

22859 Posts

Posted - 2006-02-07 : 10:08:59

Well, my question would be "How are you going to detect that something has changed on Oracle?"

If you SELECT all rows, from all tables, and then compare them against your copy in MS-SQL [to see what has changed] it will be very slow!

But if the Oracle database already has UpdateDate/Time columns then you can use those to examine only rows known to have changed recently, and that will be faster.

But maybe I have misunderstood what you are wanting to achieve?

"Can u help with some queries"

When I pull data from Oracle [into MS-SQL] I usually do something like:


SELECT *
FROM	OPENQUERY(MyLinkedOracleServer, 
		'SELECT Col1, Col2, ... 
		FROM OracleInstance.OracleTable
		WHERE ColX = ''FOO'''
	)

Kristen

a_r_satish
Yak Posting Veteran

84 Posts

Posted - 2006-02-07 : 23:14:28

Hello Sir,
My purpose is to connect to any oracle db. It depends on the application to which i am going to connect. So i don think it is feasible for me to create a field in every database which i am connecting.

Moreover when i execute the query, it says you dont have permission to perform this action(sp_AddlinkedServer).
Is there any other alternative??/

Thanks in advance.

quote:
Originally posted by Kristen

Well, my question would be "How are you going to detect that something has changed on Oracle?"

If you SELECT all rows, from all tables, and then compare them against your copy in MS-SQL [to see what has changed] it will be very slow!

But if the Oracle database already has UpdateDate/Time columns then you can use those to examine only rows known to have changed recently, and that will be faster.

But maybe I have misunderstood what you are wanting to achieve?

"Can u help with some queries"

When I pull data from Oracle [into MS-SQL] I usually do something like:

SELECT *
FROM OPENQUERY(MyLinkedOracleServer,
'SELECT Col1, Col2, ...
FROM OracleInstance.OracleTable
WHERE ColX = ''FOO'''
)

Kristen

Regards,
satish.r

Kristen
Test

22859 Posts

Posted - 2006-02-08 : 03:11:16

"So i don think it is feasible for me to create a field in every database which i am connecting"

Indeed, but my point is that maybe such a field has already been created

Kristen

a_r_satish
Yak Posting Veteran

84 Posts

Posted - 2006-02-08 : 06:31:42

Sir,
I don have to bother about those fields in the application. That's ok i ll manage.
Now i have a problem. I don have the rights to addlinkedserver.

Is there any other way to handle this situation(That's to connect to other database)??

Kindly Help.

quote:
Originally posted by Kristen

"So i don think it is feasible for me to create a field in every database which i am connecting"

Indeed, but my point is that maybe such a field has already been created

Kristen

Regards,
satish.r

a_r_satish
Yak Posting Veteran

84 Posts

Posted - 2006-02-08 : 23:28:23

Hi,

Is there any other way to access to other database tables rather than using the Linked Server.
( I don have the permission to create the linked server)

Regards,
satish.r

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-02-08 : 23:40:04

quote:
Originally posted by a_r_satish

Hi,

Is there any other way to access to other database tables rather than using the Linked Server.
( I don have the permission to create the linked server)

Regards,
satish.r

just have your application talk to the Oracle databases directly. It sounds as if you just want to use SQL Server as the repository for the data that you pull out of various sources. You don't need linked servers to do that.

When you say "Audit Check" what exactly do you mean? What specifically do you want to audit?

-ec

a_r_satish
Yak Posting Veteran

84 Posts

Posted - 2006-02-09 : 00:07:39

Sir,

By Audit check i mean to sync my db with the other applications db.

That is after i import the tables from other db, there may be changes in the table structure(Quite Obvious) in terms of fields added, constraints, etc., So i want to update my db with the current live DB and maintain the history so that the table changes may be viewed for a selected range of time.

I hope i am clear

quote:
Originally posted by eyechart

"When you say "Audit Check" what exactly do you mean? What specifically do you want to audit?"

-ec

Regards,
satish.r

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-02-09 : 00:43:01

as maddy suggested,

use two connections in your apps: one for oracle, one for mssql

or you can ask your DBA to do the sync for you

HTH

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2006-02-09 : 02:21:18

Ah, the penny drops!

Synchronise the table structure between the two systems, not changes to the data.

Kristen

a_r_satish
Yak Posting Veteran

84 Posts

Posted - 2006-02-09 : 23:05:41

Thanks for all ur Suggestions.

Regards,
satish.r

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources