9 Replies Latest reply: Mar 8, 2013 10:24 AM by Frank Kulash RSS

    Problem with blocks

    NTW
      Can someone please tell me what the problem is with this code? When I compile it, I get Error(244,5): PLS-00103: Encountered the symbol "BEGIN" . It appears to be a problem with my third block.

      PROCEDURE apps_initialize(p_source_name IN VARCHAR2
      ,x_status OUT NOCOPY VARCHAR2)
      IS
      l_user_id NUMBER;
      l_responsibility_id NUMBER;
      l_application_id NUMBER;
      BEGIN
      SELECT user_id
      INTO l_user_id
      FROM fnd_user
      WHERE user_name = 'JONES'
      EXCEPTION
      WHEN OTHERS
      THEN
      --log errors
      conc_log('Unexpected error in APPS_INITIALIZE while determining the Login User with Error Message: ' || SUBSTR(SQLERRM, 1, 250));
      RAISE;
      END;
      BEGIN
      SELECT responsibility_id
      INTO l_responsibility_id
      FROM fnd_responsibility_tl
      WHERE responsibility_name = 'GOV Payables Manager';
      EXCEPTION
      WHEN OTHERS
      THEN
      --log errors
      conc_log('Unexpected error in APPS_INITIALIZE while determining the Responsibility "GOV Payables Manager" with Error Message: ' || SUBSTR(SQLERRM, 1, 250));
      RAISE;
      END;
      BEGIN -- (This is where the compilation is failing)
      SELECT application_id
      INTO l_application_id
      FROM fnd_application
      WHERE application_short_name = 'SQLAP';
      EXCEPTION
      WHEN OTHERS
      THEN
      --log errors
      conc_log('Unexpected error in APPS_INITIALIZE while determining the Application "Oracle Payables (SQLAP)" with Error Message: ' || SUBSTR(SQLERRM, 1, 250));
      RAISE;
      END;

      IF fnd_global.user_id < 0 AND fnd_global.resp_id < 0 AND fnd_global.resp_appl_id < 0
      THEN
      fnd_global.apps_initialize(l_user_id,l_responsibility_id, l_application_id);
      END IF;

      x_status := g_status_success;
      EXCEPTION
      WHEN OTHERS
      THEN
      x_status := g_status_failure;
      END apps_initialize;
        • 1. Re: Problem with blocks
          sb92075
          To start with the top most SELECT does not have a terminating semicolon;

          IMO the plethora of EXCEPTION handlers are BUGS waiting to misbehave without warning.

          How do I ask a question on the forums?
          SQL and PL/SQL FAQ


          scroll down to #9 to learn how to use
           tags                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
          • 2. Re: Problem with blocks
            Frank Kulash
            Hi,

            Also, you have 3 BEGIN statements and 4 END statements. Perhaps you meant to have 2 consecutive BEGIN statements where the first one is now.


             

            You may have noticed that this site normally doesn't display multiple spaces in a row.
            Whenever you post formatted text (including, but not limited to, code) on this site, type these 6 characters:

            \
            (small letters only, inside curly brackets) before and after each section of formatted text, to preserve spacing.
            
            
             
            
            I hope this answers your question.
            If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
            Explain, using specific examples, how you get those results from that data.
            Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
            See the forum FAQ {message:id=9360002}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
            • 3. Re: Problem with blocks
              NTW
              Thanks for your quick response. My code actually does have a semicolon after the name 'JONES' in the first block. At the last minute before posting this question, I changed the name and unintentionally removed the ending semicolon. But my code does have it.

              Is there another way for me to capture the EXCEPTIONS following each SELECT INTO?

              -NTW
              • 4. Re: Problem with blocks
                NTW
                Thank you, Frank. I tried two consecutive BEGINs but that did not compile either. Any other suggestions? (And thanks for the spacing hint!)
                • 5. Re: Problem with blocks
                  Frank Kulash
                  Hi,
                  user573999 wrote:
                  Thank you, Frank. I tried two consecutive BEGINs but that did not compile either. Any other suggestions?
                  Yes; my other suggestions are:
                  Frank Kulash wrote:
                  ... post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
                  Explain, using specific examples, how you get those results from that data.
                  Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
                  See the forum FAQ {message:id=9360002}
                  Post your revised code, and the complete error message too.
                  Simplify your procedure, if possioble, so that it still produces the same error, but doesn't depend on so many tables. That way, you won't need to post so much.
                  • 6. Re: Problem with blocks
                    sb92075
                    user573999 wrote:
                    Can someone please tell me what the problem is with this code? When I compile it, I get Error(244,5): PLS-00103: Encountered the symbol "BEGIN" . It appears to be a problem with my third block.
                    since there are NOT 244 lines in posted code, your obfuscation prevents debugging here.

                    You're On Your Own (YOYO)!
                    • 7. Re: Problem with blocks
                      NTW
                      I have already simplified the code as much as possible. I have a package which calls the given procedure (among many other procedures). To give you tables and fields will only complicate the question. My problem is not with the data, it's with compiling the code. I have also already provided the complete error message.

                      Error(244,5): PLS-00103: Encountered the symbol "BEGIN"

                      I wonder if I should move all the EXCEPTIONS together? But then I will lose the specifics about the EXCEPTION.

                      I am compiling this using SQL Developer version 3.2.10.09; we are on Oracle 11g.
                      • 8. Re: Problem with blocks
                        NTW
                        That's because I pulled out a portion of the code to make it simpler to look at. The line 244 references the BEGIN in the 3rd block in the given code.
                        • 9. Re: Problem with blocks
                          Frank Kulash
                          Hi,
                          user573999 wrote:
                          I have already simplified the code as much as possible.
                          So if you remove any one of the 3 nested blocks, the problem goes away? Post the smallest version of your code that produces the error, and the version, just a little bit smaller, that does not produce the error.
                          I have a package which calls the given procedure (among many other procedures). To give you tables and fields will only complicate the question.
                          Actualy, just the opposite. It will allow the people who want to help you to see the problem for themselves, and to test their ideas.
                          My problem is not with the data, it's with compiling the code.
                          Exactly: I can't compile the code without the tables. Posting INSERT statements for the data would help in getting the right results, but there's a chance of finding the problem, with just the CREATE TABLE statements. Table with data are better than tables without data; no tables at all is worse than either.
                          I have also already provided the complete error message.

                          Error(244,5): PLS-00103: Encountered the symbol "BEGIN"

                          I wonder if I should move all the EXCEPTIONS together? But then I will lose the specifics about the EXCEPTION.
                          Yes, you would lose the specifics, but do you need them to solve this problem? When you simplify the code to isolate the problem, you will not get the same results. What you will get is a clearer picture of what the problem is, and how to solve it. Once you've solved the simplified problem in the simplified code, you will know how to solve the same problem in the real code.