Error compiling in 10g due to trigger > sequence.nextval

[martindsouza]

From @martindsouza on July 26, 2013 13:37

Submitted by Patrick Jolliffe: http://www.talkapex.com/2013/06/logger-200-released.html - it does not currently compile on 10g database (cannot use SEQUENCE.NEXTVAL directly in trigger, have to insert into a local variable).

Update: .nextval is only available in PL/SQL starting with 11gR2 onwards: http://docs.oracle.com/cd/B28359_01/server.111/b28279/chapter1.htm#FEATURENO07450

Copied from original issue: tmuth#26

[martindsouza]

Suggested fix:

• M logger_configure
  • Add a new variable l_lt_11_2 varchar2(50) := 'FALSE';
  • Modify it

if l_version < 11 then
  l_lt_11_2 := 'TRUE';
else
  l_lt_11_2 := 'FALSE';
end if;

...

l_variables := l_variables||'LT_11_2:'||l_lt_11_2||',';

• M bi_logger_logs (in logger_logs)

-- Oracle 10 and below require a SELECT INTO for nextval;
$IF $$LT_11_2 $THEN
  select logger_logs_seq.nextval into :new.id from dual;
$ELSE
  :new.id := logger_logs_seq.nextval;
$END

[martindsouza]

Due to conditional compilation this will raise an error during initial compiling on 10g databases. The installer will running logger_configure at the end which will resolve the trigger error and it then works fine in 10g (i.e. ignore the initial error).

Final results can be verified using dbms_preprocessor.print_post_processed_source: http://www.oracle-base.com/articles/10g/conditional-compilation-10gr2.php on how to test.

[martindsouza]

Fixed as part of #31. Just doing a SELECT INTO for all versions of DB.