| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> 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
<an identifier> <a double-quoted delimited-identifier> 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
--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 || '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 || '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;
/
Received on Thu Feb 24 2005 - 12:41:15 CST
 |
 |