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;
ASKER CERTIFIED SOLUTION
paquicuba

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 5 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 5 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004