CODEWITHSUNDEEP
plsql
Sam
Sam

Reputation: 25

PL/SQL - Inserting data using Exception

I have the following code which is not executing correctly. I have data stored in date_tmp (varchar) that includes dates and nondates. I want to move the dates in that column to date_run (date) and data that is not a date, will be moved to a comments (varchar) column. When I run the following code, the entire set of data gets moved to comments. It runs fine when I edit out the insert statement and just run the dbms_outputline line. What might I be doing incorrectly? 

DECLARE

  CURSOR getrow IS
  SELECT a.id, a.date_tmp
  FROM mycolumn a 
  WHERE a.id < 1300;

  v_date date;  

BEGIN
  FOR i in getrow LOOP
    BEGIN
      v_date := to_date(i.date_tmp, 'mm/dd/yy');  
      INSERT INTO mycolumn a(a.date_run)
      VALUES(i.date_tmp);

    EXCEPTION
      WHEN OTHERS THEN       
        --dbms_output.put_line(i.date_tmp);
        update mycolumn a
        SET a.comments = i.date_tmp
        where a.id = i.id;
    END;
  END LOOP; 
END;

Upvotes: 0

Views: 67

Answers (3)

Aitor
Aitor

Reputation: 3429

@hol solution is the best approach for me. Avoid always you can loops and procedures if you can do it with simple SQL statments, your code will be more faster. Also, if you have always have a data fixed format , you can ride of the PL/SQL function is_a_date function and do it everything with SQL... but the code gets a little uglier with something like this: 

update mycolumn 
   set date_run = to_date(date_tmp,'dd/mm/yy')
     where substr(date_tmp,1,2) between '1' and '31'
      and substr(date_tmp,4,2) between '1' and '12'
      and substr(date_tmp,7,2) between '00' and '99';

If you need more speed in your query or you have a huge amount of data in date_tmp, as function is_a_date is deterministic (always returns the same value given the same values for X, Y,), you can create an index for it:

create index mycol_idx on mycolumn(is_a_date(date_tmp));

And when you use the function, Oracle will use your index, like in those selects:

SELECT a.id, a.date_tmp
  FROM mycolumn a 
  WHERE a.id < 1300
and is_a_date(a.date_tmp) is not null;

SELECT a.id, a.date_tmp
  FROM mycolumn a 
  WHERE a.id < 1300
  and (is_a_date(a.date_tmp) is not null and is_a_date(a.date_tmp)>sysdate-5);

Upvotes: 1

TommCatt
TommCatt

Reputation: 5636

You have an insert where it looks like you need an update, like you have in the exception handler. So just change it to:

  v_date := to_date(i.date_tmp, 'mm/dd/yy');  
  update  mycolumn
      set date_run = v_date
  where   id = i.id;

or you could shorten it to:

  update  mycolumn
      set date_run = to_date(i.date_tmp, 'mm/dd/yy')
  where   id = i.id;

Upvotes: 1

hol
hol

Reputation: 8423

You try to insert varchar i.date_tmp into a date field. Instead insert v_date.

...
INSERT INTO mycolumn a (a.date_run)
VALUES(v_date);
...

But actually your requirement is a move. That calls for an update actually. So I think what you really want to do is:

...
update mycolumn a
SET a.date_run = v_date
where a.id = i.id    
...

And actually you could have a function that checks if you have a valid date or not and then you might be able to handle the whole task using a simple update statement. 

create or replace function is_a_date(i_date varchar2, i_pattern varchar2) 
return date 
is
begin
  return to_date(i_date, i_pattern);
exception
  when others return null;
end is_a_date;

With that function you could write two update statements

update mycolumn 
   set date_run = to_date(date_tmp,'dd/mm/yy')
 where is_a_date(date_tmp, 'dd/mm/yy') is not null;

update mycolumn 
   set comment = date_tmp
 where is_a_date(date_tmp, 'dd/mm/yy') is null;

I designed the function in a way that you could use it in various ways as it returns you a date or null but no exception if the varchar does not conform to the date pattern.

Upvotes: 2

Related Questions