Connect to a different schema from SP

[kiransalu]

I have two schema in the same database.
Map and Vendor

The tables in the Map schema gets loaded through some external application. Now i want to write a SP in Map schema to push the data from MAP to Vendor schema. I have to apply various business validations before inserting data to Vendor schema.

I am worried, how do i connect to Vendor schema through my SP in MAP Schema?

 

[SantaMufasa]

Kiran,

First, what do you mean by "push" the data from MAP to Vendor schema?

Second, in the MAP stored procedure, you can reference objects in the Vendor schema without "connecting" to the Vendor schema...you just preface any reference to Vendor's objects with the name "Vendor":

SELECT * into <some target> FROM Vendor.<table_name>...
INSERT into Vendor.<table_name> values...
UPDATE Vendor.<table_name> SET...
DELETE Vendor.<table_name> WHERE...

In addition to the "Vendor.<table_name>" reference, Vendor must:

GRANT INSERT, UPDATE, DELETE, SELECT on <table_name> to MAP;

...prior to MAP's first reference to Vendor's object(s).

Does this answer your question?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 20:24 (15Feb05) UTC (aka "GMT" and "Zulu"),
@ 13:24 (15Feb05) Mountain Time

Do you use Oracle and live or work in Utah, USA? Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[kiransalu]

Wow Excellet Mufasa.

Keep it up.