How to achieve using TRIM command on SELECT * From TABLE

oracle

I am building DWH where in source data dump is made through SELECT * query into DB. I am taking dump from the source DB, to save time (else had to write 115 select stmt using TRIM where average number of filed per table is 150) I used SELECT * FROM TABLE query to get the dump post which I am executing SED command to remove spaces from the fields.

This is taking a huge time & space as first SELECT query dumps the table and sed trims the Spaces. I have written a BJ to download the files & conversion.

There is space constraint in my server and I cannot mount extra space.

I am looking forward for inputs/tools/package/procedure on how can still use SELECT * but there are no spaces in the fields and I can avoid using sed command in UNIX.

Best Answer

If I understand you correctly, this PL/SQL will do the job for you:

DECLARE
   CURSOR c1 IS SELECT table_name FROM USER_TABLES;
   QUERY varchar(4000);
BEGIN
  FOR rec IN c1 LOOP
    SELECT 'SELECT '||RTRIM(EXTRACT(COLXML,'//COLUMN_NAME/text()'),',')||' FROM '||rec.table_name||';' COL_STRING INTO QUERY
    FROM (SELECT E.table_name,
      xmlelement("USER_TAB_COLUMNS",
      XMLAGG(xmlelement("COLUMN_NAME", case when data_type like '%CHAR%' then 'TRIM(' end ||
      column_name||
      case when data_type like '%CHAR%' then '),' else ',' end) 
    ORDER BY COLUMN_NAME)) COLXML
    FROM USER_TAB_COLUMNS E WHERE TABLE_NAME=rec.table_name
    GROUP BY E.table_name );
  DBMS_OUTPUT.PUT_LINE(QUERY);
  END LOOP;
END;
/

Make sure you set serveroutput on to get the output from DBMS_OUTPUT.

Change the query in the CURSOR to alter the table list it generates queries for.

Note: Doesn't do anything for 'CLOB'/'BLOB' columns.

Example output:

SELECT A,TRIM(B),TRIM(C) FROM BLAH;
SELECT A,B FROM EDITIONTEST;
SELECT C,D FROM EDITIONTEST2;

PL/SQL procedure successfully completed.

Another thing to mention; You're going to be limited to the 4000 character limit, but will be OK unless you have tables with hundreds of columns.

Related Question