yes of course.
I tested it on three different 12.2.0.1 Oracle instances. I get nothings.
On 11.2.0.4 and 12.1.0.2 it works fine.
I think it is again a Oracle bug :-(
Am Di., 29. Jan. 2019 um 09:27 Uhr schrieb Stefan Knecht <
knecht.stefan@xxxxxxxxx>:
Does the 12.2 database where you see no results have serveroutput enabled?
On Mon, Jan 28, 2019 at 9:59 PM Ahmed <gherrami@xxxxxxxxx> wrote:
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
--
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net | @zztat_oracle | fb.me/zztat | zztat.net/blog/