CODEWITHSUNDEEP
plsqloracle-sqldeveloper
TrickyDBA
TrickyDBA

Reputation: 37

Bulk trimming all columns in a table

I'm trying to create a small PL\SQL code to remove all leading and ending spaces in a table. I created 2 files, column_trimmer & stop. The stop.sql is nothing more than a message to say have a nice day. The column_trimmer has the main code. All I need is to grab the specified table and then grab all the columns in that table and remove leading and ending spaces. This is what I have so far..

ACCEPT column_name CHAR PROMPT 'Please specify the table name' 

     NEED CODE IN HERE TO GRAB TABLE NAME, THEN ALL COLUMNS IN IT
              AND APPLY IT TO THE CODE BELOW

UPDATE EWS_PHONE_SLX SET columnname = TRIM(columnname);
ACCEPT response CHAR PROMPT 'Task completed! Would you like to trim all columns in another table? '
SET TERM OFF

SELECT CASE LOWER('&&response.') WHEN 'yes' THEN 'column_trimmer' ELSE 'no' END AS script
FROM DUAL;

SET TERM ON

@&script

Upvotes: 0

Views: 1636

Answers (1)

DirkNM
DirkNM

Reputation: 2664

Here ist a little plsql procedure (not tested):

CREATE OR REPLACE PROCEDURE trim_all_columns(p_table IN VARCHAR)
AS
  v_sql   VARCHAR2(32767);
  v_first BOOLEAN := true;
BEGIN
  v_sql := 'UPDATE ' || p_table || ' SET ';
  FOR c1 IN (
    SELECT column_name
      FROM user_tab_columns
     WHERE table_name = p_table
       AND data_type = 'VARCHAR2'
  ) LOOP
    IF v_first = false THEN
      v_sql := v_sql || ', ';
    END IF;
    v_sql := v_sql || c1.column_name || ' = TRIM(' || c1.column_name || ')';    
    v_first := false;
  END LOOP;
  EXECUTE IMMEDIATE v_sql;
END;
/

and here an example call for a table named "MY_TABLE":

BEGIN
  trim_all_columns('MY_TABLE');
END;
/

Hope it helps.

Upvotes: 1

Related Questions