need a sql query to wrap text coming in differnt colums to one column

Question

i am running the following query

SELECT ora_database_name AS SERVER_NAME,'CNTODSSTG' AS DB_NAME,p.owner,p.object_name,s.text 
FROM all_procedures p,all_source s 
WHERE p.owner<>'SYS' AND p.owner<>'SYSTEM' AND s.TYPE='PROCEDURE'
AND p.object_name=s.name
GROUP BY p.owner,p.object_name,s.text

s.text is the coding inside the procedure. But it is coming in different lines I need to get it in one column Let me know how to modify above query to get required result

Answer 1

Replace the new lines characters with spaces

REGEXP_REPLACE(s_text, '[[:space:]]+', ' ') s_oneline_text

My idea there is that we deal with CR, LF, and spaces at beginning or end of line, replacing with a single space. This will also replace multiple space characters with a single space.

Answer 2

SELECT ora_database_name AS SERVER_NAME,
       'CNTODSSTG' AS DB_NAME,p.owner,p.object_name,
       dbms_metadata.get_ddl(p.object_type,p.object_name,p.owner)
FROM all_procedures p 
WHERE p.owner<>'SYS' AND p.owner<>'SYSTEM' AND s.TYPE='PROCEDURE'