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

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