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