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

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