EXECUTE IMMEDIATE DROP FUNCTION causes Oracle SQL Developer to hang-CodePudding

I have the following script. But whenever I include the line EXECUTE IMMEDIATE 'DROP FUNCTION table_exists';, SQL Developer seems to hang. Any idea what could be causing it?

If I cancel the task, the script runs to completion but doesn't drop the function. However, I can simply run DROP FUNCTION table_exists; in my DB connection and it drops the table no problem. Is it some quirk of PL/SQL?

CREATE OR REPLACE FUNCTION table_exists(table_name VARCHAR2)
RETURN BOOLEAN
AS
    table_count NUMBER := 0;
    exists_sql VARCHAR2(255);
BEGIN
    exists_sql := 'SELECT COUNT(1) FROM tab WHERE tname = :tab_name';
    EXECUTE IMMEDIATE exists_sql INTO table_count USING table_name;

    RETURN table_count > 0;
END;
/

DECLARE
    TYPE tables_array IS VARRAY(2) OF VARCHAR2(25); -- change varray size if running on dev
    tables tables_array;
BEGIN
    tables := tables_array(
        'FOO',
        'BAR'
    );

    FOR table_element IN 1 .. tables.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Backing up data for ' || tables(table_element));
        IF table_exists(tables(table_element) || '_ORIGINAL') THEN
            DBMS_OUTPUT.PUT_LINE(tables(table_element) || '_ORIGINAL already exists');
        ELSE
            EXECUTE IMMEDIATE 'CREATE TABLE ' || tables(table_element) || '_ORIGINAL AS SELECT * FROM '|| tables(table_element);
        END IF;
    END LOOP;

    EXECUTE IMMEDIATE 'DROP FUNCTION table_exists';

    COMMIT;

    EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE ('Unexpected error: ' || sqlerrm);
    ROLLBACK;
    RAISE;
END;
/

CodePudding user response：

Oracle won't drop a function which is being used in this very procedure.

Is there a workaround? Yes, at least one - submit a job which will do it in another session.

Sample tables (so that procedure won't fail because of that)

SQL> create table foo (id number);

Table created.

SQL> create table bar (id number);

Table created.

Create a function:

SQL> CREATE OR REPLACE FUNCTION table_exists(table_name VARCHAR2)
  2  RETURN BOOLEAN
  3  AS
  4      table_count NUMBER := 0;
  5      exists_sql VARCHAR2(255);
  6  BEGIN
  7      exists_sql := 'SELECT COUNT(1) FROM tab WHERE tname = :tab_name';
  8      EXECUTE IMMEDIATE exists_sql INTO table_count USING table_name;
  9
 10      RETURN table_count > 0;
 11  END;
 12  /

Function created.

Is it here? Yes, it is:

SQL> desc table_Exists;
FUNCTION table_Exists RETURNS BOOLEAN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TABLE_NAME                     VARCHAR2                IN

Run the main piece of code; note line #4 (declaration of a variable which will contain job number) and line #21 which submits a job.

SQL> DECLARE
  2      TYPE tables_array IS VARRAY(2) OF VARCHAR2(25); -- change varray size if running on dev
  3      tables tables_array;
  4      i number;  -- job number
  5  BEGIN
  6      tables := tables_array(
  7          'FOO',
  8          'BAR'
  9      );
 10
 11      FOR table_element IN 1 .. tables.COUNT LOOP
 12          DBMS_OUTPUT.PUT_LINE('Backing up data for ' || tables(table_element));
 13          IF table_exists(tables(table_element) || '_ORIGINAL') THEN
 14              DBMS_OUTPUT.PUT_LINE(tables(table_element) || '_ORIGINAL already exists');
 15          ELSE
 16              EXECUTE IMMEDIATE 'CREATE TABLE ' || tables(table_element) || '_ORIGINAL AS SELECT * FROM '|| tables(table_element);
 17          END IF;
 18      END LOOP;
 19
 20      -- EXECUTE IMMEDIATE 'DROP FUNCTION table_exists';
 21      dbms_job.submit(i, 'begin execute immediate ''drop function table_exists''; end;');
 22
 23      COMMIT;
 24
 25      EXCEPTION
 26          WHEN OTHERS THEN
 27            DBMS_OUTPUT.PUT_LINE ('Unexpected error: ' || sqlerrm);
 28      ROLLBACK;
 29      RAISE;
 30  END;
 31  /

PL/SQL procedure successfully completed.

Is the function still here? Nope, it was dropped.

SQL> desc table_exists;
ERROR:
ORA-04043: object table_exists does not exist


SQL>