set define '^'

Rem
Rem    MODIFIED   (MM/DD/YYYY)
Rem    hfarrell   03/12/2009 - Created patch file for bug (8331010)
Rem    hfarrell   03/13/2009 - Revised solution to handle clob >32K:modified varchar 'p', 
Rem                            and set item prompt,pre_element_text, post_element_text, and item_field_template to null.
Rem    jkallman   04/10/2009 - Correct "set define"


declare
    p varchar2(32767) := null;
begin
    wwv_flow_security.g_security_group_id := 10;
    
    p := 'declare'||chr(10)||
'  l_clob clob := empty_clob;'||chr(10)||
'  v_print     varchar2(4000);'||chr(10)||
'begin'||chr(10)||
''||chr(10)||
'  select e.cdata into l_clob '||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    b.id = :P112_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)||
''||chr(10)||
'  htp.p(wwv_flow_lang.system_message(''SQL_QUERY'')||'':'');'||chr(10)||
'  htp.p(''<pre class="code_text">'');'||chr(10)||
'  wwv_flow_sw_script.print_clob(l_clob);'||chr(10)||
'  htp.p(''</pre>'');'||chr(10)||
'end;' ;

    update wwv_flow_step_items
    set    source = p,
           source_type = 'FUNCTION_BODY',
           prompt = null,
           pre_element_text = null,
           post_element_text = null,
           item_field_template = null
    where  flow_id between 4400 and 4409
    and id >= 75919211501803773
    and id < 75919211501803773 + 1; 

    commit;

end;
/  

set define '^'