Re: a strange behavior in Oracle 12.2.0.1 by nested PLSQL cursor

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 29 Jan 2019 09:04:49 +0000
Message-ID: <CWXP265MB05033BA1B4CF13C9791377C7A5970_at_CWXP265MB0503.GBRP265.PROD.OUTLOOK.COM>

It works for me on an unpatched 12.2.0.1

In my case I log in, create a parent and child table, then run your code. Is the schema running your code the same as the schema owning the tables ? Could there be a difference in privileges rather than a bug.

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Ahmed <gherrami_at_gmail.com> Sent: 28 January 2019 14:57
To: ORACLE-L
Subject: a strange behavior in Oracle 12.2.0.1 by nested PLSQL cursor

Hi,

The following block should provide the commands to disable the foreign key pointing on a table. It works wonderfully in 11.2.0.4 and 12.1.0.2 but not on 12.2.0.1. Does not trigger an error but also no results. You can test it, just replace 'MYTABLE' with a table that has foreign key pointing on it.

DECLARE

• table that has Foreignkey pointing on it v_tbl_name VARCHAR2(30) := 'MYTABLE'; CURSOR get_massdata_tableinfo IS SELECT v_tbl_name table_name FROM dual

;

    CURSOR get_fks(par_target_table user_tables.table_name%TYPE)     IS

      WITH
      user_constr AS
      (
        SELECT *
         FROM all_constraints
         WHERE owner = sys_context('USERENV', 'CURRENT_SCHEMA')
      )
      SELECT r.constraint_name,
             r.table_name
       FROM user_constr r,
            user_constr t
       WHERE t.table_name = par_target_table
         AND t.constraint_type = 'P'
         AND t.constraint_name = r.r_constraint_name

;

BEGIN
  FOR crec IN get_massdata_tableinfo
    LOOP

      --
      dbms_output.put_line('Table Name ' || crec.table_name);
      -- disable FK´s pointing to table
      FOR rec IN get_fks(crec.table_name) --no rows in 12.2.0.1 (but it works in 11.2.0.4 and 12.1.0.2)
      LOOP
        dbms_output.put_line('ALTER TABLE ' || rec.table_name ||
             ' DISABLE CONSTRAINT ' || rec.constraint_name);

      END LOOP;

    END LOOP; END; If I call the SQL directly, then I get records:

WITH

      user_constr AS
      (
        SELECT *
         FROM all_constraints
         WHERE owner = sys_context('USERENV', 'CURRENT_SCHEMA')
      )
      SELECT r.constraint_name,
             r.table_name
       FROM user_constr r,
            user_constr t
       WHERE t.table_name = 'MY_TABLE'
         AND t.constraint_type = 'P'
         AND t.constraint_name = r.r_constraint_name

Is this a bug or have I just missed something?

Regards
Ahmed Fikri

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 29 2019 - 10:04:49 CET