set serveroutput on
set verify off

alter session set current_schema = &1;

DECLARE
 P_SQL_ID VARCHAR2(100) := '&2';
 P_DEST_SO_NAME VARCHAR2(100) := '&3';
 P_DEST_SO_CAT VARCHAR2(100) := '&4';
 
 v_sqltext clob;
 v_sqltext1 VARCHAR2(32000);
 v_sqltext2 VARCHAR2(32000);
 v_dbid number;

 err_num          number;
 err_msg          varchar2 (255);
BEGIN
    select dbid
      into v_dbid
      from dba_hist_database_instance
     where rownum <= 1;
      
    select sql_text
      into v_sqltext
      from dba_hist_sqltext
     where dbid = v_dbid
       and sql_id = P_SQL_ID;
	
	v_sqltext1 := substr(v_sqltext,1,20000);
	v_sqltext2	:= substr(v_sqltext,20001,45000);
	
    
    execute immediate 'create public outline '||P_DEST_SO_NAME||' for category '||P_DEST_SO_CAT||' on '||v_sqltext1||v_sqltext2; 

    dbms_output.put_line('ol '||P_DEST_SO_NAME||' creada sin errores en la categoria: '||P_DEST_SO_CAT||' para la consulta: '||P_SQL_ID);
    
    commit;
EXCEPTION
 when others then
      err_num := sqlcode;
      err_msg := sqlerrm;

      rollback;
      raise_application_error(-20001, 'error general. '||err_num||'-'||err_msg);

END;
/
