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

  • From: Ahmed <gherrami@xxxxxxxxx>
  • To: Andy Sayer <andysayer@xxxxxxxxx>
  • Date: Tue, 29 Jan 2019 15:12:44 +0100

It's already a cursor. As you can also see in the example.
We believe that this is an Oracle bug. Also in 12.1 we had to deal with
similar things.

Regards
Ahmed

Am Di., 29. Jan. 2019 um 15:00 Uhr schrieb Andy Sayer <andysayer@xxxxxxxxx>:

It works for me in the pl/sql on 12.2.0.1 with JAN2019 patch set.

Your notes on the no_merge helping suggest that this is a bug caused by
some rewrite.

Difference in behaviour between pl/sql and sql might be due to the
implicit bind variable being used in the pl/sql, try using binds in your
sql to see if the problem occurs.

Hope that helps,
Andy


On Tue, 29 Jan 2019 at 13:29, Ahmed <gherrami@xxxxxxxxx> wrote:

I first built the workaround with no_merge, so I can do at least a
regression test.
The regression took about an hour. (should take about 25 minutes .In
regression, we have only a few data. But at least no crashes or any
Ora-00600 errors.).
Looks like the upgrading will make us headache.

If the regression is over, I'll start a performance test  see which SQL
become slower.

Your suggestion sounds interesting concerning the performance.

Thanks

Am Di., 29. Jan. 2019 um 14:04 Uhr schrieb kathy duret <
katpopins21@xxxxxxxxx>:

Didn't read the thread and someone may have suggested this already but
we have turned off the adaptive tuning feature in 12.1 .

You might try turning it off in a lower environment and see how things
perform afterwards.

I think Oracle is "retooling" this feature in the future.  We are
skipping 12.2 and going to 12.3 due to some bugs.

Good Luck


Kathy Duret



On Tuesday, January 29, 2019, 4:29:04 AM CST, Ahmed <gherrami@xxxxxxxxx>
wrote:


A colleague came up with the idea to use a no_merge hint. Then
everything works again. Very strange.

    CURSOR get_fks(par_target_table user_tables.table_name%TYPE)
    IS
      WITH
      user_constr AS
      (
        SELECT /*+ no_merge */  *
         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
    ;

Regards
Ahmed Fikri

Am Di., 29. Jan. 2019 um 10:33 Uhr schrieb Ahmed <gherrami@xxxxxxxxx>:

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

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

 I get AS Output:
Table Name PARENT

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@xxxxxxxxxxxxxxxxxx>:


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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Ahmed <gherrami@xxxxxxxxx>
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


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



Other related posts: