Calling Oracle Procedure from CLR SQL 2005

Question

Post reply

Calling Oracle Procedure from CLR SQL 2005

camilo.villa

SSC Veteran

Points: 262
More actions
August 25, 2009 at 9:23 pm

#209032

Hi.
I need to call a variety of procedures in ORACLE Database, from a SQL CLR .NET Application... I try to use linked server calling, .... but don't do what i need... cause this procedures delete records on Oracle DataBase... i see all security permissions on user that is used to connect to Oracle from LinkedServer and has all rights.
I made a clr procedure on SQL, that call the procedure made it on ORACLE, but this ask me to be UNSAFE or EXTERNAL Assembly, I change my database in SQL to be when i try to deploy as an EXTERNAL or UNSAFE, blow an Error telling me, that cannot be made because the owner SID is different in DataBase Master, From the current database, use ALTER AUTHORIZATION... but i don't understand this error.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

I hope you are also asking this question in an ORACLE forum.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 2

Hi

If you just need to call the procedure in Oracle without moving any data to SQL Server I wouldn't use a linked server to handle this. I wouldn't even use a SQL CLR procedure ;-). In this case use SSIS or a .NET process which works with ADO OracleDb client.

If you need to receive data from your Oracle procedure into SQL Server I wouldn't use .NET. In this case use a usual T-SQL procedure which calls the remote procedure on your linked server.

The message to mark your assembly as EXTERNAL or UNSAFE while distribution indicates you write any file or work with other unsafe things (like threading, COM, API) within your CLR procedure. Usual connections to a database server should not require this mode.

Greets

Flo

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

Hi, Flo. Nice to "see" you again. Hope all is well with you.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 4

Bob Hovious (8/26/2009)
Hi, Flo. Nice to "see" you again. Hope all is well with you.

Hi Bob! Nice to see you too! I'm quiet busy those days... New project with about 300 days effort starts and I have to do many planning tasks.

camilo.villa SSC Veteran Points: 262 More actions · Answer 5

Hi,

Thanks for your help!!!

My intention is delete data in Oracle, and someone else create a Package with this logic, i need to call them form SQL Server, then if really erase data in the other side of the dark, proceed with other operations that i have to do...

Thats what i need.

Thanks again!!!