+ Reply to Thread
Page 1 of 4 1 2 3 ... LastLast
Results 1 to 10 of 36

PLS-00103: Encountered the symbol "" when expecting one of the following

  1. PLS-00103: Encountered the symbol "" when expecting one of the following

    I have written a script whose intention is to create a a series of audit
    triggers, one on each table in the schema beginning 'AD_' (in my
    organisation that is an admin or static data table). The script runs OK
    and it creates the triggers but they are all invalid and a recompile
    produces, in all cases, the error:

    TRIGGER TEST.AUDITTRIGGER_AD_ELEMENTS
    On line: 1
    PLS-00103: Encountered the symbol "" when expecting one of the
    following: begin function package pragma procedure subtype type use
    form

    However, if I copy the code that has been created and run it in a SQL+
    worksheet, it runs OK and produces a valid usable trigger.

    Here is the trigger code that is produced by the script foloowed by the
    the script code itself.

    I have now spent 2 days trying to track this one down and desperation is
    setting in!

    TIA

    Paul BJ

    CREATE OR REPLACE TRIGGER test.AUDITTRIGGER_AD_ELEMENTS
    AFTER DELETE OR INSERT OR UPDATE
    ON AD_ELEMENTS
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE v_appname varchar(50);
    v_tablename varchar(50);
    v_valuebefore varchar(3000);
    v_valueafter varchar(3000);
    v_userid varchar(50);
    v_connectionid varchar(50);

    BEGIN
    v_tablename := 'AD_ELEMENTS';

    SELECT osuser, username, program
    into v_userid, v_connectionid, v_appname
    FROM v_$session
    WHERE audsid = USERENV('SESSIONID');

    IF INSERTING THEN
    v_valueafter:= :OLD.ELEMENT || '|' ||:OLD.SOURCECODE || '|'
    ||:OLD.DESCRIPTION || '|' ||:OLD.SOURCETABLENAME || '|'
    ||:OLD.SOURCEFUNDFIELD || '|' ||:OLD.LOCALTABLENAME || '|' ;
    INSERT INTO LOG_AUDIT (APPNAME, tableName, action, valueAfter, userID,
    CONNECTIONID, TIMESTAMPE)
    VALUES (v_appname, v_tablename, 'I', v_valueafter, v_userID,
    v_connectionid, SYSDATE);

    ELSIF DELETING THEN
    v_valuebefore:= :NEW.ELEMENT || '|' ||:NEW.SOURCECODE || '|'
    ||:NEW.DESCRIPTION || '|' ||:NEW.SOURCETABLENAME || '|'
    ||:NEW.SOURCEFUNDFIELD || '|' ||:NEW.LOCALTABLENAME || '|' ;
    INSERT INTO LOG_AUDIT (APPNAME, tableName, action, valueBefore, userID,
    connectionID, timestampe)
    VALUES (v_appname, v_tablename,'D', v_valuebefore, v_userID,
    v_connectionid, SYSDATE);

    ELSIF UPDATING THEN
    v_valuebefore:= :NEW.ELEMENT || '|' ||:NEW.SOURCECODE || '|'
    ||:NEW.DESCRIPTION || '|' ||:NEW.SOURCETABLENAME || '|'
    ||:NEW.SOURCEFUNDFIELD || '|' ||:NEW.LOCALTABLENAME || '|' ;
    v_valueafter:= :OLD.ELEMENT || '|' ||:OLD.SOURCECODE || '|'
    ||:OLD.DESCRIPTION || '|' ||:OLD.SOURCETABLENAME || '|'
    ||:OLD.SOURCEFUNDFIELD || '|' ||:OLD.LOCALTABLENAME || '|' ;
    INSERT INTO LOG_AUDIT VALUES (v_appname, v_tablename,'U', v_valuebefore,
    v_valueafter, v_userid, v_connectionid, SYSDATE);
    END IF;
    END;


    DECLARE

    cursor_name INTEGER;
    ret INTEGER;
    scriptstring varchar(4000);
    tablename varchAr2(30);
    colname varchar2(30);
    valbeforestring varchar2(1000);
    valafterstring varchar2(1000);
    v_errorstring varchar2(512);
    v_errcode varchar2(50);

    --cursor for table names
    CURSOR table_curs IS select TABLE_NAME from USER_TABLES
    where TABLE_NAME like 'AD_%';

    --cursor to collect the column names for each table
    CURSOR col_curs (p_tablename VARCHAR2) IS select column_name from
    user_tab_cols
    where table_name = p_tablename;

    BEGIN

    -- loop through the the tables and create the script (outer loop)
    FOR CurTable IN Table_curs LOOP
    tablename := curtable.table_name;

    --put the starting few chars into each string; also clears the
    previous contents
    valbeforestring := 'v_valuebefore:= ';
    valafterstring := 'v_valueafter:= ';

    --loop through all the table names in the recordset
    FOR curCol IN col_curs(tablename) LOOP

    ---concatenate the strings containing the column names each column
    name separated from the next by a pipe char
    colname := curCol.column_name;
    valbeforestring := valbeforestring || ':NEW.' || colname || ' ||
    ''|'' ||';
    valafterstring := valafterstring || ':OLD.' || colname || ' ||
    ''|'' ||';
    END LOOP;
    valbeforestring := SUBSTR(valbeforestring, 1, LENGTH(valbeforestring)
    - 2) || ';';
    valafterstring := SUBSTR(valafterstring, 1, LENGTH(valafterstring) -
    2) || ';';

    scriptstring := 'CREATE OR REPLACE TRIGGER test.AUDITTRIGGER_' ||
    tablename || CHR(13);
    scriptstring := scriptstring || 'AFTER DELETE OR INSERT OR UPDATE' ||
    chr(13) || 'ON ' || tablename || CHR(13);
    scriptstring := scriptstring || 'REFERENCING NEW AS NEW OLD AS OLD' ||
    CHR(13) || 'FOR EACH ROW' || CHR(13);
    scriptstring := scriptstring || 'DECLARE ';
    scriptstring := scriptstring || 'v_appname varchar(50);'|| CHR(13)||
    'v_tablename varchar(50);'|| CHR(13);
    scriptstring := scriptstring || 'v_valuebefore varchar(3000);'||
    CHR(13)|| 'v_valueafter varchar(3000);'|| CHR(13);
    scriptstring := scriptstring || 'v_userid varchar(50);'|| CHR(13)||
    'v_connectionid varchar(50);'|| CHR(13);
    scriptstring := scriptstring || CHR(13) ||'BEGIN'|| CHR(13);
    scriptstring := scriptstring || 'v_tablename := ' || chr(39)
    ||tablename || chr(39) ||';'|| CHR(13) || CHR(13);
    scriptstring := scriptstring || 'SELECT osuser, username, program '||
    CHR(13);
    scriptstring := scriptstring || 'into v_userid, v_connectionid,
    v_appname '|| CHR(13);
    scriptstring := scriptstring || 'FROM v_$session '|| CHR(13);
    scriptstring := scriptstring || 'WHERE audsid =
    USERENV(''SESSIONID''); '|| CHR(13) || CHR(13);
    scriptstring := scriptstring || 'IF INSERTING THEN '|| CHR(13);
    scriptstring := scriptstring || ' ' || valafterstring || ' ' || CHR(13);
    scriptstring := scriptstring || 'INSERT INTO LOG_AUDIT (APPNAME,
    tableName, action, valueAfter, userID, CONNECTIONID, TIMESTAMPE) '||
    CHR(13);
    scriptstring := scriptstring || 'VALUES (v_appname, v_tablename,
    ''I'', v_valueafter, v_userID, v_connectionid, SYSDATE);'|| CHR(13) ||
    CHR(13);
    scriptstring := scriptstring || 'ELSIF DELETING THEN '|| CHR(13);
    scriptstring := scriptstring || ' ' ||valbeforestring || ' '|| CHR(13);
    scriptstring := scriptstring || 'INSERT INTO LOG_AUDIT (APPNAME,
    tableName, action, valueBefore, userID, connectionID, timestampe) '||
    CHR(13);
    scriptstring := scriptstring || 'VALUES (v_appname, v_tablename,''D'',
    v_valuebefore, v_userID, v_connectionid, SYSDATE); '|| CHR(13)|| CHR(13);
    scriptstring := scriptstring || 'ELSIF UPDATING THEN ' || CHR(13);
    scriptstring := scriptstring || ' ' || valbeforestring || ' '|| CHR(13);
    scriptstring := scriptstring || ' ' || valafterstring || ' '|| CHR(13);
    scriptstring := scriptstring || 'INSERT INTO LOG_AUDIT VALUES
    (v_appname, v_tablename,''U'', v_valuebefore, v_valueafter, v_userid,
    v_connectionid, SYSDATE);'|| CHR(13);
    scriptstring := scriptstring || 'END IF;' ||chr(13) || 'END;';

    BEGIN
    cursor_name := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(cursor_name, scriptstring, 1);
    ret := DBMS_SQL.EXECUTE(cursor_name);
    DBMS_SQL.CLOSE_CURSOR(cursor_name);

    EXCEPTION
    WHEN OTHERS THEN
    v_errorstring := SQLERRM;
    v_errcode := SQLCODE;
    DBMS_OUTPUT.PUT_LINE('Error ' || v_errcode || ' Desc: ' ||
    v_errorstring);

    END;

    END LOOP;

    END;
    /
    SHOW ERRORS;
    /










  2. Re: PLS-00103: Encountered the symbol "" when expecting one of thefollowing

    Sybrand Bakker wrote:


    > IIRC the keyword DECLARE is redundant in trigger bodies, as it is in
    > procedure bodies.
    > --
    > Sybrand Bakker, Senior Oracle DBA


    SQL*Plus: Release 10.1.0.3.0 - Production on Thu Feb 24 18:24:40 2005

    Copyright (c) 1982, 2004, Oracle. All rights reserved.

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options

    SQL> drop table t purge;

    Table dropped.

    SQL> CREATE TABLE t (testcol VARCHAR2(20));

    Table created.

    SQL> CREATE OR REPLACE TRIGGER statement_level
    2 BEFORE UPDATE
    3 ON t
    4
    5 DECLARE
    6
    7 x VARCHAR2(1);
    8
    9 BEGIN
    10 x := 'A';
    11 END statement_level;
    12 /

    Trigger created.

    SQL> CREATE OR REPLACE TRIGGER statement_level
    2 BEFORE UPDATE
    3 ON t
    4
    5 --DECLARE
    6
    7 x VARCHAR2(1);
    8
    9 BEGIN
    10 x := 'A';
    11 END statement_level;
    12 /
    x VARCHAR2(1);
    *
    ERROR at line 7:
    ORA-04079: invalid trigger specification

    Redundant? Not at all. By the same token:

    SQL> CREATE OR REPLACE PROCEDURE a IS
    2
    3 x VARCHAR2(1);
    4
    5 BEGIN
    6 x := 'A';
    7 END;
    8 /

    Procedure created.

    SQL> CREATE OR REPLACE PROCEDURE a IS
    2
    3 DECLARE
    4
    5 x VARCHAR2(1);
    6
    7 BEGIN
    8 x := 'A';
    9 END;
    10 /

    Warning: Procedure created with compilation errors.

    SQL> show err
    Errors for PROCEDURE A:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    3/1 PLS-00103: Encountered the symbol "DECLARE" when expecting one
    of the following:

    is also invalid.
    --
    Daniel A. Morgan
    University of Washington
    damorgan@x.washington.edu
    (replace 'x' with 'u' to respond)

  3. Re: PLS-00103: Encountered the symbol "" when expecting one of thefollowing

    Sybrand Bakker wrote:


    > IIRC the keyword DECLARE is redundant in trigger bodies, as it is in
    > procedure bodies.
    > --
    > Sybrand Bakker, Senior Oracle DBA


    SQL*Plus: Release 10.1.0.3.0 - Production on Thu Feb 24 18:24:40 2005

    Copyright (c) 1982, 2004, Oracle. All rights reserved.

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options

    SQL> drop table t purge;

    Table dropped.

    SQL> CREATE TABLE t (testcol VARCHAR2(20));

    Table created.

    SQL> CREATE OR REPLACE TRIGGER statement_level
    2 BEFORE UPDATE
    3 ON t
    4
    5 DECLARE
    6
    7 x VARCHAR2(1);
    8
    9 BEGIN
    10 x := 'A';
    11 END statement_level;
    12 /

    Trigger created.

    SQL> CREATE OR REPLACE TRIGGER statement_level
    2 BEFORE UPDATE
    3 ON t
    4
    5 --DECLARE
    6
    7 x VARCHAR2(1);
    8
    9 BEGIN
    10 x := 'A';
    11 END statement_level;
    12 /
    x VARCHAR2(1);
    *
    ERROR at line 7:
    ORA-04079: invalid trigger specification

    Redundant? Not at all. By the same token:

    SQL> CREATE OR REPLACE PROCEDURE a IS
    2
    3 x VARCHAR2(1);
    4
    5 BEGIN
    6 x := 'A';
    7 END;
    8 /

    Procedure created.

    SQL> CREATE OR REPLACE PROCEDURE a IS
    2
    3 DECLARE
    4
    5 x VARCHAR2(1);
    6
    7 BEGIN
    8 x := 'A';
    9 END;
    10 /

    Warning: Procedure created with compilation errors.

    SQL> show err
    Errors for PROCEDURE A:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    3/1 PLS-00103: Encountered the symbol "DECLARE" when expecting one
    of the following:

    is also invalid.
    --
    Daniel A. Morgan
    University of Washington
    damorgan@x.washington.edu
    (replace 'x' with 'u' to respond)

+ Reply to Thread
Page 1 of 4 1 2 3 ... LastLast