Hi All,
I am a beginner in Oracle and will be great if you render your helping hand to solve this concern.
Error: PLS-00357::Table,View Or Sequence reference 'string' not allowed in this context
Oracle DB Version: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
I am unable to solve the concern.
Steps that i followed initially i was referring value of sequence directly to a variable and changed to select into..
Initial Snippet of code
--- P_OUT_REQ_ID := AERIAL_RPT_REQ_SEQ.CURRVAL;
Changed to :
SELECT AERIAL_USER.AERIAL_RPT_REQ_SEQ.CURRVAL INTO SEQ_NO FROM DUAL;
Yet no luck. Please need your help.
Code:
CREATE OR REPLACE PROCEDURE AERIAL_USER.MERGE_RPT_REQ(
P_REQUEST_ID IN AERIAL_USER.AERIAL_RPT_REQ.REQUEST_ID%TYPE,
P_STATUS IN AERIAL_USER.AERIAL_RPT_REQ.STATUS%TYPE,
P_RPT_NAME IN AERIAL_USER.AERIAL_RPT_REQ.RPT_NAME%TYPE,
P_FREQUENCY IN AERIAL_USER.AERIAL_RPT_REQ.FREQUENCY%TYPE,
P_SRC_SYSTEMS IN AERIAL_USER.AERIAL_RPT_REQ.SRC_SYSTEMS%TYPE,
P_PURPOSE IN AERIAL_USER.AERIAL_RPT_REQ.PURPOSE%TYPE,
P_WAS_CLONED IN AERIAL_USER.AERIAL_RPT_REQ.WAS_CLONED%TYPE,
P_FIELD_CHOSEN IN AERIAL_USER.AERIAL_RPT_REQ.FIELD_CHOSEN%TYPE,
P_PRIORITY IN AERIAL_USER.AERIAL_RPT_REQ.PRIORITY%TYPE,
P_DEVELOPMENT_OWNER IN AERIAL_USER.AERIAL_RPT_REQ.DEVELOPMENT_OWNER%TYPE,
P_ORIGINAL_REQ IN AERIAL_USER.AERIAL_RPT_REQ.ORIGINAL_REQ%TYPE,
P_REQUESTOR IN AERIAL_USER.AERIAL_RPT_REQ.REQUESTOR%TYPE,
P_SORT_ORDER IN AERIAL_USER.AERIAL_RPT_REQ.FIELD_CHOSEN%TYPE,
P_DESCRIPTION IN AERIAL_USER.AERIAL_RPT_REQ.DESCRIPTION%TYPE,
P_OTHER_RECEPIENTS IN AERIAL_USER.AERIAL_RPT_REQ.FIELD_CHOSEN%TYPE,
P_REG_APP_ID IN AERIAL_USER.AERIAL_RPT_REQ.REG_APP_ID%TYPE,
P_TECHNICIAN_COMMENTS IN AERIAL_USER.AERIAL_RPT_REQ.TECHNICIAN_COMMENTS%TYPE,
P_REG_REV_COMMENTS IN AERIAL_USER.AERIAL_RPT_REQ.REG_REV_COMMENTS%TYPE,
P_IS_RESUBMITTED IN AERIAL_USER.AERIAL_RPT_REQ.IS_RESUBMITTED%TYPE,
P_REPORT_FILTER_CRITERIA IN AERIAL_USER.AERIAL_RPT_REQ.FIELD_CHOSEN%TYPE,
P_REPORT IN AERIAL_USER.AERIAL_RPT_REQ.REPORT%TYPE,
P_ADDITIONAL_PURPOSE IN AERIAL_USER.AERIAL_RPT_REQ.ADDITIONAL_PURPOSE%TYPE,
P_OUT_REQ_ID OUT AERIAL_USER.AERIAL_RPT_REQ.REQUEST_ID%TYPE,
P_RESULT_STATUS OUT AERIAL_USER.AERIAL_RPT_REQ.RPT_NAME%TYPE,
P_TRANSITION_REASON IN AERIAL_USER.AERIAL_RPT_REQ.TRANSITION_REASON%TYPE,
P_REG_REV_STATUS IN AERIAL_USER.AERIAL_RPT_REQ.REG_REV_STATUS%TYPE,
P_AML_GOV_STATUS IN AERIAL_USER.AERIAL_RPT_REQ.AML_GOV_STATUS%TYPE,
P_AML_GOV_REV_COMMENTS IN AERIAL_USER.AERIAL_RPT_REQ.AML_GOV_REV_COMMENTS%TYPE,
P_REQUESTOR_NAME IN AERIAL_USER.AERIAL_RPT_REQ.REQUESTOR_NAME%TYPE,
P_REQ_PROD_DATE IN AERIAL_USER.AERIAL_RPT_REQ.REQ_PROD_DATE%TYPE)
IS
L_REQUEST_ID AERIAL_USER.AERIAL_RPT_REQ.REQUEST_ID%TYPE := P_REQUEST_ID;
L_STATUS AERIAL_USER.AERIAL_RPT_REQ.STATUS%TYPE := P_STATUS;
L_RPT_NAME AERIAL_USER.AERIAL_RPT_REQ.RPT_NAME%TYPE := P_RPT_NAME;
L_FREQUENCY AERIAL_USER.AERIAL_RPT_REQ.FREQUENCY%TYPE := P_FREQUENCY;
L_SRC_SYSTEMS AERIAL_USER.AERIAL_RPT_REQ.SRC_SYSTEMS%TYPE := P_SRC_SYSTEMS;
L_PURPOSE AERIAL_USER.AERIAL_RPT_REQ.PURPOSE%TYPE := P_PURPOSE;
L_WAS_CLONED AERIAL_USER.AERIAL_RPT_REQ.WAS_CLONED%TYPE := P_WAS_CLONED;
L_FIELD_CHOSEN AERIAL_USER.AERIAL_RPT_REQ.FIELD_CHOSEN%TYPE := P_FIELD_CHOSEN;
L_PRIORITY AERIAL_USER.AERIAL_RPT_REQ.PRIORITY%TYPE := P_PRIORITY;
L_DEVELOPMENT_OWNER AERIAL_USER.AERIAL_RPT_REQ.DEVELOPMENT_OWNER%TYPE:= P_DEVELOPMENT_OWNER;
L_ORIGINAL_REQ AERIAL_USER.AERIAL_RPT_REQ.ORIGINAL_REQ%TYPE := P_ORIGINAL_REQ;
L_REQUESTOR AERIAL_USER.AERIAL_RPT_REQ.REQUESTOR%TYPE := P_REQUESTOR;
L_SORT_ORDER AERIAL_USER.AERIAL_RPT_REQ.FIELD_CHOSEN%TYPE := P_SORT_ORDER;
L_DESCRIPTION AERIAL_USER.AERIAL_RPT_REQ.DESCRIPTION%TYPE := P_DESCRIPTION;
L_OTHER_RECEPIENTS AERIAL_USER.AERIAL_RPT_REQ.FIELD_CHOSEN%TYPE := P_OTHER_RECEPIENTS;
L_REG_APP_ID AERIAL_USER.AERIAL_RPT_REQ.REG_APP_ID%TYPE := P_REG_APP_ID;
L_TECHNICIAN_COMMENTS AERIAL_USER.AERIAL_RPT_REQ.TECHNICIAN_COMMENTS%TYPE := P_TECHNICIAN_COMMENTS;
L_REG_REV_COMMENTS AERIAL_USER.AERIAL_RPT_REQ.REG_REV_COMMENTS%TYPE:= P_REG_REV_COMMENTS;
L_IS_RESUBMITTED AERIAL_USER.AERIAL_RPT_REQ.IS_RESUBMITTED%TYPE := P_IS_RESUBMITTED;
L_REPORT_FILTER_CRITERIA AERIAL_USER.AERIAL_RPT_REQ.FIELD_CHOSEN%TYPE := P_REPORT_FILTER_CRITERIA;
L_REPORT AERIAL_USER.AERIAL_RPT_REQ.REPORT%TYPE := P_REPORT;
L_ADDITIONAL_PURPOSE AERIAL_USER.AERIAL_RPT_REQ.ADDITIONAL_PURPOSE%TYPE:= P_ADDITIONAL_PURPOSE;
L_TRANSITION_REASON AERIAL_USER.AERIAL_RPT_REQ.TRANSITION_REASON%TYPE := P_TRANSITION_REASON;
L_REG_REV_STATUS AERIAL_USER.AERIAL_RPT_REQ.REG_REV_STATUS%TYPE := P_REG_REV_STATUS;
L_AML_GOV_STATUS AERIAL_USER.AERIAL_RPT_REQ.AML_GOV_STATUS%TYPE := P_AML_GOV_STATUS;
L_AML_GOV_REV_COMMENTS AERIAL_USER.AERIAL_RPT_REQ.AML_GOV_REV_COMMENTS%TYPE := P_AML_GOV_REV_COMMENTS;
L_REQUESTOR_NAME AERIAL_USER.AERIAL_RPT_REQ.REQUESTOR_NAME%TYPE := P_REQUESTOR_NAME;
L_REQ_PROD_DATE AERIAL_USER.AERIAL_RPT_REQ.REQ_PROD_DATE%TYPE := P_REQ_PROD_DATE;
SEQ_NO NUMBER;
REQ_NO NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE ('L_REQUEST_ID:' || L_REQUEST_ID);
SELECT AERIAL_USER.AERIAL_RPT_REQ_SEQ.NEXTVAL INTO REQ_NO FROM DUAL;
MERGE INTO AERIAL_USER.AERIAL_RPT_REQ T
USING (SELECT 1 FROM DUAL) S
ON (NVL (T.REQUEST_ID, '667') = NVL (L_REQUEST_ID, '777'))
WHEN MATCHED
THEN
UPDATE SET T.RPT_NAME = L_RPT_NAME,
T.STATUS=L_STATUS,
T.PURPOSE = L_PURPOSE,
T.DESCRIPTION = L_DESCRIPTION,
T.PRIORITY = L_PRIORITY,
T.SRC_SYSTEMS = L_SRC_SYSTEMS,
T.FREQUENCY = L_FREQUENCY,
T.FIELD_CHOSEN = L_FIELD_CHOSEN,
T.DEVELOPMENT_OWNER = L_DEVELOPMENT_OWNER,
T.LAST_UPDATED = SYSDATE,
T.SORT_ORDER = L_SORT_ORDER,
T.OTHER_RECEPIENTS = L_OTHER_RECEPIENTS,
T.REG_APP_ID = L_REG_APP_ID,
T.IS_RESUBMITTED = L_IS_RESUBMITTED,
T.REPORT_FILTER_CRITERIA = L_REPORT_FILTER_CRITERIA,
T.REPORT = L_REPORT,
T.ADDITIONAL_PURPOSE=L_ADDITIONAL_PURPOSE,
T.TRANSITION_REASON=L_TRANSITION_REASON,
T.REG_REV_STATUS = L_REG_REV_STATUS,
T.AML_GOV_STATUS = L_AML_GOV_STATUS,
T.AML_GOV_REV_COMMENTS = L_AML_GOV_REV_COMMENTS,
T.REQUESTOR_NAME = L_REQUESTOR_NAME,
T.REQ_PROD_DATE = L_REQ_PROD_DATE
WHEN NOT MATCHED
THEN
INSERT (REQUEST_ID,
STATUS,
RPT_NAME,
FREQUENCY,
RPT_CREATE_DT,
SRC_SYSTEMS,
PURPOSE,
WAS_CLONED,
FIELD_CHOSEN,
PRIORITY,
DEVELOPMENT_OWNER,
ORIGINAL_REQ,
LAST_UPDATED,
REQUESTOR,
SORT_ORDER,
DESCRIPTION,
OTHER_RECEPIENTS,
REG_APP_ID,
TECHNICIAN_COMMENTS,
REG_REV_COMMENTS,
IS_RESUBMITTED,
REPORT_FILTER_CRITERIA,
REPORT,
ADDITIONAL_PURPOSE,
TRANSITION_REASON,
REG_REV_STATUS,
AML_GOV_STATUS,
AML_GOV_REV_COMMENTS,
REQUESTOR_NAME,
REQ_PROD_DATE
)
VALUES (REQ_NO,
---- AERIAL_USER.AERIAL_RPT_REQ_SEQ.NEXTVAL,
L_STATUS,
L_RPT_NAME,
L_FREQUENCY,
SYSDATE,
L_SRC_SYSTEMS,
L_PURPOSE,
L_WAS_CLONED,
L_FIELD_CHOSEN,
L_PRIORITY,
L_DEVELOPMENT_OWNER,
L_ORIGINAL_REQ,
SYSTIMESTAMP,
L_REQUESTOR,
L_SORT_ORDER,
L_DESCRIPTION,
L_OTHER_RECEPIENTS,
L_REG_APP_ID,
L_TECHNICIAN_COMMENTS,
L_REG_REV_COMMENTS,
L_IS_RESUBMITTED,
L_REPORT_FILTER_CRITERIA,
L_REPORT,
L_ADDITIONAL_PURPOSE,
L_TRANSITION_REASON,
L_REG_REV_STATUS,
L_AML_GOV_STATUS,
L_AML_GOV_REV_COMMENTS,
L_REQUESTOR_NAME,
L_REQ_PROD_DATE);
COMMIT;
IF (L_REQUEST_ID IS NULL)
THEN
--- P_OUT_REQ_ID := AERIAL_RPT_REQ_SEQ.CURRVAL;
SELECT AERIAL_USER.AERIAL_RPT_REQ_SEQ.CURRVAL INTO SEQ_NO FROM DUAL;
P_OUT_REQ_ID := SEQ_NO;
ELSE
P_OUT_REQ_ID := L_REQUEST_ID;
END IF;
P_RESULT_STATUS := 'SUCCESS';
DBMS_OUTPUT.put_line (P_RESULT_STATUS);
EXCEPTION
WHEN OTHERS
THEN -- handles all other errors
P_RESULT_STATUS := 'FAILURE';
DBMS_OUTPUT.put_line (SQLERRM);
END;
/