Thanks for the question, vijay.
Asked: April 12, 2004 - 8:35 am UTC
Last updated: April 12, 2004 - 8:35 am UTC
Version: 9.0.1
Viewed 1000+ times
You Asked
Hi Tom,
I want to pass database link as a parameter in my pl/sql. her is my code
v_db_link, i given as input variable. v_db_link is a database link
PROCEDURE entitylibrary_populate_single
(v_db_link VARCHAR2) AS
--------
FOR entitytype_tag IN (SELECT profiletypeid FROM profiletypes@v_db_link )
LOOP
FOR brand_tag IN (SELECT DISTINCT(brandid) FROM brands@v_db_link )
LOOP
----- here i'm doing some actions-----------------
COMMIT;
END LOOP;
END LOOP;
i'm getting -PL/SQL: ORA-00942: table or view does not exist
error.
if i give database link as statically , it's working fine. but passing through the variable its not working.....
can u help me to solve it
Thanks
vijay
and Tom said...
oh yuck. why would you have a "do it yourself" nested loop like that? please let the database do what the database was born to do -- JOIN!!!!!!!
You would have to use dynamic sql in order to accomplish this.
procedure p( p_db_link in varchar2 )
is
l_cursor sys_refcursor;
l_profiletype <datatype>;
l_brandid <datatype>;
begin
open l_cursor for 'select profiletype, brandid
from profiletypes@' || l_db_link || '
(select distinct(brandid) brandid
from brands@' || l_db_line || ' )';
loop
fetch l_cursor into l_profiletype, l_brandid;
exit when l_cursor%notfound;
process data
end loop;
close l_cursor;
commit; --- <<--- commit here IF at all (i wouldn't commit here at all)
end;
/
You might consider a driving site hint if all objects are at the remote site as well.
Is this answer out of date? If it is, please let us know via a Comment