Ask the Expert

Trigger with stored procedure won't compile

I have a trigger that won't compile. I'm trying to call a stored procedure from within the trigger and this seems to be the problem.
LINE/COL ERROR
--------
-----------------------------------------------------------------
19/9     PLS-00103: Encountered the symbol "SEND_MESSAGE" when expecting
         one of the following:
         := . ( @ % ;
         The symbol ":=" was substituted for "SEND_MESSAGE" to continue.
Below are the errors:
21/35    PLS-00103: Encountered the symbol ";" when expecting one of the
         following:
         ( - + case mod new not null others <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> avg
         count current exists max min prior sql stddev sum variance
         execute forall merge time timestamp interval date
         <a string literal with character set specification>
         <a number> <a single-quoted SQL string> pipe
         <an alternatively-quoted string literal with character set
         specification>
         <an alternatively-q
Here is the trigger code:
DECLARE
   ROWCNT NUMBER;
   PIN NUMBER;
   SIGNUP_URL VARCHAR;
--
--
BEGIN
   SIGNUP_URL := 'http://signup.2gn.com/signup.pl';

   SELECT COUNT(*) INTO ROWCNT FROM SUBSSHORTCODE WHERE SHORT_CODE =
:N.SHORT_CODE
   AND SUBSCRIBER_PHONE= :N.SUBSCRIBER_PHONE;
   IF
rowcnt >0  THEN
   UPDATE MSGLOG SET STATUS = 'A'
   WHERE MESSAGE_ID = :N.MESSAGE_ID;

-- Send the auth notification to the
-- content provider.
   CALL SEND_MESSAGE(N.SUBSCRIBER_PHONE,
                     N.SHORT_CODE,
                     'mcinc',     ;; mcinc is a sample content provider
smsc
                     'AUTH')

ELSE
-- pin_number needs to be an integer in the sub pin table
   PIN := ROUND ( DBMS_RANDOM.VALUE(1000,9999), 0);
   INSERT INTO SUBSCRIBER_PIN (SUBSCRIBER_PHONE, PIN_NUMBER)
      VALUES (:N.SUBSCRIBER_PHONE, PIN);
   CALL SEND_MESSAGE(N.SUBSCRIBER_PHONE,
                     N.SHORT_CODE,
                     'nokia',  ;; send pin directly to the subscriber
                     'Sign up for this program at ' || SIGNUP_URL
                     || ' with pin number ' || PIN);


END IF;
END;

    Requires Free Membership to View

The problem you are having is with the "call statement." You cannot use the call statement within an anonymous PL/SQL or procedure or function. You can only use the call statement in an interactive environment such as SQL*Plus.

This was first published in October 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: