5 Replies Latest reply on Jul 4, 2017 6:20 AM by Marwim

    SQLDeveloper -4.2

    3497137

      MY code: in sql developer 4.2

      SELECT  'DECLARE  V_CNT NUMBER;  BEGIN SELECT COUNT(*) INTO V_CNT FROM USER_OBJECTS WHERE OBJECT_TYPE=''PROCEDURE'' AND UPPER(OBJECT_NAME)=UPPER('''||OBJECT_NAME||'''); IF V_CNT=1 THEN EXECUTE IMMEDIATE ''DROP PROCEDURE '||OBJECT_NAME ||'''; END IF; END;

      / '

      FROM

          user_objects

      WHERE

              object_type = 'PROCEDURE'

          AND

              object_name LIKE 'SP_%';

       

      press F5

       

      Error:

       

      Error starting at line : 1 in command -

      SELECT  'DECLARE  V_CNT NUMBER;  BEGIN SELECT COUNT(*) INTO V_CNT FROM USER_OBJECTS WHERE OBJECT_TYPE=''PROCEDURE'' AND UPPER(OBJECT_NAME)=UPPER('''||OBJECT_NAME||'''); IF V_CNT=1 THEN EXECUTE IMMEDIATE ''DROP PROCEDURE '||OBJECT_NAME ||'''; END IF; END

      Error at Command Line : 1 Column : 238

      Error report -

      SQL Error: ORA-01756: quoted string not properly terminated

      01756. 00000 -  "quoted string not properly terminated"

      *Cause:   

      *Action:

       

      Error starting at line : 3 in command -

      FROM

      Error report -

      Unknown Command

       

       

      Error starting at line : 4 in command -

          user_objects

      Error report -

      Unknown Command

       

       

      Error starting at line : 5 in command -

      WHERE

      Error report -

      Unknown Command

       

       

      Error starting at line : 6 in command -

              object_type = 'PROCEDURE'

      Error report -

      Unknown Command

       

      SP2-0044: For a list of known commands enter HELP

      and to leave enter EXIT.

       

      Error starting at line : 7 in command -

          AND

      Error report -

      Unknown Command

       

       

      Error starting at line : 8 in command -

              object_name LIKE 'SP_%'

      Error report -

      Unknown Command

        • 1. Re: SQLDeveloper -4.2
          Marwim

          Your string has a slash at the beginning of a new line. This is interpreted as end of statement.

          Add the line break with the ascii code

           

          END IF; END;'||chr(10)||'/ '

           

          Regards

          Marcus

          • 2. Re: SQLDeveloper -4.2
            3497137

            This is also getting following error.

             

             

            Error starting at line : 2 in command -

            END IF; END;'||chr(10)||'/ '

            Error report -

            Unknown Command

             

             

            Error starting at line : 4 in command -

            FROM

            Error report -

            Unknown Command

             

             

            Error starting at line : 6 in command -

                user_objects

            Error report -

            Unknown Command

             

            SP2-0044: For a list of known commands enter HELP

            and to leave enter EXIT.

             

            Error starting at line : 8 in command -

            WHERE

            Error report -

            Unknown Command

             

             

            Error starting at line : 10 in command -

                    object_type = 'PROCEDURE'

            Error report -

            Unknown Command

            • 3. Re: SQLDeveloper -4.2
              Marwim
              SELECT  'DECLARE  V_CNT NUMBER;  BEGIN SELECT COUNT(*) INTO V_CNT FROM USER_OBJECTS WHERE OBJECT_TYPE=''PROCEDURE'' AND UPPER(OBJECT_NAME)=UPPER('''||OBJECT_NAME||'''); IF V_CNT=1 THEN EXECUTE IMMEDIATE ''DROP PROCEDURE '||OBJECT_NAME ||'''; END IF; END;'||chr(10)||'/ '
              FROM user_objects
              WHERE object_type = 'PROCEDURE'
              AND object_name LIKE 'SP_%';
              

               

              Works for me

               

              Does it show a different error message when you use quoted strings

               

              SELECT

              q'[DECLARE ]'||

              q'[    V_CNT NUMBER; ]'||

              q'[BEGIN ]'||

              q'[    SELECT COUNT(*) ]'||

              q'[    INTO V_CNT ]'||

              q'[    FROM USER_OBJECTS ]'||

              q'[    WHERE OBJECT_TYPE='PROCEDURE' ]'||

              q'[    AND UPPER(OBJECT_NAME)=UPPER(']'||OBJECT_NAME||q'['); ]'||

              q'[    IF V_CNT=1 THEN ]'||

              q'[    EXECUTE IMMEDIATE 'DROP PROCEDURE]'||OBJECT_NAME ||q'['; ]'||

              q'[    END IF; ]'||

              q'[END;]'||CHR(10)||q'[/ ]'

              FROM user_objects

              WHERE object_type = 'PROCEDURE'

              AND object_name LIKE 'SP_%';

              • 4. Re: SQLDeveloper -4.2
                3497137

                Thank you

                • 5. Re: SQLDeveloper -4.2
                  Marwim

                  If it works now then please mark the question as answered so other readers know from looking at the thread icon. Then they can invest their time on other threads.

                  You don't need to mark a reply as correct you can also set the thread directly to "assumed answered"