Hi,
Oracle version:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
"CORE 12.2.0.1.0 Production" 0
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0
I am trying to create a stored procedure which fetches records from remote database and inserts in my local table.
when i try to compile it is giving me error.
Error report -
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "DECL_OBJ#": invalid identifier
00604. 00000 - "error occurred at recursive SQL level %s"
*Cause: An error occurred while processing a recursive SQL statement
(a statement applying to internal dictionary tables).
*Action: If the situation described in the next error on the stack
can be corrected, do so; otherwise contact Oracle Support.
But when i run the insert into select statement individually/outside of procedure it is inserting reocrds without errors.
Can you help me identifying what wrong I am doing in procedure.
create or replace PROCEDURE REFRESH_ITAM_PBI_MTX
as
L_STS TIMESTAMP;
begin
Select systimestamp into L_STS from dual;
insert into STG_WDR_PBI_MTX(source_id, target_id)
SELECT
PBM_PROBLEM_INVESTIGATION.Problem_Investigation_ID ,
PM_RELATED_CHANGES.Infrastructure_Change_ID
FROM
(
SELECT DISTINCT FIELD_ENUM_VALUES.ENUMID, FIELD_ENUM_VALUES.VALUE AS PRIORITY
FROM FIELD_ENUM_VALUES@V9SITA
WHERE FIELD_ENUM_VALUES.SCHEMAID = 2609 AND FIELD_ENUM_VALUES.FIELDID = 1000000164
) PBM_PRIORITY RIGHT OUTER JOIN PBM_PROBLEM_INVESTIGATION@V9SITA ON (PBM_PROBLEM_INVESTIGATION.PRIORITY=PBM_PRIORITY.ENUMID)
LEFT OUTER JOIN (
SELECT PBM_INVESTIGATION_ASSOCIATIONS.REQUEST_ID02,
PBM_INVESTIGATION_ASSOCIATIONS.REQUEST_ID01,
PBM_INVESTIGATION_ASSOCIATIONS.REQUEST_TYPE01,
PBM_INVESTIGATION_ASSOCIATIONS.ASSOCIATION_TYPE01,
PBM_INVESTIGATION_ASSOCIATIONS.REQUEST_DESCRIPTION01,
CHG_CHANGEINTERFACE.INFRASTRUCTURE_CHANGE_ID,
CHG_CHANGEINTERFACE.LOCATION_COMPANY
FROM PBM_INVESTIGATION_ASSOCIATIONS@V9SITA,
CHG_CHANGEINTERFACE@V9SITA
WHERE PBM_INVESTIGATION_ASSOCIATIONS.REQUEST_ID01 = CHG_CHANGEINTERFACE.INFRASTRUCTURE_CHANGE_ID
AND CHG_CHANGEINTERFACE.LOCATION_COMPANY like 'Telstra%'
) PM_RELATED_CHANGES ON (PBM_PROBLEM_INVESTIGATION.PROBLEM_INVESTIGATION_ID=PM_RELATED_CHANGES.REQUEST_ID02);
commit;
end;
Thanks.