set define off

Rem
Rem    MODIFIED   (MM/DD/YYYY)
Rem    hfarrell   03/12/2009 - Created patch file for bug (8330936)


declare
    p varchar2(32767) := null;
    s varchar2(32767) := null;
begin
    wwv_flow_security.g_security_group_id := 10;
    
    p := 'declare'||chr(10)||
'    l_query  varchar2(32767);'||chr(10)||
'    l_status boolean := true;'||chr(10)||
'    l_clob   clob    := empty_clob;'||chr(10)||
'begin'||chr(10)||
'apex_collection.create_or_truncate_collection'||chr(10)||
'                    ( p_collection_name => ''INVALID_QUERIES'') ;'||chr(10)||
''||chr(10)||
'    for c1 in ( select replace(replace(replace(e.cdata,''
'', '' ''), ''''', chr(39)),chr(10),'' '') sql_query, a.file_name filename'||chr(10)||
'                from   wwv_mig_rpts a,'||chr(10)||
'                       wwv_mig_report b,'||chr(10)||
'                       wwv_mig_rpt_data c,'||chr(10)||
'                       wwv_mig_rpt_datasrc d, '||chr(10)||
'                       wwv_mig_rpt_datasrc_select e'||chr(10)||
'     '||chr(10)||
'                where  a.project_id = :MW_PROJ_ID'||chr(10)||
'                and    a.security_group_id = :flow_security_group_id'||chr(10)||
'                and    a.id = b.file_id'||chr(10)||
'                and    b.id = c.report_id'||chr(10)||
'                and    c.id = d.data_id'||chr(10)||
'                and    d.id = e.datasrc_id'||chr(10)||
'                and    a.select_for_app = ''Y'') '||chr(10)||
'    loop'||chr(10)||
'        l_query := wwv_flow_utilities.clob_to_varchar2(c1.sql_query);'||chr(10)||
'        wwv_flow_security.g_parse_as_schema_override := :P29_SCHEMA ;'||chr(10)||
'        if (wwv_mig_frm_utilities.is_valid_query(l_query) = false ) then '||chr(10)||
'            l_status := false ;'||chr(10)||
''||chr(10)||
'            dbms_lob.createtemporary( l_clob, FALSE, dbms_lob.session );'||chr(10)||
'            dbms_lob.open(l_clob,dbms_lob.lob_readwrite);'||chr(10)||
'            dbms_lob.writeappend(l_clob,length(l_query),l_query);'||chr(10)||
'            dbms_lob.close( l_clob );'||chr(10)||
''||chr(10)||
'            htmldb_collection.add_member('||chr(10)||
'                 p_collection_name => ''INVALID_QUERIES'','||chr(10)||
'                 p_c001            => c1.filename,'||chr(10)||
'                 p_clob001         => l_clob,'||chr(10)||
'                 p_c003            => ''QUERY'') ;'||chr(10)||
'        end if;'||chr(10)||
'    end loop;'||chr(10)||
'    return l_status;'||chr(10)||
'end;' ;

    update wwv_flow_step_validations 
    set    validation = p 
    where  flow_id between 4400 and 4409  
    and    flow_step_id >= 29 
    and    flow_step_id < 29 + 1
    and    id >= 247845429279760400 
    and    id < 247845429279760400 + 1;


    s:=s||'select c001 report_c001, clob001 report_c002'||chr(10)||
'  from  apex_collections '||chr(10)||
'  where collection_name = ''INVALID_QUERIES'' '||chr(10)||
'  and   (c003 = ''QUERY'' )'||chr(10)||
'  order by c001';

    update wwv_flow_page_plugs
    set    plug_source = s
    where  flow_id between 4400 and 4409
    and    page_id >= 29
    and    page_id < 29 + 1
    and    id >= 247871227528958530
    and    id < 247871227528958530 + 1;

    commit;

end;
/  

set define '^'