Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Updating Data in %ROWTYPE ? (Oracle 10g R2)
Updating Data in %ROWTYPE ? [message #614455] Wed, 21 May 2014 14:31 Go to next message
mp352
Messages: 2
Registered: May 2014
Junior Member
Hi,

is it possible updating the data in a cursor? I have a cursor with rowtype in this cursor i am searching for misstakes in spelling. the next thing would be the correction of such a misstake but how could i correct the data in the cursor? In EXCEPTION Block i would like to correct the data of specific field of the cursor.

i hope the german term of the cursor field are not irratating.

create or replace PROCEDURE name_correction
  is CURSOR p_import_I is SELECT * FROM PERSONAL_IMP_I;
  rec_import_I p_import_I%ROWTYPE;
  name_error EXCEPTION;
  BEGIN
    open p_import_I;
      loop
        fetch p_import_I into rec_import_I;
          exit when p_import_I%NOTFOUND;
           BEGIN
            IF REGEXP_LIKE(rec_import_I.vorname, '[0-9]') OR REGEXP_LIKE(rec_import_I.nachname, '[0-9]')
             then RAISE name_error;
            END IF;
            Exception when name_error 
             then insert into log_table values (rec_import_I.id, 'Name enthält Zahlen', 'Namensprüfung', 'Personal_IMP_I',        
                   systimestamp);
                  commit;
  --Here i would like to correct the date of the field rec_import_I.vorname the the regexp_replace function, but how
           END;
           insert into personen_val_I values
                                  (rec_import_I.id, rec_import_I.vorname, rec_import_I.nachname,
                                  rec_import_I.email, rec_import_I.status, rec_import_I.aktiv,
                                  rec_import_I.vorgesetzter, rec_import_I.fachbereichs_id,
                                  rec_import_I.telefonnummer, rec_import_I.Lokation_ID);                  
    end loop;
   close p_import_I;
 END;


Manuel
Re: Updating Data in %ROWTYPE ? [message #614456 is a reply to message #614455] Wed, 21 May 2014 15:21 Go to previous messageGo to next message
mp352
Messages: 2
Registered: May 2014
Junior Member
hi,

the solution for my problem is:

rec_import_I.vorname := regexp_replace(rec_import_I.vorname, '[0-9]');
Re: Updating Data in %ROWTYPE ? [message #614470 is a reply to message #614456] Thu, 22 May 2014 01:00 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK, so you fixed it (I'm glad you did).

However, just one remark regarding your cursor usage: consider switching to cursor FOR loops which are WAY easier to handle. Rewritten, your code would look like this:
create or replace procedure name_correction
is 
  name_error exception;
begin
  for cur_r in (select * from personal_imp_i) loop
    begin
      if regexp_like(cur_r.vorname, '[0-9]') or ... then
         raisename_error;
      end if;
    exception when name_error then ...
    end;
    insert into personen_val_I values ...
  end loop;
end;

See? No opening, fetching, taking care about exiting the loop ... much easier to read and maintain.
Previous Topic: WANT TO INSERT JSON VALUE IN A TABLE
Next Topic: Check Duplicate Values
Goto Forum:
  


Current Time: Thu Mar 28 08:03:03 CDT 2024