PL/SQL: What is wrong with the INSERT in this PL/SQL program?

From: <mindcooler_at_gmail.com>
Date: 3 Apr 2006 13:38:51 -0700
Message-ID: <1144096730.882671.126970_at_v46g2000cwv.googlegroups.com>

Hello, just started learning PL/SQL (and I'm a complete novice at SQL). Consider the following script:
$ cat withdraw_or_deposit.sql
-- Insert into database by issuing:

• SQL> _at_ withdraw_or_deposit
• If compilation errors occur, view them by issuing:
• SQL> show errors
• Run procedure by issuing:
• SQL> EXECUTE withdraw_or_deposit(); SET TERMOUT OFF CREATE SEQUENCE transaction_seq starts with 100 SET TERMOUT ON

set serveroutput on;

CREATE OR REPLACE PROCEDURE
   withdraw_or_deposit(accnum IN INTEGER, amount IN INTEGER, empnum IN INTEGER)
IS

   num_acc INTEGER := 0;
   num_emp INTEGER := 0;

BEGIN
   SELECT COUNT(account_number)

      INTO num_acc
      FROM account
      WHERE accnum=account.account_number;

   IF num_acc = 0 THEN
      raise_application_error(
         -20000,
         'Error - ' || accnum || ' is an invalid account number.'
      );

   END IF;    SELECT COUNT(key)

       INTO num_emp
       FROM employee
       WHERE empnum=employee.key;

   IF num_emp = 0 THEN
      raise_application_error(
         -20000,
         'Error - there is no employee with the key ' || empnum || '.'
      );

   END IF;

• Only reached if we have a valid account number and a valid employee key. INSERT INTO transaction VALUES(transaction_seq.NEXTVAL, amount, sysdate, empnum, accnum); UPDATE account SET balance = balance + amount WHERE account.account_number = accnum; END withdraw_or_deposit; /

The compilation fails, show errors yields: SQL> show errors

Errors for PROCEDURE WITHDRAW_OR_DEPOSIT:

LINE/COL ERROR

---

---

33/4 PL/SQL: SQL Statement ignored

34/14 PLS-00201: identifier 'TRANSACTION_SEQ.NEXTVAL' must be declared

If I comment out the INSERT it compiles cleanly...so what's wrong with the INSERT? I'm guessing it has to do with sysdate...what can I do to fix this?

/ E Received on Mon Apr 03 2006 - 22:38:51 CEST