It might be that you see one record in a view that generates an error somewhere but you have not clue which record might generate that error. It could then be that the next logic helps. Create this procedure:<\/p>\n\n\n\n
create or replace package mypkg_tom\nas\ncursor c is select * from tpl_v_calc_dossier;\ntype cur_tab is table of c%rowtype;\nfunction foo return mypkg_tom.cur_tab pipelined;\nend;\n<\/pre>\n\n\n\nwhich is followed by the package definition:<\/p>\n\n\n\n
create or replace package body mypkg_tom\nas\nfunction foo return mypkg_tom.cur_tab pipelined\nas\nl_data cur_tab;\nbegin\nopen c;\nfetch c bulk collect into l_data;\nclose c;\ndbms_output.put_line('omvang '||l_data.count);\nfor i in 1..l_data.count\nloop\npipe row( l_data(i) );\nif mod(i, 1000) < 1 then dbms_output.put_line('regel '||i); end if;\nif (i between 250000 and 250010) then dbms_output.put_line(i); end if;\nend loop;\nreturn ;\nend;\nend;<\/pre>\n\n\n\nIt can then be called by something like select count(*) from table( mypkg_tom.foo ); This then shows a counter at each 1000th record. You then know where the error may lie. Next step is to precisely locate the error and solve the issue.<\/p>\n\n\n\n
An alternative is to show the ids from the view:<\/p>\n\n\n\n
create or replace package mypkg_tom
as
cursor c_varchar is select id from tpl_v_calc_dossier;
type cur_varchar is table of varchar(216);
function foo_varchar return mypkg_tom.cur_varchar pipelined;
end;<\/pre>\n\n\n\nfollowed by<\/p>\n\n\n\n
create or replace package body mypkg_tom
as
function foo_varchar return mypkg_tom.cur_varchar pipelined
as
l_data cur_varchar;
opvang varchar(216);
begin
open c_varchar;
fetch c_varchar bulk collect into l_data;
close c_varchar;
dbms_output.put_line('omvang '||l_data.count);
for i in 1..l_data.count
loop
pipe row( l_data(i) );
if mod(i, 1000) < 1 then dbms_output.put_line('regel '||i); end if;
opvang := l_data(i);
if (i between 250000 and 250010) then dbms_output.put_line(opvang); end if;
end loop;
return ;
end;
end;<\/pre>\n\n\n\nwhich can then be called as select count(*) from table( mypkg_tom.foo_varchar );<\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
It might be that you see one record in a view that generates an error somewhere but you have not clue which record might generate that error. It could then be that the next logic helps. Create this procedure: create or replace package mypkg_tom as cursor c is select * from tpl_v_calc_dossier; type cur_tab is […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[],"class_list":["post-3836","post","type-post","status-publish","format-standard","hentry","category-niet-gecategoriseerd"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3836","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3836"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3836\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3836"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3836"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3836"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}