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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation