select and insert in store proc

royjayd
royjayd used Ask the Experts™
on
higuys

I have this following requirment.I am using Oracle 9i and these are the tables i  am using

Database1
Table  Customer_Profile
Columns
Name
Id
Stock_Quote#

Database2
Table  Cus_Profile
Columns
Cus_Name
Cus_Id
Stock_Quote#

Data from table Customer_Profile (of Database1) needs to be fetched and inserted into Cus_Profile(of Database2)
We want to use Oracle stored procedure to do this using a oracle refcursor.

create or replace package customertrading.Customer_profile_storeProc
...
define ref cursor here ?
...
SELECT * from Customer_Profile

...

INSERT into Cus_Profile (..)

...


I dont have much experience with oracle stored procs so was wondering how i should proceed.

Any help will be appreciated.

thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
IT-specialist
Commented:
In database 2
create (public) database link dblink_database1  (.connect)   using 'DATABASE1') --
-- determine if you want a public database link and the way to connect  to it  


create or replace package customertrading
is
prodecure Customer_profile_storeProc(p_cus_id Cus_Profile.CUS_ID%type);  -- procedure to insert 1 customer by id
end;
/

create or replace package body customertrading
is

prodecure Customer_profile_storeProc(p_cus_id Cus_Profile.CUS_ID%type)
is
begin
   insert into cus_profile
   select Name,    Id , Stock_Quote#
   from  Customer_Profile@dblink_database1 where id = p_cus_id;    -- you don't need a ref_cursor or you didn't tell all you want
end;

end;
/


you can execute the procedure
for example in sqlpuls

begin
   customertrading.Customer_profile_storeProc(3);  -- to insert customer with id 3
end;
/

Commented:
Why do you want to make use of REF_CURSOR?

Is this part of routine job? if its one time process, you can make use of INSERT INTO...SELECT * FROM using DBLINK.


Commented:
You can do  the following:

1. if your both the users (database1, database2) created in the different DB server.
    create a DBLink as suggested by flow01 and use the procedure provided by him.

2. if your both the users (database1, database2) created in the same DB server.
    create a Synonym for Customer_Profile of database1, use that synonym in the procedure.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial