Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle DB 19c: AVG function and wrong results. Already known BUG, or something else?

S-MaxAug 5 2020 — edited Aug 6 2020

Hi all,

after upgrade from 12c to 19c I have problems with select statements including AVG function.

Without any regularity it brings 0 or correct value, and it is dependent on the place in the select clause and/or selected columns!

If you would like to test this case I have attached 2 files with create table, insert rows, create view and select statements I use.

On the 11g and 12g all select statements are workíng fine!

Anyone any ideas?

Comments

Christian Neumueller-Oracle
Answer

Hi Tim,

the parser for supporting objects which splits a clob into runnable statements is rather simplistic. A "/" on a single line ends the statement. For example, you could use the multi line code pattern of the APEX export files:

c_pkg_spec_script constant t_max_vc2 := apex_string.join(apex_t_varchar2 (
    'CREATE OR REPLACE PACKAGE #PKG_NAME#',
    'AS',
    '  #FUNC_PROC#',
    'END #PKG_NAME#;',
    '/' ));

Since this is about code generation, I sometimes also write templates like this to get consistent alignment and have the template stand out:

c_pkg_spec_script constant t_max_vc2 := regexp_replace(q'[
  % CREATE OR REPLACE PACKAGE #PKG_NAME#
  % AS
  %   #FUNC_PROC#
  % END #PKG_NAME#;
  % /
  ]',
  '^\s+% ', null, 1, 0, 'm' );

Regards,

Christian

Marked as Answer by Tim Halbach · Sep 27 2020
Tim Halbach

Hi Christian,

Thank you so much for that, that's exactly what I was looking for.

1 - 2

Post Details

Added on Aug 5 2020
#general-database-discussions
11 comments
1,781 views