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

Yes the schema running the code is the same as the schema owning the tables

That's what I did:

CREATE USER tester IDENTIFIED BY tester QUOTA 10M ON USERS; GRANT CREATE SESSION TO tester;
GRANT CREATE TABLE TO tester;
-- after loggon as tester
CREATE TABLE PARENT(ID NUMBER PRIMARY KEY); CREATE TABLE PARENT(ID NUMBER PRIMARY KEY); CREATE TABLE CHILD(ID NUMBER, parent_id NUMBER ,CONSTRAINT FOREIGN KEY REFERENCES PARENT(ID)); DECLARE

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

      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

      --
      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;

But on 11.2.0.4 or 12.1.0.2-- the same steps I get as Output: Table Name PARENT
ALTER TABLE CHILD DISABLE CONSTRAINT SYS_C00444321 This confuses me

The context of these error is an attempt to upgrade our software from 11.2.0.4 to 12.2.0.1.
Everything works fine on 11.2.0.4. In the example block above I try to summarize only the error here in this list.

we have patched the following patch.

ARU platform id: 226

ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 12c
12.2.0.1.0

There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch 28662603 : applied on Sat Nov 24 14:07:07 CET 2018

Unique Patch ID: 22485591

Patch description: "Database Oct 2018 Release Update : 12.2.0.1.181016 (28662603)"

   Created on 5 Oct 2018, 03:26:32 hrs PST8PDT

Regards
Ahmed Fikri

Am Di., 29. Jan. 2019 um 10:05 Uhr schrieb Jonathan Lewis < jonathan_at_jlcomp.demon.co.uk>:

>
> 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
>
>
>

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