7 Replies Latest reply: Jun 28, 2010 3:32 AM by Mkirtley-Oracle RSS

    PLS-00201: identifier must be declared

    781033
      We are attempting to execute an AS400 stored procedure via the gateway (v11.1.0.6). The stored procedure works just fine on the 400.

      The script is:

      SQL> DECLARE

      2 CMPNYNO DECIMAL(2,0);

      3 ACCT DECIMAL(8,0);

      4 PROD DECIMAL(4,0);

      5 DISCDATE DECIMAL(8,0);

      6 DISCAMTD DECIMAL(9,2);

      7 DISCAMT DECIMAL(9,2);

      8 RTNCDTN DECIMAL(3,0);

      9 BEGIN

      10 CMPNYNO := 1;

      11 ACCT := 12873816;

      12 PROD := 3;

      13 DISCDATE := 20101001;

      14 PGMLIB.RS7235P@itcd_dqa(CMPNYNO, ACCT, PROD, DISCDATE, DISCAMTD, DISCAMT, RTNCDTN);

      15 END;



      The error returned is:

      ERROR at line 14:

      ORA-06550: line 14, column 1:

      PLS-00201: identifier 'PGMLIB.RS7235P@ITCD_DQA' must be declared

      ORA-06550: line 14, column 1:

      PL/SQL: Statement ignored


      I have granted execute authority to the user profile associated to the gateway.

      We execute "normal" queries to tables on the 400 without problem. This is the first time we've tried accessing a stored procedure, so this is new territory.

      Advice?
        • 1. Re: PLS-00201: identifier must be declared
          Mkirtley-Oracle
          Hi,
          I see you have also opened a tar with Support about this.
          As this is the first time you've tried an AS400 store procedure make sure that -

          - you have journalled the AS400 ORACLE2PC table
          - you have followed the steps in the documentation -

          Oracle® Database Gateway for DB2/400
          Installation and User’s Guide
          10g Release 2 (10.2) for IBM iSeries OS/400

          - 9.4.1 Executing DB2/400 Stored Procedures from Applications

          Regards,
          Mike
          • 2. Re: PLS-00201: identifier must be declared
            781033
            Thanks for the response Mike. Yes, our Oracle support team has opened a TAR. I'm support from the AS400 side and just wanted to be sure everything is covered from this side. The ORACLE2PC table is currently journaled.

            Maximum record length . . . . . . . . . . . : 1236
            Volatile . . . . . . . . . . . . . . . . . : No
            File is currently journaled . . . . . . . . :            Yes
            Current or last journal . . . . . . . . . . : QSQJRN
            Library . . . . . . . . . . . . . . . . . : DELTACRM
            Journal images . . . . . . . . . . . . . . : IMAGES *BOTH        
            Journal entries to be omitted . . . . . . . : OMTJRNE *OPNCLO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
            • 3. Re: PLS-00201: identifier must be declared
              Mkirtley-Oracle
              Hi,
              As far as I can see everything looks okay and there isn't an obvious cause for the problem.
              So, could you check the following -

              - the procedure name PGMLIB.RS7235P is correctly defined as ROUTINE_SCHEMA and ROUTINE_NAME in QSYS2/SYSPROCS
              - does a gateway debug trace show any errors
              - that the Oracle database link itcd_dqa is defined with the user that has the privileges on the stored procedure
              - a 10046 event trace on the Oracle side may also show something and this has been requested in the tar.

              Regards,
              Mike
              • 4. Re: PLS-00201: identifier must be declared
                Mkirtley-Oracle
                Hi,
                The tar for this problem was opened as TG4DB2400 running on the AS400 but it looks like you are actually using the Gateway for DRDA which must be running on your Sun box.
                This makes a difference to how procedures are called and the tracing, but we still need you to check the AS400 side.
                The trace will be crated on the Sun box, not the AS400.

                Regards,
                Mike
                • 5. Re: PLS-00201: identifier must be declared
                  781033
                  Mike,

                  I did verify that QSYS2/SYSPROCS and QSYS2/SYSPARMS do contain the valid information related to the procedure.

                  Early on I did grant execute privileges to the user associated to the link.

                  The other issues will have to be reviewed by our Oracle team, so I will pass that along to them.

                  Thanks again for the response.
                  Kevin
                  • 6. Re: PLS-00201: identifier must be declared
                    781033
                    Apparently there was some issue with the id associated to the link. The link was modified to use a different id and suddenly everything worked as expected.
                    • 7. Re: PLS-00201: identifier must be declared
                      Mkirtley-Oracle
                      Kevin,
                      Thanks very much for letting us know the cause and solution.

                      Regards,
                      Mike