Avatar of E43509
E43509Flag for United States of America

asked on 

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

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;
Oracle Database

Avatar of undefined
Last Comment
paquicuba
Avatar of E43509
E43509
Flag of United States of America image

ASKER

ASKER CERTIFIED SOLUTION
Avatar of paquicuba
paquicuba
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of E43509
E43509
Flag of United States of America image

ASKER

That solved it!
Thanks
Just an fyi
This is probably a carryover from my SqlServer/Access days.
Chr(10) returns a linefeed character
Chr(13) returns a carriage return character
The two-character string Chr(13) & Chr(10) returns a Windows newline
Avatar of paquicuba
paquicuba
Flag of United States of America image

I know, I was joking... ; )
I use MS Access, but I don't use the Chr(13) & Chr(10) combination all the time.
e.g. The message box below works in 97 with chr(10) only:

MsgBox "Some alert" & chr(10) & "Some question?" & chr(10) & chr(10) & _
              "Click Yes or No",vbQuestion+vbYesNO,"Title"


 
Avatar of paquicuba
paquicuba
Flag of United States of America image

Thanks a bunch for the points!!
Oracle Database
Oracle Database

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo