Bulk collect ve no_data_found

Hataya çok açık olan bir konuya değinmek istiyorum.Bildiğimiz gibi klasik select into ile tablodan okuduğumuz veriyi lokal değişkenimize yazıyoruz.

Select ile seçtğimiz sonuç kümesi boş gelirse no_data_found exception ı ile hatayı yakalayabiliyoruz.

Bunun paralelinde bulk_collect ile tablodan okuduğumuz veriyi lokal array imize yazmak istediğimizde ki sonuç kümesinin boş olduğunu varsayalım bu durumda oracle no_data_found hatasını fırlatmıyor ve eğer bizimde iş sürecimizde bu hatayı handle etmeye ihtiyacımız varsa farklı bir yol kullanmamız gerekiyor.Bunu da bulk collect işlemlerinde oracle ın lokalimizde yüklemeyi yapacağımız arrayimizi otomatik olarak boşaltmasından yararlanarak yani array in count metodunun sıfıra eşitliğini kullanarak yapıyoruz.

Örneklerle konuyu açıklamaya çalışayım.

drop table bulktable;
create table bulktable (col1 integer);

insert into bulktable values(1);
insert into bulktable values(2);

commit;

DECLARE
l_id PLS_INTEGER;
BEGIN

SELECT ora_rowscn INTO l_id FROM bulktable WHERE col1 = 11;
EXCEPTION
WHEN no_data_found THEN

raise_application_error(-20001, 'Hata aldik bekledigimiz gibi sorun yok');

end;


DECLARE
l_scn_arr dbms_sql.Number_Table;
BEGIN

SELECT ora_rowscn bulk collect INTO l_scn_Arr FROM bulktable where col1 is null;

EXCEPTION
WHEN no_data_found THEN

raise_application_error(-20001, 'Asla buraya girmeyecek');

end;


DECLARE
l_id_arr dbms_sql.number_table;
BEGIN

SELECT ora_rowscn BULK COLLECT
INTO l_scn_arr
FROM bulktable
WHERE col1 is NULL;

IF l_id.COUNT = 0 THEN
raise_application_error(-20001, 'Hatayi aldik istedigimiz de buydu ');
END IF;
END;

Comments

Post a Comment

Popular posts from this blog

Pyppeteer fix for BrowserError: Browser closed unexpectedly

if you are having an error related with Pyppeteer   BrowserError: Browser closed unexpectedly   is just the error you get when Chrome crashes for whatever reason. It would be nice if pyppeteer printed out the error, but it doesn't. To track things down, it's helpful to pull up the exact command that pyppeteer runs. You can do that this way: from pyppeteer.launcher import Launcher ' '.join(Launcher().cmd) run output from command line and find missing libraries and install it.
Read more

overlay filesystem and containers

Containers A  container  is a process running on the host system.  This process is started by the container runtime from a container image, and the runtime provides a set of tools to manage the container process.  Namespaces virtualize the container process’s PID, network, root, and users.  Cgroups help set resource usage limits the container process can consume on the host system, and security contexts enforce permissions the container process has on the host system. A container, as a runtime object, consumes the typical resources any running process would consume on a system: storage for the file system and any saved configuration files, CPU, memory, and networking to serve traffic to/from external clients, and other containers or devices on the system. docker run container from image and connect it with shell , install necessary networking tools docker run -d -it ubuntu:22.10 bash docker run -d -it --privileged ubuntu:22.10 bash -> if you need to write to filesystem run wit priv
Read more

How to add pagination to django comments for your model

First step is import Comment and ContentType to your view like below from django.contrib.comments.models import Comment from django.contrib.contenttypes.models import ContentType Second step is defining your model which you want to paginate its comments content_type_id= ContentType.objects.get_for_model( YOUR_MODEL_NAME ) Last get comments of specific instance of your model class with defining object_pk as filter parameter comments = Comment.objects.filter(content_type=content_type_id,object_pk=quote_id) here is complete view code from django.core.paginator import Paginator, InvalidPage, EmptyPage from django.contrib.comments.models import Comment from django.contrib.contenttypes.models import ContentType def quote_show_page(request,quote_id): quote = get_object_or_404(Quote, id=quote_id) content_type_id=ContentType.objects.get_for_model(Quote) comments = Comment.objects.filter(content_type=content_type_id,object_pk=quote_id) paginator = Paginator(comments, 5) # Show 5 comments pe
Read more