×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

stored procedures with an implicit cursor

stored procedures with an implicit cursor

stored procedures with an implicit cursor

(OP)
my procedure is created but comes back with the following error when I go to execute it

CREATE OR REPLACE PROCEDURE POSTING_INTEREST
IS
T_NUM NUMBER(10,0);
ACCT_NUM NUMBER(10,0);
T_TYPE CHAR(1);
T_DATE DATE;
T_AMT NUMBER(12,2);
T_DESC CHAR;
NO_INTEREST EXCEPTION;
INTEREST_UPDATED EXCEPTION;
ANNUAL_INTEREST_RATE NUMBER(7,4);
CLOSING_DAY NUMBER(2);
BEGIN
IF BALANCE > 0 THEN
IF CLOSING_DAY = SYSDATE THEN
SELECT
TRANS_NUMBER,acctNO,'4',SYSDATE,
(BALANCE * (ANNUAL_INTEREST_RATE/1200)), 'INTEREST POSTED' INTO
T_NUM,ACCT_NUM,T_TYPE,t_DATE, T_AMT,T_DESC
FROM TRANSACTION, ACCOUNT
WHERE (LAST_CLOSING_DATE IS NULL) OR
(CLOSING_DAY = TO_CHAR(SYSDATE, 'DD') AND
LAST_CLOSING_DATE < SYSDATE);
ELSIF TOO_MANY_ROWS THEN
Insert into TRANSACTION (TRANS_NUMBER,acct_number,trans_type,trans_date,trans_amt,trans_desc)
VALUES
(T_NUM,ACCT_NUM,T_TYPE,T_DATE, T_AMT, T_DESC);
--
UPDATE ACCOUNT
SET LAST_CLOSING_DATE = SYSDATE
WHERE CLOSING_DAY = TO_CHAR(SYSDATE, 'DD') AND
LAST_CLOSING_DATE < SYSDATE;
DBMS_OUTPUT.PUT_LINE('UPDATE TO LAST_CLOSING_DATE TOOK
PLACE'¦¦ SYSDATE);
UPDATE ACCOUNT
SET BALANCE = BALANCE * (ANNUAL_INTEREST_RATE/1200) + BALANCE
WHERE CLOSING_DAY = TO_CHAR(SYSDATE, 'DD') AND
LAST_CLOSING_DATE < SYSDATE;
DBMS_OUTPUT.PUT_LINE('UPDATE TO BALANCE OF ACCOUNT'¦¦ SYSDATE);
ELSIF NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA MEETS THE CRITERIA TO GET
INTEREST');
--RAISE NO_INTEREST;
ELSIF BALANCE = (BALANCE * (ANNUAL_INTEREST_RATE/1200) + BALANCE) THEN
RAISE INTEREST_UPDATED;
END IF;
COMMIT;
EXCEPTION
WHEN INTEREST_UPDATED THEN
DBMS_OUTPUT.PUT_LINE('INTEREST ALREADY POSTED'¦¦ ANNUAL_INTEREST_RATE);
--EXCEPTION
--WHEN NO_INTEREST THEN ;
END;
--SELECT * FROM USER_ERRORS WHERE NAME = 'POSTING_INTEREST';


POSTING_INTEREST PROCEDURE 1 48 1
PLS-00103: Encountered the symbol

RE: stored procedures with an implicit cursor

SharonAnne -
It looks like your error messages are being truncated, so it will be rather difficult specific errors until you can see the entire message.

However, I noticed a couple of things that will give you problems.

First, you have a variable called BALANCE, but it is not defined anywhere. This will give you undefined symbol errors.

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.

RE: stored procedures with an implicit cursor

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

RE: stored procedures with an implicit cursor

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 ?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close