troubleshooting Question

PLS-00103 error on execute immediate to create trigger from a procedure.

Avatar of E43509
E43509Flag for United States of America asked on
Oracle Database
5 Comments1 Solution3541 ViewsLast Modified:
Oracle 10 g
-- I got a funky error trying to create the trigger within the package
    -- the code ran but the trigger was invalid
    -- with an error of
    --      PLS-00103  Encountered the symbol '''' when expected one fo the following:
    --      begin function package pragma procedure subtype use
    --      <an identifier><a double-quoted delimited-identifier>form
    --      current cursor
    -- the error says it is on the DECLARE line
    -- touching any line and recompiling, the error goes away
    -- not sure why
An alter trigger compile externally won't compile it also
Here is the code:
CREATE OR REPLACE
PROCEDURE ceg_iface_cr8trig_proc (table_name IN VARCHAR2
                                , in_sysid IN VARCHAR2 DEFAULT 'EXTSYS2'
                                , in_action IN VARCHAR2 DEFAULT 'Add'
                                , in_sender_field IN VARCHAR2 DEFAULT 'sendersysid'
                                )
AS
    v_create_trigger    VARCHAR2(4000);
    CRLF                VARCHAR2(2) := CHR(13)||CHR(10);
    v_trigger_name      VARCHAR2(100);
BEGIN
    v_trigger_name   := 'ceg_' || lower(table_name) || '_q_b4trig';
    v_create_trigger := 'CREATE OR REPLACE TRIGGER ' || v_trigger_name || ' ' || CRLF;
    v_create_trigger := v_create_trigger || 'BEFORE INSERT ON ' || lower(table_name) || ' ' || CRLF;
    v_create_trigger := v_create_trigger || 'REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW ' || CRLF;
    v_create_trigger := v_create_trigger || 'DECLARE ' || CRLF;
    v_create_trigger := v_create_trigger || '    myTransID   NUMBER; ' || CRLF;
    v_create_trigger := v_create_trigger || 'BEGIN' || CRLF;
    v_create_trigger := v_create_trigger || '    --  Handle inbound transactions '  || CRLF;    
    v_create_trigger := v_create_trigger || '    IF :NEW.TransID IS NULL AND upper(:NEW.' || in_sender_field || ') = ''' || upper(in_sysid)  || ''' THEN' || CRLF;
    v_create_trigger := v_create_trigger || '        SELECT maxifacetransseq.NEXTVAL ' || CRLF;
    v_create_trigger := v_create_trigger || '        INTO myTransID ' || CRLF;
    v_create_trigger := v_create_trigger || '        FROM dual; ' || CRLF;
    v_create_trigger := v_create_trigger || CRLF;
    v_create_trigger := v_create_trigger || '        :NEW.transID:= myTransID;-- set the transaction ID' || CRLF;
    v_create_trigger := v_create_trigger || CRLF;
    v_create_trigger := v_create_trigger || '        INSERT INTO mxin_inter_trans(extsysname,ifacename,transid,action)' || CRLF;
    v_create_trigger := v_create_trigger || '        VALUES (''' ||upper(in_sysid) || ''',''' || upper(table_name) || ''', myTransID ,''' || in_action || ''');' || CRLF;
    v_create_trigger := v_create_trigger || CRLF;
    v_create_trigger := v_create_trigger || '        IF:NEW.transseq IS NULL THEN -- no seq set' || CRLF;
    v_create_trigger := v_create_trigger || '            :NEW.transseq:= 1;' || CRLF;
    v_create_trigger := v_create_trigger || '        END IF;' || CRLF;
    v_create_trigger := v_create_trigger || '    END IF;' || CRLF;
    v_create_trigger := v_create_trigger || CRLF;          
    v_create_trigger := v_create_trigger || 'END;' || CRLF;

    -- if you spool the output you can just grab the code and run it separately
    DBMS_OUTPUT.put_line(v_create_trigger);

   --
        execute IMMEDIATE v_create_trigger;
END;
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros