Dynamically Trim Column Values

Question

Here's a question for you all, is there any way to make the PL/SQL code below work by dynamically replacing the column_name in the cursor.column_name syntax? I know I'm confusing the pl/sql engine, but I'm not entirely sure on how to fix this...

Currently I get the error below, but I think the actual issue is that the pl/sql engine doesn't know how to interpret TRIM (e_rec.v_temp_column_name) :

"[Error] PLS-00302 (133: 26): PLS-00302: component 'V_TEMP_COLUMN_NAME' must be declared"

Parameter: x_person_rec IN OUT xxsome_table%ROWTYPE

v_temp_column_name   dba_tab_columns.column_name%TYPE;
...
BEGIN
      FOR e_rec IN (SELECT * FROM xxsome_table WHERE ..)
      LOOP
         --LOG (3, 'Loading/Sanitizing Header Record');

         FOR col IN (SELECT column_name
                       FROM dba_tab_columns
                      WHERE table_name = UPPER ('xxsome_table'))
         LOOP
            --LOG (3, 'Sanitizing Column Name: ' || col.column_name);
            v_temp_column_name := col.column_name;
            x_person_rec.v_temp_column_name := TRIM (e_rec.v_temp_column_name);
         END LOOP;
      END LOOP; 

...

I've tried doing this (which results in different error): x_person_rec.col.column_name := TRIM (e_rec.col.column_name);

score 2 · Answer 1 · edited May 23 '17 at 12:11

No, you can't and you are indeed confusing the PL/SQL engine. The problem is that v_temp_column_name is a character, so TRIM (e_rec.v_temp_column_name) is evaluated as TRIM (e_rec.'v_temp_column_name'), which doesn't make any sense.

The best things to do, if trailing whitespace is a problem, is to ensure that all data being put into your database is trimmed by your application/ETL processes at the time. If you can't do this then use a trigger to ensure that it happens inside the database. If it's a really bad problem you can even enforce check constraints to stop it from ever happening.

Now, to sort of answer your question, there's no need to do anything dynamically here. Your cursor may be implicit but it's not been dynamically generated. You know every column in the table so be a little less lazy and type them all out.

FOR e_rec IN (SELECT trim(col1) as col1
                   , trim(col2) as col2
                FROM xxsome_table WHERE ...

If you can't fix your data (or if it's not broken!) then this is easily the simplest way to do it.

To actually answer your question, you can dynamically build your SELECT statement using the same techniques you're using here...

declare
   l_cols varchar2(4000);
   l_curs sys_refcursor;
begin

   select wm_concat('trim(' || column_name || ')')
     into l_cols
     from user_tab_columns
    where table_name = 'XXSOME_TABLE'
          ;

    open l_curs for
     ' select ' || l_cols || ' from xxsome_table where ...';

    loop
      ...
    end loop;

end;
/

As you're on 10g you can't use the excellent LISTAGG(), but there are plenty of other string aggregation techniques. Please note that if the resulting string is greater than 4,000 bytes, you'll have to loop rather than generating the column list in a single SQL statement.

P.S., if these columns are CHAR(n) then you'll have to trim them every time you select. It might be worth changing them to VARCHAR2(n)

thanks for the response, I think you said what I needed to hear, in which I'll handle trimming the data during the ETL process. Your suggestion to dynamically build the SELECT is actually a good way around this problem, so I appreciate the extra effort! — Roberto Navarro, May 19 '14 at 20:32
No problem @Roberto; it's a lot of hassle when setting something up to trim everything, but it's a lot easier in the longer run as you only need to do it once rather than every time... — Ben, May 19 '14 at 20:35

Dynamically Trim Column Values

1 Answers1