Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

PLS-00357::Table,View Or Sequence reference 'string' not allowed in this context

2975076Oct 19 2016 — edited Nov 15 2016

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;

/

This post has been answered by unknown-7404 on Oct 19 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 16 2016
Added on Oct 19 2016
12 comments
11,800 views