6 Replies Latest reply on Dec 14, 2019 11:19 PM by fac586

    Creating PL/sql Function Body in classic report

    2765426

      Sorry about that.

       

      I am trying to create PL/SQL Function Body in APEX 19.2 .

       

      Attached the query below:

       

      return q'{SELECT

      sum(a - b)+

      (c - d) TOTAL_EXPENDITURE

      FROM xxx_period_table

      WHERE PERIOD_NAME in &P59_PERIOD.

      AND cost_centre IN ('1234','5432') }';

       

      This works fine in APEX 18.2 but throws an error in APEX 19.2. attached the error below.

       

      report error:

      ORA-00907: missing right parenthesis

        • 1. Re: Creating PL/sql Function Body in classic report
          fac586

          2765426 wrote:

          Please update your forum profile with a recognisable username instead of "2765426".

           

          Always include the information described in these guidelines when posting a question: how to get answers from forum

          I am trying to create PL/SQL Function Body in APEX 19.2 .

           

          Attached the query below:

           

          return q'{SELECT

          sum(a - b)+

          (c - d) TOTAL_EXPENDITURE

          FROM xxx_period_table

          WHERE PERIOD_NAME in &P59_PERIOD.

          AND cost_centre IN ('1234','5432') }';

           

          This works fine in APEX 18.2 but throws an error in APEX 19.2. attached the error below.

           

          report error:

          ORA-00907: missing right parenthesis

          When does it throw the error? In the Application Builder at design time? Or in the application at runtime?

           

          What type of item is P59_PERIOD? What value(s) does it take? How, where, and when is this set?

           

          Why is a dynamic SQL data source being used at all? The main use cases for doing so are variable data sources or dynamic pivots, neither of which exist here.

          • 2. Re: Creating PL/sql Function Body in classic report
            Mike Kutz

            Please don't write code like that.

            https://xkcd.com/327/

             

            When creating dynamic SQL, always send the results to APEX_DEBUG.MESSAGE() before you RETURN it.

             

            What version is your database?

            There are other ways... ways that use BIND variables, not String Substitution. some require a minimum version.

             

            What are you trying to accomplish?

             

            MK

            • 3. Re: Creating PL/sql Function Body in classic report
              fac586

              Mike Kutz wrote:

               

              When creating dynamic SQL, always send the results to APEX_DEBUG.MESSAGE() before you RETURN it.

              APEX has done that automatically for several versions...

              • 4. Re: Creating PL/sql Function Body in classic report
                Mike Kutz

                2765426 wrote:

                 

                kkk

                Attempting to delete your post is frowned upon.

                Since faq586 has a copy of your original post, it is also pointless.

                 

                Instead, please mark your thread as Assumed Answered.  (if you have a solution, post it then flag it as "Correct")

                • 5. Re: Creating PL/sql Function Body in classic report
                  Natalie G

                  omg, momn!

                   

                  Neither of you geniuses offered any substantive help whatsoever -- why in the world would you conclude this was Assumed Answered? (Although the q. in re the value of P59_PERIOD is on point.)

                   

                  You did notice this?

                  This works fine in APEX 18.2 but throws an error in APEX 19.2. attached the error below.

                   

                  @2765426 : You can't delete your question, but you can move it somewhere else. Maybe to `Spaces > Oracle Groundbreakers > Community Feedback`.

                  • 6. Re: Creating PL/sql Function Body in classic report
                    fac586

                    Natalie G wrote:

                     

                    omg, momn!

                    What does that mean? OTN is not SMS, Twitter, or Facebook. This is a community of professionals and professional standards of discourse are expected. Posts should use language appropriate to a commercial email, not casual texting between 12 year olds.

                     

                    Additionally, most of the community are not native English speakers. Colloquialisms and slang are therefore best avoided to eliminate confusion.

                    Neither of you geniuses

                    Sarcasm has hitherto been markedly absent from this forum. Please read the Terms of Use, specifically noting the relevant parts of section 6:

                    You agree that you will neither use the Site in a manner, nor Share any Content, that: ... (b) is defamatory, derogatory, degrading or harassing of another or constitutes a personal attack;

                     

                    why in the world would you conclude this was Assumed Answered?

                    No one assumed that it was answered. Did you notice that the question was originally posted on 10 December? We assumed that "2765426" had withdrawn the question or lost interest when the contents of the original post was changed to "kkk" later that night. The original question content was reinstated on 13 December, but without any of the additional information requested, meaning that further progress remains impossible.

                    [Neither of you] offered any substantive help whatsoever

                    On the contrary, we concluded that we were unable to provide a definitive answer based on the information available. Rather than waste both our time and the OP's by making potentially misleading suggestions based on random guesses as to the possible cause, we provided "substantive" assistance in the form of requests for more information in order to determine the actual or most probable cause.

                    You did notice this?

                    This works fine in APEX 18.2 but throws an error in APEX 19.2. attached the error below.

                    Of course, but it is irrelevant at present.

                     

                    It could indicate the presence of a bug in APEX 19.2. If it does we can't isolate it without the diagnostic information already requested.

                     

                    It may be entirely coincidental and due to other changes to the application we have not been informed about, or as a result of different input data at runtime.

                     

                    The priorities are therefore to:

                     

                    1. Find out exactly when, where, and how the reported error occurs. As of last Tuesday we don't even know whether this is during development or at runtime.
                    2. Understand the underlying requirements so that in the event that it is an APEX bug we can suggest a suitable workaround.

                     

                    @2765426 : You can't delete your question, but you can move it somewhere else. Maybe to `Spaces > Oracle Groundbreakers > Community Feedback`.

                    Why would that be of any use? It would only provoke a double rebuke from the mods/admins: one for the deletion of the original post, and another for posting a product-related question in the clearly marked Community Feedback (No Product Questions) space. Then it would be moved back here.