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 last published in October 2006

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close