Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
I have a package create as below.
CREATE OR REPLACE PACKAGE pkg_test
AS
FUNCTION fn_test (p_a IN VARCHAR2)
RETURN VARCHAR2;
END pkg_test;
/
CREATE OR REPLACE PACKAGE BODY pkg_test
AS
FUNCTION fn_test (p_a IN VARCHAR2)
RETURN VARCHAR2
IS
nm VARCHAR (1000) DEFAULT NULL;
BEGIN
SELECT emp_name
INTO nm
FROM employee_details@dblink
WHERE employee_id = p_a;
RETURN nm;
END;
END pkg_test;
/
Next I tried to create a materialized view using this package :
create materialized view test as select pkg_test.fn_test('ID#') from dual;
But I got this error:
ORA-12840: cannot access a remote table after parallel/insert direct load txn
ORA-06512: at "ISGCOS.PKG_TEST", line 8
I can run the SELECT statement and it is working fine.
select pkg_test.fn_test('ID#') from dual;
I can create materialized view as well using the remote table directly into the Create materialized view script.
create materialized view test as select emp_name from employee_details@dblink where employee_id = 'ID#';
Any idea why am I getting the error when tried the package function to access the remote table to create materialized view.
And the remote database is a Oracle DB as well.
Thanks,
Sam