I have a PL/SQL package that copies data from a remote DB2 database using a database link. To keep the package configuration-independent I wrapped the remote table in a view. But a single field in a single table causes me lots of headache. It is a char(1) field that is used as a bit vector. Because the encodings are different, it gets reencoded and scrambled, so I have to update it using DBMS_HS_PASSTHROUGH:
C := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@remotedb1; DBMS_HS_PASSTHROUGH.PARSE@remotedb1(C, 'SELECT PKEY, HEX(FLAGS) FROM TABLE1' ) ; LOOP NR := DBMS_HS_PASSTHROUGH.FETCH_ROW@remotedb1(C); EXIT WHEN NR = 0; DBMS_HS_PASSTHROUGH.GET_VALUE@remotedb1(C, 1, M_PKEY); DBMS_HS_PASSTHROUGH.GET_VALUE@remotedb1(C, 2, M_FLAGS); UPDATE TABLE1 SET FLAGS = M_FLAGS WHERE PKEY = M_PKEY; END LOOP; DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@remotedb1(C);
Now I have a specific db-link name,
remotedb1, in my package body, while I need to retarget it to another db-link rather often. I tried creating a synonym for a db-link, but that wouldn't work. Is there another way around it other than using a
&&variable and recompiling from source? Will wrapping each call in
EXECUTE IMMEDIATE work?