create table test_table1 (a number primary key, b number, c number);
I have the below function in a remote DB. This function is fetching a record from test_table and returning this to calling program.
create or replace function gettesttablerec(p_a number)
return test_table%rowtype is
v_testtablerec test_table%rowtype;
begin
begin
select *
into v_testtablerec
from test_table
where a = p_a;
exception
when others then
null;
end;
return v_testtablerec;
end;
I have below procedure here in my DB. Where I am trying to call above function from remote DB (through DB link) and insert the same record in another table with same structure. I have written below procedure but giving me some compilation issue. Probably I have never tried such things before so dont have idea about the proper syntax of that.
create or replace procedure insertintonewtable(p_a number) is
v_testtablerec test_table%rowtype;
begin
execute immediate ‘begin :RETVAL := gettesttablerec(:1)@; end;’
using IN p_a, OUT v_testtablerec;
insert into test_table1
values v_testtablerec;
end;
I hope above info would be sufficient to answer my question, if you you still feel that this not enough please do let me know.
Oracle Advanced PL/SQL Developer Professional Guide
Disclaimer
SBHOracle is an independent blog and all the posts are based on my self experience and hands on with the technologies. It shares no relations with any of my current projects or from those in the past.
here is my problem in detail:
I have table test_table.
create table test_table1 (a number primary key, b number, c number);
I have the below function in a remote DB. This function is fetching a record from test_table and returning this to calling program.
create or replace function gettesttablerec(p_a number)
return test_table%rowtype is
v_testtablerec test_table%rowtype;
begin
begin
select *
into v_testtablerec
from test_table
where a = p_a;
exception
when others then
null;
end;
return v_testtablerec;
end;
I have below procedure here in my DB. Where I am trying to call above function from remote DB (through DB link) and insert the same record in another table with same structure. I have written below procedure but giving me some compilation issue. Probably I have never tried such things before so dont have idea about the proper syntax of that.
create or replace procedure insertintonewtable(p_a number) is
v_testtablerec test_table%rowtype;
begin
execute immediate ‘begin :RETVAL := gettesttablerec(:1)@; end;’
using IN p_a, OUT v_testtablerec;
insert into test_table1
values v_testtablerec;
end;
I hope above info would be sufficient to answer my question, if you you still feel that this not enough please do let me know.
Bold part is the problematic area.
Posted by Peeush | April 2, 2012, 7:40 pm