tag:blogger.com,1999:blog-7777850065728349315.post4976512638549126735..comments2023-07-25T14:53:08.584-04:00Comments on I wonder...: Oracle 12c OFFSET...FETCH.. bug?Michael "Misha" Rosenblumhttp://www.blogger.com/profile/05568300284769247997noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-7777850065728349315.post-89908688850655881942013-08-29T04:10:24.427-04:002013-08-29T04:10:24.427-04:00Looks then some casting should fix things:
declar...Looks then some casting should fix things:<br /><br />declare<br /> type table_tt is table of all_objects%rowtype;<br /> v_tt table_tt;<br /><br /> v_limit_nr number:=10;<br /> v_counter_nr number:=0;<br /> begin<br /> select *<br /> bulk collect into v_tt<br /> from all_objects<br /> offset cast(v_counter_nr*v_limit_nr as number) rows<br /> fetch next cast(v_limit_nr as number) rows only; -- variable<br /> end;<br />/<br /><br />Connor McDonaldhttps://www.blogger.com/profile/06246356571229889735noreply@blogger.comtag:blogger.com,1999:blog-7777850065728349315.post-37343428942407703092013-08-27T11:10:34.005-04:002013-08-27T11:10:34.005-04:00By the way, an interesting finding by my colleague...By the way, an interesting finding by my colleague Grigoriy Novikov. The following two cases would work just fine:<br /><br /> ... <br /> 12 fetch next to_number(v_limit_nr) rows only; <br /> 13 end;<br /> 14 /<br />PL/SQL procedure successfully completed.<br />SQL><br /><br /> ... <br /> 12 fetch next v_limit_nr+0 rows only; <br /> 13 end;<br /> 14 /<br />PL/SQL procedure successfully completed.<br />SQL><br /><br />It seems to be that there is an issue with the datatypes.Michael "Misha" Rosenblumhttps://www.blogger.com/profile/05568300284769247997noreply@blogger.comtag:blogger.com,1999:blog-7777850065728349315.post-63387055230897132942013-08-27T09:52:17.170-04:002013-08-27T09:52:17.170-04:00Ok, so it is not only me... Logged SR (#3-77275729...Ok, so it is not only me... Logged SR (#3-7727572911). Will keep everybody posted!Michael "Misha" Rosenblumhttps://www.blogger.com/profile/05568300284769247997noreply@blogger.comtag:blogger.com,1999:blog-7777850065728349315.post-57427964115986366002013-08-27T01:17:11.261-04:002013-08-27T01:17:11.261-04:00SQL> declare
2 type table_tt is table of...SQL> declare<br /> 2 type table_tt is table of all_objects%rowtype;<br /> 3 v_tt table_tt;<br /> 4<br /> 5 v_limit_nr number:=10;<br /> 6 v_counter_nr number:=0;<br /> 7 begin<br /> 8 select *<br /> 9 bulk collect into v_tt<br /> 10 from all_objects<br /> 11 offset v_counter_nr*v_limit_nr rows<br /> 12 fetch next v_limit_nr rows only; -- variable<br /> 13 end;<br /> 14 /<br /><br /> from all_objects<br /> *<br />ERROR at line 10:<br />ORA-03113: end-of-file on communication channel<br />Process ID: 21616<br />Session ID: 357 Serial number: 7<br /><br />[oracle]$ uname -rvms<br />Linux 2.6.39-100.7.1.el6uek.x86_64 #1 SMP Wed May 16 04:04:37 EDT 2012 x86_64<br /><br />Connor McDonaldhttps://www.blogger.com/profile/06246356571229889735noreply@blogger.com