Q

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.

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

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close