Skip to content

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

Closed
martindsouza opened this Issue Jul 26, 2013 · 4 comments

1 participant

@martindsouza
Collaborator

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

@martindsouza martindsouza was assigned Jul 26, 2013
@martindsouza
Collaborator

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
Collaborator

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 martindsouza reopened this Jul 29, 2013
@martindsouza
Collaborator

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

@martindsouza
Collaborator

This issue was moved to OraOpenSource/Logger#26

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.