Hello,
I have some non printable characters (not white spaces, the trim works in the procedure) that I would like to remove from a table for many fields; not just one. Can anyone assist me to get this procedure to execute to update both fields. The procedure compiles but when I execute it I receive error messages. The standard update works but when I add the REGEXP_REPLACE (column_name, '[^[:cntrl:] -~is where the issue is I believe. Thanks for reading.
[code]
CREATE TABLE TBL_TEST
(
PK_TEST_ID NUMBER(12) NOT NULL,
LAST_NAME VARCHAR2(30 BYTE),
FIRST_NAME VARCHAR2(30 BYTE)
)
CREATE OR REPLACE PROCEDURE p_RegExpChars_TEST (
pa_table_name all_tab_columns.table_name%TYPE)--,
--pa_owner all_tab_columns.owner%TYPE)
IS
v_sql_text VARCHAR2 (4000);
BEGIN
FOR rec
IN (SELECT column_name
FROM all_tab_columns
WHERE --owner = UPPER (pa_owner)
--AND
table_name = UPPER (pa_table_name)
AND data_type = 'VARCHAR2')
LOOP
v_sql_text :=
'UPDATE '
--|| pa_owner
--|| '.'
|| pa_table_name
|| ' SET '
|| rec.COLUMN_NAME
|| ' = REPLACE('
|| rec.COLUMN_NAME
|| '[^[:cntrl:] -~]'
|| ')';
EXECUTE IMMEDIATE (v_sql_text);
COMMIT;
END LOOP;
END p_RegExpChars_TEST;
/
--When I execute I receive error messages.
execute p_RegExpChars_TEST('TBL_TEST');
--Error Messages
ORA-00907: missing right parenthesis
ORA-06512: at "P_REGEXPCHARS_TEST", line 28
ORA-06512: at line 1
--Test Data
INSERT INTO TBL_TEST values(20040968,'HARRIS ','TINICA ');
INSERT INTO TBL_TEST values(20040969,'PAYNE ','DIANNA ');
INSERT INTO TBL_TEST values(20040970,'CARTER ','DARLENE ');
INSERT INTO TBL_TEST values(20040971,'JARVIS ','MICHELLE ');
INSERT INTO TBL_TEST values(20040972,'HAYES ','SAMANTHA ');
--This works but I want to execute the procedure for every column in table that is VARCHAR2
--not changing the field names and executing every time.
UPDATE TBL_TEST
SET LAST_NAME = REGEXP_REPLACE (LAST_NAME, '[^[:cntrl:] -~]')
WHERE REGEXP_LIKE (LAST_NAME, '[^[:cntrl:] -~]')
[/code]