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

From: Andy Sayer <andysayer_at_gmail.com>
Date: Tue, 29 Jan 2019 14:00:31 +0000
Message-ID: <CACj1VR5Req9-LpCbC8SnvkyA2hs72JMyMO88uG78xRVkdq=PpQ_at_mail.gmail.com>



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_at_gmail.com> 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_at_yahoo.com>:
>
>> 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_at_gmail.com>
>> 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_at_gmail.com>:
>>
>> 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_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
Received on Tue Jan 29 2019 - 15:00:31 CET

Original text of this message