plsql - PL/SQL garbage value cleaning -

-

a table contains millions of records in there 12 critical columns contain garbage values. not records in these 12 columns garbage. there records fine while others garbage.

so want write pl/sql script clean these garbage values without using update statement 12 times. have code written doesn't seem work cause. suggestions modify code or change code entirely welcome. suppose dynamic sql used here don't have knowledge of ever , i'm short on time.

set serveroutput on declare count_total number := 0;           number :=0; ch          varchar2(10); ch2         varchar2(10);  cursor cursor_sim_b  select a1, a2, a3, a4, a5, a6, b1, b2, b3, b4, b5, b6, -- identify trim(translate(a1,'0123456789',' ')) a_1,             -- garbage values trim(translate(a2,'0123456789',' ')) a_2, trim(translate(a3,'0123456789',' ')) a_3, trim(translate(a4,'0123456789',' ')) a_4, trim(translate(a5,'0123456789',' ')) a_5, trim(translate(a6,'0123456789',' ')) a_6, trim(translate(b1,'0123456789',' ')) b_1, trim(translate(b2,'0123456789',' ')) b_2, trim(translate(b3,'0123456789',' ')) b_3, trim(translate(b4,'0123456789',' ')) b_4, trim(translate(b5,'0123456789',' ')) b_5, trim(translate(b6,'0123456789',' ')) b_6  temp_clean;  sim_b_rec cursor_sim_b%rowtype;  begin dbms_output.put_line('outside cursor ');  sim_b_rec in cursor_sim_b loop dbms_output.put_line('outside  loop ');  in 1..6 loop ch := 'a_' ||i; dbms_output.put_line('inside loop '||ch);   if sim_b_rec.ch not null   -- error in passing 'ch' cursor                          -- error says 'ch' not declared begin  execute immediate 'update temp_clean set sim_b_rec.ch = null';  exception      when  others      rollback; end;  dbms_output.put_line('a'||i||'<' || sim_b_rec.ch || '>'); end if;  ch2 := 'b_' ||i; if sim_b_rec.ch2 not null begin execute immediate 'update temp_clean set sim_b_rec.ch2 = null';  exception      when  others      rollback; end;  dbms_output.put_line('b'||i||'<'|| sim_b_rec.ch2 || '>'); end if;  end loop; end loop;    commit; end; /

this not answer. perhaps question op.

i guess way of finding garbage not work. translating every value null. assuming garbage contains non-numeric.

see below: 

select translate('567','0123456789',null), length(translate('567','0123456789',null) )  dual;  -- output: null null  select translate('abc56','0123456789',null) , length(translate('abc56','0123456789',null) ) dual;  -- output: null null  select translate('%&12ab','0123456789',null), length(translate('%&12ab','0123456789',null) )  dual;    -- output: null null

just make sure translated null have added length check.

provide sample valid values , garbage values further


Comments

Post a Comment

Popular posts from this blog

html - Firefox flex bug applied to buttons? -

-
i'm trying apply flex button on firefox i'm having problem on chrome doesn't happens. this css: .button { display: inline-flex; align-items: center; } as can see in fiddle, "caret" word moved new line, happens if element <button> tag, <div> works expected. http://codepen.io/anon/pen/xblmgx is firefox bug or there css i'm missing? nevermind, looks "mr firefox" doesn't idea... <button> not implementable (by browsers) in pure css, bit of black box, perspective of css. means don't react in same way e.g. <div> would. this isn't specific flexbox -- e.g. don't render scrollbars if put "overflow:scroll" on button, , don't render table if put "display:table" on it. stepping further, isn't specific <button> . consider <fieldset> , <table> have special rendering behavior: data:text/html,<fieldset style="d
Read more

html - Missing border-right in select on Firefox -

-
Image
when put select div width , padding-left, options have no border on right. more, happend if select have width 118px , parent have 15px of padding left! have idea, going on? my code: <!doctype html> <html> <head lang="en"> </head> <body> <div style="width: 1000px; padding-left: 15px;"> <select style="width: 118px;"> <option>a</option> <option>b</option> <option>c</option> </select> </div> </body> </html> and result: here plunker: click it happend on firefox on windows. tested on 40.0.2 version -webkit-box-sizing: border-box; -moz-box-sizing: border-box; -ms-box-sizing: border-box; box-sizing: border-box; source: https://stackoverflow.com/a/11390432/4431269 source of problem: https://bugzilla.mozilla.org/show_bug.cgi?id=924068 and use of google ! edit: (source of problem, explained) after rea
Read more

mysql - FireDac error 314 - but DLLs are in program directory -

-
i getting when trying access mysql database : [firedac][phys][mysql]-314. cannot load vendor library [libmysql.dll or libmysqlld.dll] this did not happen (unchanged) code, however, have upgraded windows 10 , had reinstall delphi xe8, system configuration matter. in order try solve problem, copied both of files c:\windows\sysytem32. when did not seem work, copied them \win32\debug, generated .exe resides. i imagine doing rather stupid, can't see what. the proper solution place driver file (eg., libmysql.dll ) in application's folder, or place installation location in fddrivers.ini file: [mysql] vendor=<folder>\libmysql.dll (recent versions of documentation seem use vendorlib instead of vendor in ini file.) see rad studio documentation topics configuring drivers (firedac) , connect mysql server (firedac) more information.
Read more