Hi Gurus,
I'm having this issue with this piece of code.
I've run this code as a block PL/SQL and everything works fine (just testing inserting 2 line).
DECLARE
CURSOR remote_insert is
SELECT
UR.NAME as NAME,
CP.CUSTOMER_ID as CUSTOMER_ID,
CP.MOBILE_NUMBER as MOBILE_NUMBER,
CKD.FIRST_NAME AS FIRST_NAME,
CKD.LAST_NAME as LAST_NAME,
CP.EMAIL_ID as EMAIL_ID,
A.CURRENT_BALANCE/100 as "CURRENT_BALANCE"
FROM
copernicus.ACCOUNT A,
copernicus.USER_ROLE UR,
copernicus.CUSTOMER_PROFILE CP,
copernicus.CUSTOMER_KYC_DATA CKD
WHERE
CP.CUSTOMER_ID = A.CUSTOMER_ID AND
A.USER_ROLE_ID = UR.ID AND
CP.ENTITY_TYPE = 'CUSTOMER'
AND ROWNUM <= 2;
BEGIN
FOR rec IN remote_insert LOOP
INSERT INTO "ipw_rept_account_balance"@mobipw ("name","customer_id","mobile_number","first_name","last_name","email","current_balance")
VALUES (rec.name,rec.customer_id, rec.mobile_number,rec.first_name,rec.last_name,rec.email_id,rec.current_balance);
END loop;
--commit;
END;
/
When cheking remote table :
select count(*) from "ipw_rept_account_balance"@mobipw;
COUNT(*)
----------
2
When I run inside a procedure :
SQL> create or replace PROCEDURE LOAD_IPW_REPT_CURRENT_BALANCE
AS
CURSOR remote_insert is
SELECT
UR.NAME as NAME,
CP.CUSTOMER_ID as CUSTOMER_ID,
CP.MOBILE_NUMBER as MOBILE_NUMBER,
CKD.FIRST_NAME AS FIRST_NAME,
CKD.LAST_NAME as LAST_NAME,
CP.EMAIL_ID as EMAIL_ID,
A.CURRENT_BALANCE/100 as "CURRENT_BALANCE"
FROM
copernicus.ACCOUNT A,
copernicus.USER_ROLE UR,
copernicus.CUSTOMER_PROFILE CP,
CUSTOMER_KYC_DATA CKD -- Line 16
WHERE
CP.CUSTOMER_ID = A.CUSTOMER_ID AND
A.USER_ROLE_ID = UR.ID AND
CP.ENTITY_TYPE = 'CUSTOMER'
AND ROWNUM <= 2;
BEGIN
FOR rec IN remote_insert LOOP
execute immediate 'INSERT INTO "ipw_rept_account_balance"@mobipw ("name","customer_id","mobile_number","first_name","last_name","email","current_balance")
VALUES (rec.name,rec.customer_id, rec.mobile_number,rec.first_name,rec.last_name,rec.email_id,rec.current_balance)';
END loop;
--commit;
END;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
Warning: Procedure created with compilation errors.
SQL> show errors;
Errors for PROCEDURE LOAD_IPW_REPT_CURRENT_BALANCE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1 PL/SQL: SQL Statement ignored
16/2 PL/SQL: ORA-00942: table or view does not exist
I've create a public synonym just to avoid any "ambiguities"
and make a count(*) and result PK
SQL> select count(*) from CUSTOMER_KYC_DATA;
COUNT(*)
----------
1275
Whats wrong with this procedure????
Thanks in advance for your help.
Rgds
Carlos