Re: a strange behavior in Oracle 12.2.0.1 by nested PLSQL cursor
From: Ahmed <gherrami_at_gmail.com>
Date: Tue, 29 Jan 2019 17:19:24 +0100
Message-ID: <CANkb5P2R0Fg95HtGAzUPBJGNxFWi3kx_LzbN40At9_tjZbmpZg_at_mail.gmail.com>
END LOOP;
END;
--using dynamic sql + bind variable
DECLARE
END LOOP;
END; Am Di., 29. Jan. 2019 um 15:12 Uhr schrieb Ahmed <gherrami_at_gmail.com>:
Date: Tue, 29 Jan 2019 17:19:24 +0100
Message-ID: <CANkb5P2R0Fg95HtGAzUPBJGNxFWi3kx_LzbN40At9_tjZbmpZg_at_mail.gmail.com>
Hi Andy,
I tested two variants of the block. Once with ref cursor and bind variable
and once without explicit cursor. So dynamic code using bind variable and
insert bulk collect.
The result is always the same (works everywhere but not on my 12.2).
With the no_merge hint works everywhere.
The no_merge workaround is sure not a reasonable solution. I have completely rewritten the block. I'm not a fan of such nested loops anyway.
See the two PL / SQL block bellow.
Thanks and Regards
--using ref cursor
DECLARE
- table that has Foreignkey pointing on it v_tbl_name VARCHAR2(30) := 'PARENT'; v_tbl_child VARCHAR2(30); v_cons_name VARCHAR2(30); CURSOR get_massdata_tableinfo IS SELECT v_tbl_name table_name FROM dual
;
get_fks_as_refcursr sys_refcursor;
v_stmt VARCHAR2(4000) := '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
OPEN get_fks_as_refcursr FOR v_stmt USING crec.table_name ;
LOOP
FETCH get_fks_as_refcursr INTO v_cons_name,v_tbl_child;
EXIT WHEN get_fks_as_refcursr%NOTFOUND;
dbms_output.put_line('ALTER TABLE ' || v_tbl_child||
' DISABLE CONSTRAINT ' || v_cons_name);
END LOOP;
CLOSE get_fks_as_refcursr ;
END LOOP;
END;
--using dynamic sql + bind variable
DECLARE
- table that has Foreignkey pointing on it v_tbl_name VARCHAR2(30) := 'PARENT'; v_tbl_child sys.odcivarchar2list := sys.odcivarchar2list(); v_cons_name sys.odcivarchar2list := sys.odcivarchar2list();
CURSOR get_massdata_tableinfo
IS
SELECT v_tbl_name table_name FROM dual
;
v_stmt VARCHAR2(4000) := '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
EXECUTE IMMEDIATE v_stmt BULK COLLECT INTO v_cons_name,v_tbl_child
USING crec.table_name;
FOR i IN 1..v_cons_name.count LOOP
dbms_output.put_line('ALTER TABLE ' || v_tbl_child(i)||
' DISABLE CONSTRAINT ' || v_cons_name(i));
END LOOP;
END LOOP;
END; Am Di., 29. Jan. 2019 um 15:12 Uhr schrieb Ahmed <gherrami_at_gmail.com>:
> 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
Received on Tue Jan 29 2019 - 17:19:24 CET