set serveroutput on 
CREATE OR REPLACE PROCEDURE get_sql (sess_id NUMBER) IS

CURSOR sql_code IS
select st.sql_text,st.piece
from v$sqltext st, v$session s
where st.address=s.sql_address and
s.sid = sess_id
order by st.piece;

vSql VARCHAR2(4000);
vCount NUMBER  := 0;

BEGIN

DBMS_OUTPUT.ENABLE(1000000);

DBMS_OUTPUT.PUT_LINE(chr(10)||'SQL statement for SID: '||sess_id);
DBMS_OUTPUT.PUT_LINE(RPAD('-',64,'-'));

select COUNT(st.piece) INTO vCount 
from v$sqltext st, v$session s
where st.address=s.sql_address and
s.sid = sess_id
order by st.piece;

IF vCount = 0 THEN
   DBMS_OUTPUT.PUT_LINE('No statement.');
END IF;

FOR rec IN sql_code LOOP
   DBMS_OUTPUT.PUT_LINE(rec.sql_text);
END LOOP;

END;
/
show errors