stored procedures with an implicit cursor

[SharonAnne]

my procedure is created but comes back with the following error when I go to execute it<br>

<br>

CREATE OR REPLACE PROCEDURE POSTING_INTEREST<br>

IS <br>

T_NUM NUMBER(10,0);<br>

ACCT_NUM NUMBER(10,0);<br>

T_TYPE CHAR(1);<br>

T_DATE DATE;<br>

T_AMT NUMBER(12,2);<br>

T_DESC CHAR;<br>

NO_INTEREST EXCEPTION;<br>

INTEREST_UPDATED EXCEPTION;<br>

ANNUAL_INTEREST_RATE NUMBER(7,4);<br>

CLOSING_DAY NUMBER(2);<br>

BEGIN<br>

IF BALANCE > 0 THEN<br>

IF CLOSING_DAY = SYSDATE THEN<br>

SELECT <br>

TRANS_NUMBER,acctNO,'4',SYSDATE,<br>

(BALANCE * (ANNUAL_INTEREST_RATE/1200)), 'INTEREST POSTED' INTO<br>

T_NUM,ACCT_NUM,T_TYPE,t_DATE, T_AMT,T_DESC<br>

FROM TRANSACTION, ACCOUNT<br>

WHERE (LAST_CLOSING_DATE IS NULL) OR<br>

(CLOSING_DAY = TO_CHAR(SYSDATE, 'DD') AND <br>

LAST_CLOSING_DATE < SYSDATE);<br>

ELSIF TOO_MANY_ROWS THEN<br>

Insert into TRANSACTION (TRANS_NUMBER,acct_number,trans_type,trans_date,trans_amt,trans_desc) <br>

VALUES<br>

(T_NUM,ACCT_NUM,T_TYPE,T_DATE, T_AMT, T_DESC); <br>

--<br>

UPDATE ACCOUNT<br>

SET LAST_CLOSING_DATE = SYSDATE<br>

WHERE CLOSING_DAY = TO_CHAR(SYSDATE, 'DD') AND<br>

LAST_CLOSING_DATE < SYSDATE;<br>

DBMS_OUTPUT.PUT_LINE('UPDATE TO LAST_CLOSING_DATE TOOK <br>

PLACE'¦¦ SYSDATE);<br>

UPDATE ACCOUNT<br>

SET BALANCE = BALANCE * (ANNUAL_INTEREST_RATE/1200) + BALANCE<br>

WHERE CLOSING_DAY = TO_CHAR(SYSDATE, 'DD') AND<br>

LAST_CLOSING_DATE < SYSDATE;<br>

DBMS_OUTPUT.PUT_LINE('UPDATE TO BALANCE OF ACCOUNT'¦¦ SYSDATE);<br>

ELSIF NO_DATA_FOUND THEN<br>

DBMS_OUTPUT.PUT_LINE('NO DATA MEETS THE CRITERIA TO GET <br>

INTEREST');<br>

--RAISE NO_INTEREST;<br>

ELSIF BALANCE = (BALANCE * (ANNUAL_INTEREST_RATE/1200) + BALANCE) THEN<br>

RAISE INTEREST_UPDATED;<br>

END IF;<br>

COMMIT;<br>

EXCEPTION<br>

WHEN INTEREST_UPDATED THEN<br>

DBMS_OUTPUT.PUT_LINE('INTEREST ALREADY POSTED'¦¦ ANNUAL_INTEREST_RATE);<br>

--EXCEPTION <br>

--WHEN NO_INTEREST THEN ;<br>

END; <br>

--SELECT * FROM USER_ERRORS WHERE NAME = 'POSTING_INTEREST';<br>

<br>

<br>

POSTING_INTEREST PROCEDURE 1 48 1<br>

PLS-00103: Encountered the symbol

 

[carp]

SharonAnne -<br>
It looks like your error messages are being truncated, so it will be rather difficult specific errors until you can see the entire message.<br>
<br>
However, I noticed a couple of things that will give you problems.<br>
<br>
First, you have a variable called BALANCE, but it is not defined anywhere. This will give you undefined symbol errors.<br>
<br>
Also, you are using predefined exceptions (TOO_MANY_ROWS and NO_DATA_FOUND) in your procedural code. These can only be referenced in your EXCEPTION section. <br>

 

[carp]

You also probably want to initialize ANNUAL_INTEREST_RATE with some value. Right now, it is unitialized when you try to use it, so <br>
(BALANCE * (ANNUAL_INTEREST_RATE/1200)<br>
will very likely yield a null value.

 

[yaffle]

Your variable CLOSING_DAY is also uninitialised, and its comparison with SYSDATE may anyway cause errors due to mismatched data types - perhaps TO_NUMBER(TO_CHAR(SYSDATE, 'DD')) instead ?