Tools for Oracle database - PL/SQL: What is wrong with the INSERT in this PL/SQL program?

Author

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

mindcooler@gmail.com

2006-04-03, 8:23 pm

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> @ 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_er
ror(
-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_er
ror(
-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_s
eq.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

Maxim Demenko

2006-04-03, 8:23 pm

mindcooler@gmail.com schrieb:
> 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> @ 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_er
ror(
> -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_er
ror(
> -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_s
eq.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
>

Probably you should put a semicolon in your script after
CREATE SEQUENCE transaction_seq starts with 100

Best regards

Maxim

Eric Lilja

2006-04-03, 8:23 pm

Maxim Demenko wrote:
> mindcooler@gmail.com schrieb:
>
> Probably you should put a semicolon in your script after
> CREATE SEQUENCE transaction_seq starts with 100

Thanks for replying, but it didn't help. Same errors as before.

>
> Best regards
>
> Maxim

/ E

Sybrand Bakker

2006-04-03, 8:23 pm

On 3 Apr 2006 13:38:51 -0700, "mindcooler@gmail.com"
<mindcooler@gmail.com> wrote:

>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> @ 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_er
ror(
> -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_er
ror(
> -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_s
eq.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

PLS-201 invariably means (and believe me : it has been posted here
over and over and over and over and over again, and STILL people DON'T
search the archives):
You either don't have access to the object, it doesn't exist, or you
have ACCESS THROUGH A ROLE (roles are ignored during PL/SQL
compilations)

Secondly: your single line comment is wrapping over multiple lines

When will people ever stop being lazy, stop assuming their problem is
unique, and start JUST SEARCHING THE ARCHIVES?
What are archives for?

--
Sybrand Bakker, Senior Oracle DBA

Maxim Demenko

2006-04-03, 8:23 pm

Eric Lilja schrieb:
> Maxim Demenko wrote:

[color=darkred]
>
> Thanks for replying, but it didn't help. Same errors as before.
>
>
> / E
>

Oh, sorry, overlooked the next error that you have - in the same
statement STARTS is wrong, should be START

Best regards

Maxim

Sponsored Links