Hi:
I've to fetch some data from a SQL Server database to Oracle (Private user) and then insert those data into same Oracle server but different Schema. I've created a DB link between SQL server and Oracle; then provided DML permission on the table (PROD: this table is under MS schema) where I want to insert. My private Oracle user named CDPDBUSR_MSSQL is actually fetching data from SQL Server and inserting those into another Schema name MS.
When I run this query from SQL window of CDPDBUSR_MSSQL it works fine but when I run through a procedure it says 'ORA-00942:Table or View doesn't exist'.
This query works fine:
INSERT INTO MS.PROD_INFO
SELECT "ProductCode", "ProductName", "SERIALNO"
FROM t_productitem@FW2CDP
WHERE "ActivationDate" BETWEEN
TO_DATE('2013-08-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
TO_DATE('2013-08-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
AND "ProductType" = 1
When i run same query in a procedure it shows error:
CREATE OR REPLACE PROCEDURE PROC_PRODUCT_INFO IS
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO MS.PROD_INFO
SELECT "ProductCode", "ProductName", "SERIALNO"
FROM t_productitem@FW2CDP
WHERE "ActivationDate" BETWEEN TO_CHAR(SYSDATE - 4, ''YYYY-MM-DD'') || '' 00:00:00''
AND TO_CHAR(SYSDATE - 4, ''YYYY-MM-DD'') || '' 23:59:59''
AND "ProductType" = 1';
COMMIT;
END PROC_PRODUCT_INFO;
Can anyone tell me why it's happening though table is already there.
Thanks/Tanvir