Hows it going,
I am getting better at PL/SQL, however, I cannot figure out what I am doing wrong in the below statement.
Basically, I would like to achieve the following:
1. Run Query.
2. based on query resultsin cursor, fill v_tab and v_col in order to create dynamic update statements.
3. Dynamic UPDATE statement written....
4. EXECUTE dynamic statements as they are created
5. Output UPDAT statement lines that show as being executed...Commit for each statement.
6. Close...
However, with the below code, I am getting an "Ora-00911 Invalid Character Error".
If I take out the EXECUTE IMMEDIAT Statement from the below block, the UPDATE Statements are written syntactically correct.
CREATE OR REPLACE PROCEDURE p_tab_null_out
IS
sql_q VARCHAR2(1000);
v_tab_name column_xref.table_name%TYPE;
v_col_name column_xref.column_name%TYPE;
CURSOR zero_list
IS
SELECT s.table_name, s.column_name
FROM apps.column_xref s
WHERE comments LIKE '%Nulled Out%';
BEGIN
OPEN zero_list;
LOOP
FETCH zero_list INTO v_tab_name, v_col_name;
EXIT WHEN zero_list%NOTFOUND;
sql_q := 'UPDATE '
|| 'system.' || v_tab_name|| ' '
|| 'SET ' || v_col_name || ' = '
|| '''NULL''' || ';';
EXECUTE IMMEDIATE sql_q USING v_tab_name, v_col_name;
dbms_output.put_line(sql_q);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20998, 'An error has occurred : ' ||SQLCODE|| ' ' ||SQLERRM);
END p_tab_null_out;