SQL & PL/SQL

My script below tries to access data over a databaselink (targetdb) and then insert selected data in the sourcedatabase.

When i am trying construct

s_stmt :=
'INSERT INTO PS_BUS_UNIT_HR_LNG SELECT * FROM PS_BUS_UNIT_HR_LNG@targetdb WHERE business_unit = ''KPN68''';

EXECUTE IMMEDIATE s_stmt;

it works,

but when i replace this construct with

INSERT INTO PS_BUS_UNIT_HR_LNG SELECT * FROM PS_BUS_UNIT_HR_LNG@targetdb WHERE business_unit = 'KPN68';

(so not using dynamic SQL anymore via EXECUTE IMMEDIATE) i get an error stating that table or view cannot be found.


Does any of you guru's have any clue on the cause?

Please help me out.

Regards,
Tom Wesseling

--------------------------------------
script from here
------------------------------------


/* Formatted on 2012/05/09 14:27 (Formatter Plus v4.8.7) */
-------------------------------------------------------------
-- Script : RFC729728.sql
-- Author : Tom Wesseling
-- Project: RFC729728
-- Date : May 2012
-- Goal : Adding setid / BU / Company KPN68 / 680 to various tables.
-------------------------------------------------------------

SET SERVEROUTPUT ON SIZE 1000000;

SET verify ON;

DECLARE
s_stmt VARCHAR2 (1000);
n_version NUMBER;
databaselink_does_not_exist EXCEPTION;
PRAGMA EXCEPTION_INIT (databaselink_does_not_exist, -2024);
BEGIN
BEGIN
s_stmt := 'ACCEPT pswd VARCHAR2(100) PROMPT ''Enter password: ''';
END;

DBMS_OUTPUT.PUT_LINE ('Debug 1');

BEGIN
s_stmt := 'DROP DATABASE LINK targetdb';

EXECUTE IMMEDIATE s_stmt;

DBMS_OUTPUT.PUT_LINE ('Debug 2');
EXCEPTION
WHEN databaselink_does_not_exist
THEN
DBMS_OUTPUT.PUT_LINE ('asfdasfafsdf');
DBMS_OUTPUT.PUT_LINE
('-- Error databaselink_does_not_exist given. Processing continues.'
);
END;

BEGIN
s_stmt :=
'CREATE DATABASE LINK targetdb CONNECT TO sysadm IDENTIFIED BY &pswd USING ''HCMG''';

EXECUTE IMMEDIATE s_stmt;

DBMS_OUTPUT.PUT_LINE ('Debug a');
END;

-- Business Unit
BEGIN
DELETE FROM PS_BUS_UNIT_HR_LNG
WHERE business_unit = 'KPN68';

s_stmt :=
'INSERT INTO PS_BUS_UNIT_HR_LNG SELECT * FROM PS_BUS_UNIT_HR_LNG@targetdb WHERE business_unit = ''KPN68''';

EXECUTE IMMEDIATE s_stmt;

DBMS_OUTPUT.PUT_LINE ('Debug b');

DELETE FROM PS_BUS_UNIT_TBL_HR
WHERE business_unit = 'KPN68';

DBMS_OUTPUT.PUT_LINE ('Debug c');
s_stmt :=
'INSERT INTO PS_BUS_UNIT_TBL_HR SELECT * FROM PS_BUS_UNIT_TBL_HR@targetdb WHERE business_unit = ''KPN68''';

EXECUTE IMMEDIATE s_stmt;
--DBMS_OUTPUT.PUT_LINE ('Debug d');
END;

BEGIN
SELECT MAX (VERSION)
INTO n_version
FROM PSVERSION
WHERE objecttypename = 'PPC';

DELETE FROM PS_SET_CNTRL_TBL
WHERE setcntrlvalue = 'KPN68';

s_stmt :=
'INSERT INTO PS_SET_CNTRL_TBL SELECT setcntrlvalue, setid, 0 FROM PS_SET_CNTRL_TBL@targetdb WHERE setcntrlvalue IN (''KPN68'')';

EXECUTE IMMEDIATE s_stmt;

UPDATE PS_SET_CNTRL_TBL
SET VERSION = n_version
WHERE setcntrlvalue IN ('KPN68');
END;

BEGIN
s_stmt := 'DROP DATABASE LINK targetdb';

EXECUTE IMMEDIATE s_stmt;
END;
END;