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

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_at_gmail.com>:

> Does the 12.2 database where you see no results have serveroutput enabled?
>
>
> On Mon, Jan 28, 2019 at 9:59 PM Ahmed <gherrami_at_gmail.com> 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 | _at_zztat_oracle | fb.me/zztat | zztat.net/blog/
>

--
http://www.freelists.org/webpage/oracle-l