Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Posts
    60

    Unanswered: PLS-00103 While Creating Trigger

    Hi

    when i am trying to create the following trigger i am getting PLS-00103

    <code>
    create or replace
    TRIGGER logon_audit
    AFTER LOGON ON DATABASE
    DECLARE sqlstr VARCHAR2(200) := 'alter session set "_optim_peek_user_binds"=false;';
    BEGIN

    BEGIN
    IF (USER = 'SCOTT') THEN
    execute immediate sqlstr;
    END IF;

    END logon_audit;
    <code>


    Error(12,16): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: begin case declare end exception exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    CREATE OR replace TRIGGER logon_audit
      after logon ON database
    DECLARE
        sqlstr VARCHAR2(200) := 'alter session set "_optim_peek_user_binds"=false;';
    BEGIN
        IF ( USER = 'SCOTT' ) THEN
          EXECUTE IMMEDIATE sqlstr;
        END IF;
    END logon_audit;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Not really.

    Although the ALTER SESSION statement looks OK, it is not. When used in EXECUTE IMMEDIATE, it shouldn't be terminated with a semi-colon.
    Code:
    No:
    set "_optim_peek_user_binds"=false;';
    
    Yes:
    set "_optim_peek_user_binds"=false';

  4. #4
    Join Date
    Oct 2004
    Posts
    60
    Thanks Littlefoot

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •