2 Replies Latest reply: Oct 19, 2012 8:07 AM by jflack RSS

    CDS-11025 Error: Oracle Designer generation error with PL/SQL package

    479623
      Hi friends,

      We tried to generate a database package from Oracle Designer (10.1.2.6) and getting "syntax errors" but the package has no syntax errors when we copy and paste the code and compile in TOAD (10.6)

      Error from Designer:

      ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      Server Generator 10.1.2.6 (Build 10.1.2.11.12) , Wed Oct 17 10:58:43 2012
      Copyright (c) Oracle Corporation 1995, 2010. All rights reserved.

      CDS-11025 Error: The PL/SQL within PACKAGE BODY AVP_WATER_ANALYSIS_V has syntax errors - At token 'END', around:
      ...te_dist.VLV_INTL_ID;
      END LOOP;


      Processing Complete: 1 error(s), 0 warning(s)
      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      code snippet from the Error message:
      -----------------------------------------------------------------------------------------------------------
      FOR rec_update_dist IN cur_update_dist (i_anlyd_intl_id,
      i_iterd_intl_id,
      i_phsed_intl_id,
      i_wtrc_intl_id)
      LOOP
      UPDATE AVP_VALVE AvpValve
      SET AvpValve.DIST_FROM_EDGE = rec_update_dist.DIST_FROM_EDGE
      WHERE AvpValve.ANLYD_INTL_ID = rec_update_dist.ANLYD_INTL_ID
      AND AvpValve.ITERD_INTL_ID = rec_update_dist.ITERD_INTL_ID
      AND AvpValve.PHSED_INTL_ID = rec_update_dist.PHSED_INTL_ID
      AND AvpValve.WTRC_INTL_ID = rec_update_dist.WTRC_INTL_ID
      AND AvpValve.VLV_INTL_ID = rec_update_dist.VLV_INTL_ID;
      END LOOP;
      ----------------------------------------------------------------------------------------------------------

      Thanks for any feedback,

      Jim
        • 1. Re: CDS-11025 Error: Oracle Designer generation error with PL/SQL package
          479623
          Found the problem.
          It was an issue with the CURSOR with the SELECT ... FROM (SELECT CASE WHEN THEN ELSE END). After swithing to the DECODE statement, Designer is able to generate the package.

          /*
          CASE
          WHEN ISOLATION_IND = 'Y' THEN
          'A'
          WHEN OPERATED_IND = 'Y' THEN
          'B'
          ELSE
          'C'
          END
          */
          decode(ISOLATION_IND,'Y','A',decode(OPERATED_IND,'Y','B','C'))

          However, still strange to me that Designer doesn't like the CASE as part of SELECT.

          Edited by: user476620 on Oct 18, 2012 8:13 PM
          • 2. Re: CDS-11025 Error: Oracle Designer generation error with PL/SQL package
            jflack
            That is Designer showing its age and state of non-maintenance. Designer pre-dates Oracle's support for the ANSI SQL CASE structure, and no-one has bothered to go and update the SQL validation in Designer.

            Glad you figured this out for yourself - your question seemed very familiar, but I couldn't remember why. When you came up with the answer, I'm going "Ohhhh, yeahhhhh! Now I remember! I had that problem too."