10 Replies Latest reply on Oct 11, 2017 6:06 PM by Gary Graham-Oracle

    SQL Developer - PL/SQL bulk collection code - errors

    DB Expert

      SQL Developer PL/SQL Bulk collection errors

       

      Java(TM) Platform1.8.0_131
      Oracle IDE17.2.0.188.1159
      Versioning Support17.2.0.188.1159

      DECLARE

         CURSOR c_pl_updt_1

         IS

            SELECT

                  SEO_TAG_ID,

                  DISPLAY_NAME,

                  TITLE,

                  DESCRIPTION,

                  KEYWORDS,

                  CONTENT_KEY,

      --(REGEXP_REPLACE (SHORT_DESC, '("){2,}', '"')) SHORT_DESC,

      --(REGEXP_REPLACE (GOOGLE_SHORT_DESC, '("){2,}', '"')) GOOGLE_SHORT_DESC,

      --(REGEXP_REPLACE (EBAY_SHORT_DESC, '("){2,}', '"')) EBAY_SHORT_DESC,           

              FROM TAP_CATA.DAS_SEO_TAG_STG;

       

         TYPE s1_info_t IS TABLE OF c_pl_updt_1%ROWTYPE

            INDEX BY PLS_INTEGER;

         data_s1   s1_info_t;

       

       

         c_limit   NUMBER := 50000;

         l_errno   NUMBER;

         l_msg     VARCHAR2 (4000);

         l_idx     NUMBER;

      BEGIN

         EXECUTE IMMEDIATE 'alter session enable parallel query';

         --  EXECUTE IMMEDIATE 'alter session set sort_area_size = 20485760';

       

         OPEN c_pl_updt_1;

       

         LOOP

            FETCH c_pl_updt_1

              BULK COLLECT INTO data_s1

            LIMIT c_limit;

       

            BEGIN

               FORALL i IN 1 .. data_s1.COUNT SAVE EXCEPTIONS

                  DELETE FROM TAP_PUB.DAS_SEO_TAG A

                   WHERE A.SEO_TAG_ID = data_s1 (i).SEO_TAG_ID;-- and A.IS_HEAD=1;

      --               SET 

      --                    A.DISPLAY_NAME = data_s1 (i).DISPLAY_NAME,

      --                    A.DESCRIPTION = data_s1 (i).DESCRIPTION,

      --                    A.KEYWORDS = data_s1 (i).KEYWORDS,

      --                    A.CONTENT_KEY = data_s1 (i).CONTENT_KEY                  

      --                   A.LONG_DESC = data_s1 (i).LONG_DESC,

      --                   A.GOOGLE_LONG_DESC = data_s1 (i).GOOGLE_LONG_DESC,

      --                   A.EBAY_LONG_DESC = data_s1 (i).EBAY_LONG_DESC,

      --                   A.SALES_DESC = data_s1 (i).SALES_DESC,

      --                   A.GOOGLE_SALES_DESC = data_s1 (i).GOOGLE_SALES_DESC,

      --                   A.EBAY_SALES_DESC = data_s1 (i).EBAY_SALES_DESC

       

               ---testing only uncomment this

               DBMS_OUTPUT.put_line (

                     'TAP_PUB.TAP_DESCRIPTION_XLATE Number of rows updated:'

                  || SQL%ROWCOUNT);

               COMMIT;

             

            EXCEPTION

               WHEN OTHERS

               THEN

                  FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT

                  LOOP

                     l_errno := SQL%BULK_EXCEPTIONS (indx).ERROR_CODE;

       

                     l_msg := SQLERRM (-l_errno);

       

                     l_idx := SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX;

                     --               DBMS_OUTPUT.put_line (

                     --                     'l_errno-'

                     --                  || l_errno

                     --                  || ',l_msg-'

                     --                  || l_msg

                     --                  || 'l_idx-'

                     --                  || l_idx);

       

                     INSERT INTO SYNC_ERR_LOG (DB_NAME,

                                               ERR_CODE,

                                               ERR_MSG,

                                               ERR_INDEX)

                     VALUES ('ATGTAP3871',

                             l_errno,

                             l_msg,

                             l_idx);

                     DBMS_OUTPUT.put_line (l_errno || ' - ' || l_msg);

                  END LOOP;

       

                  RAISE;

            END;

       

            COMMIT;

       

       

            EXIT WHEN c_pl_updt_1%NOTFOUND;

         END LOOP;

      COMMIT;

       

         CLOSE c_pl_updt_1;

      END;

       

       

      Error report -

      ORA-06550: line 14, column 9:

      PL/SQL: ORA-00936: missing expression

      ORA-06550: line 4, column 7:

      PL/SQL: SQL Statement ignored

      ORA-06550: line 453, column 1:

      PLS-00103: Encountered the symbol "SELECT"

      06550. 00000 -  "line %s, column %s:\n%s"

      *Cause:    Usually a PL/SQL compilation error.

      *Action:

        • 1. Re: SQL Developer - PL/SQL bulk collection code - errors
          Sven W.

          The error message is correct.

          It is a simple plsql syntax error. I'm not sure why you think it has to do with SQL Developer.

           

          ORA-06550: line 14, column 9:

          PL/SQL: ORA-00936: missing expression

           

          Check Line 14 and think why the compiler says there is an expression missing at that point.

          Doing so might help to find future syntax errors faster.

          • 2. Re: SQL Developer - PL/SQL bulk collection code - errors

            Wrong forum!

             

            This forum is ONLY for questions/issues regarding Sql Developer and your question has NOTHING to do with that product.

             

            Please mark the thread ANSWERED and repost in the sql and pl/sql forum if you still need help.

            SQL & PL/SQL

             

                  SELECT

                        SEO_TAG_ID,

                        DISPLAY_NAME,

                        TITLE,

                        DESCRIPTION,

                        KEYWORDS,

                        CONTENT_KEY,

            --(REGEXP_REPLACE (SHORT_DESC, '("){2,}', '"')) SHORT_DESC,

            --(REGEXP_REPLACE (GOOGLE_SHORT_DESC, '("){2,}', '"')) GOOGLE_SHORT_DESC,

            --(REGEXP_REPLACE (EBAY_SHORT_DESC, '("){2,}', '"')) EBAY_SHORT_DESC,           

                    FROM TAP_CATA.DAS_SEO_TAG_STG;

            The above is the ONLY  place in what you posted that contains the word 'SELECT'.

             

            See if you can find the problem with this query

            SELECT a, FROM myTable

            Did you spot the problem?

             

            Get rid of that WHEN OTHERS exception handler.

                  EXCEPTION

                     WHEN OTHERS

                     THEN

                        FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT

            That code attempts to use the bulk error collection for ALL EXCEPTIONS. Does that make sense to you?

             

            Read the docs and learn what exception to trap and use when performing bulk processing.

            • 3. Re: SQL Developer - PL/SQL bulk collection code - errors
              DB Expert

              This code running perfectly in SQLPLUS.exe (command line tool)!

              SQL Developer having bugs for bulk collection code! 

              • 4. Re: SQL Developer - PL/SQL bulk collection code - errors
                DB Expert

                Have tested the code in SQL Developer?

                Please test yourself code in SQL Developer, before posting some answers. simply don't post without using the tool.  if you dont have an answer, dont post some answers.

                This code running perfectly in SQLPLUS !

                SQL Developer having bugs for bulk collection code!

                • 5. Re: SQL Developer - PL/SQL bulk collection code - errors
                  Sven W.

                  3401549 wrote:

                   

                  This code running perfectly in SQLPLUS.exe (command line tool)!

                  SQL Developer having bugs for bulk collection code!

                  Prove it. Run this exact code in sql plus and show us that it works there. It does have an syntax error.

                  When SQL Plus will not show you that error, then SQL Plus is buggy. However I think you made some slight changes and by doing so introduced this issue.

                   

                  Unfortunatly we can not test your code, since you reference tables that we do not have.

                  If you provide create table and insert scripts as well, then we can test if it works in our SQL Developer instance.

                  • 6. Re: SQL Developer - PL/SQL bulk collection code - errors
                    Frank Kulash

                    Hi,

                    3401549 wrote:

                     

                    Have tested the code in SQL Developer?

                    Please test yourself code in SQL Developer, ...

                    Nobody can test anywhere until you post CREATE TABLE and INSERT statements for any tables referenced.

                     

                    If you don't post that, people will still try to help as much as they can (for example, if there's an obvious syntax error).

                    • 7. Re: SQL Developer - PL/SQL bulk collection code - errors

                      This code running perfectly in SQLPLUS !

                      No it isn't - it is NOT Possible for the code you posted to run ANYWHERE because it has syntax errors as I showed you.

                       

                      Just try running the sample query I provided and see if it works.

                       

                      SQL Developer having bugs for bulk collection code!

                      No - it is YOUR CODE that has the 'bug' and it has NOTHING to do with collection code.

                       


                      • 8. Re: SQL Developer - PL/SQL bulk collection code - errors
                        DB Expert

                        Please stop posting your messages further related to this.  I knew that this is a bug.  You never tested the code at all.  You never looked know the flow of the code here. Please stop posting your messages/answers.

                        • 9. Re: SQL Developer - PL/SQL bulk collection code - errors
                          DB Expert

                          dont need answers for this question.  Please stop posting answers.

                          • 10. Re: SQL Developer - PL/SQL bulk collection code - errors
                            Gary Graham-Oracle

                            If you do not like the (quite excellent) help and advice provided by the other posters in this discussion, you can always open a Service Request (SR) with My Oracle Support (MOS).  Of course, you will be asked to provide a complete test case, including any DDL your PL/SQL Bulk collection case depends on.