--
--      Shows the text of an SQL statement if its sql_id is known.
--
set     verify off

--  Old Version: Could not cope with statement longer the 4K...
--
--      select listagg(sql_text, '') within group (order by piece)
--      from   v$sqltext_with_newlines
--      where  sql_id = '&1';

declare
  l    varchar2(4000);
  c    char(1);
begin

  for s in (select sql_text
              from v$sqltext_with_newlines
             where sql_id = '&1'
             order by piece)     loop

    for i in 1 .. length(s.sql_text) loop

        c := substr(s.sql_text, i, 1);

        if    c = chr(10) then

              dbms_output.put_line(l);
              l := '';

        elsif length(l) > 3999 then
        --    Adjust for width of terminal here.

              dbms_output.put_line(l);
              l := c;

        else

              l := l || c;

        end if;

    end loop;
  end loop;

  dbms_output.put_line(l);

end;
/