وبلاگ مسعود حاجی حسن پور

راهکار های عملی اوراکل

راهکار های عملی اوراکل

این وبلاگ جهت به اشتراک گذاری تجربیات و راهکار های عملی در زمینه پایگاه داده اوراکل می باشد.

طبقه بندی موضوعی

۱ مطلب با کلمه‌ی کلیدی «Procedure» ثبت شده است

پیدا کردن کارکتر غیر عددی در یک ستون

data type


سلام دوستان امروز با یک مشکل جالب مواجه شدم که در این جا میخواهم با شما به اشتراک بگذارم. مشکل از این جا شروع شد که یکی از دوستان تماس گرفتند و گفتند که یک سری گزارش ها تولید نشده اند. پس از بررسی مشخص شد که یکی از procedure ها با مشکل رو به رو شده و error زیر را برمیگرداند.

ERROR at line 1:
ORA-01722: invalid number

بعد از بررسی مشخص شد که در column  مقادیر عددی ذخیره شده است، که data type آن از نوع varchar می باشد. اما در جدول حدود ۳۰۰۰۰۰۰۰۰ رکورد اطلاعات وجود داشت که واقعا پیدا کردن داده مشکل دار را سحت مینمود.

حالا با من همراه باشید که با یک شبیه سازی نشان دهم چگونه چنین مشکلی به وجود میاید و چگونه اطلاعات مشکل دار را پیدا کنیم.


خوب اول یک جدول ایجاد میکنیم و مقداری داده در آن قرار میدهیم:

create table mytest (c1 number primary key, c2 varchar (20));

Table created.

insert into mytest values (1, '20000000');

1 row created.

insert into mytest values (1, '300000000');
insert into mytest values (1, '300000000')
*
ERROR at line 1:
ORA-00001: unique constraint (HR.SYS_C0016539) violated

insert into mytest values (2, '300000000');

1 row created.

insert into mytest values (3, '300000000+');

1 row created.

select * from mytest;

        C1 C2
---------- --------------------
         1 20000000
         2 300000000
         3 300000000+

خوب حالا یک عملیات ریاضی مثل جمع انجام میدهیم و تا مقدار کل را به دست آوریم:

select sum(c2) from mytest;
select sum(c2) from mytest
           *
ERROR at line 1:
ORA-01722: invalid number

همانگونه که مشاهده نمودید به درستی با مشکل مواجه شدیم چون مقدار غیر عددی در column وجود داد. پس باید داده خراب را پیدا کرد و آن را تصحیح نمود تا کار به درستی انجام گردد.


با دستور زیر میتوان رکوردی که داده غیر عددی دارد مشخص نمود:

select *
from mytest
where regexp_like(c2, '[^0-9]+');

        C1 C2
---------- --------------------
         3 300000000+

خوب دوستان چند نکته در مورد طراحی قابل ذکر است:

  1. در صورتی که واقعا نیاز شما فقط عدد هست از نوع داده ای غیر عددی مثل varchar یا varchar2 ... برای ذخیره اعداد استفاده ننمایید، چون برای انجام عملیات های ریاضی هر چند بسیار ناچیز implicitly type conversion overhead خواهید داشت. همچنین احتمال بروز چنین مشکلاتی نیز افزایش می یابد.
  2. دوستان برنامه نویس در که صورتی ناگزیر به استفاده از داده غیر عددی برای ذخیره عداد هستید، حتما بررسی کنید که کارکتر غیر عددی در ستونی که قرار است بر روی آن اعمال ریاضی انجام گردد قرار ندهند (حتما در برنامه جلوی آن گرفته شود) تا در ادامه با چنین مشکلاتی رو به رو نشوید.

دوستان عزیز امیدوارم مطلب کاربردی بوده باشد.

شاد و پیروز باشید.