troubleshooting Question

Oracle Dynamic PL/SQL in a Stored Procedure with a Curor passing all columns name to update values.

Avatar of jc50967w
jc50967w asked on
DatabasesOracle Database
4 Comments3 Solutions2024 ViewsLast Modified:
Hi, gurus
I need to assign a date value 01/01/1900 to replace many invalide dates among 50 columns of an Oracle table. All columns are with date data type, and they have invalid date such as 01/01/0206 and 01/01/0200 (human typos from web front end).

I wrote a sp containing a cursor to retrieve all columns, and update values for each of columns fetched into the cursor. I couldn't figure out how to correctly code Dynamic PL/SQL since there is  " ' " inside of TO_DATE funcation. My entire colde is followings:

create or replace procedure DATE_CORRECT
  is       -- select specific column names into cursor
                   cursor emp_tab is select column_name from user_tab_columnS where table_name ='S0_LINE_REVISION' AND DATA_TYPE ='DATE';
             
              column_name varchar2(20);
              emp_rec emp_tab%rowtype;
            begin
                   open emp_tab;
               loop
                    fetch emp_tab into column_name;
                    exit when emp_tab%NOTFOUND;
                     
                                 -- update wrong date value in a  column fatched
                                Execute immediate
                                'update S0_line_Revision set' || column_name ||'= '||to_date('01/01/1900', 'mm/dd/yyyy')||
                                 'where'|| Cast(To_Char(column_name, 'yyyy')as Numeric) ||'<1753';
                                
            end loop;
            close emp_tab;
    end DATE_CORRECT;
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 3 Answers and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros