CODEWITHSUNDEEP
oracle-databaseplsqlpls-00103
Paul_00
Paul_00

Reputation: 11

PLS-00103: Encountered Symbol DECLARE/EOF when trying to increment values with a sequence

I'm working on a procedure that will declare a variable, take the value from a procedure that increments, and inserts that value along with other parameters into a table. I thought I had it all worked out, but then I got hit with PLS-00103: Encountered symbol "DECLARE" and Encountered symbol "end-of-file". I feel like I'm so close, so any help would be majorly appreciated! Thank you!

create or replace procedure Order_Create(date_order string, cust_id char, total float, employ_id number)
is
DECLARE NewKey;
BEGIN
NewKey := order_auto_inc.nextval;
UPDATE Progressive_Keys set Order_Limit = NewKey;
insert into ORDERS VALUES (Progressive_Keys.Order_Limit, to_date(date_order, 'yyyy-mm-dd'), cust_id, total, employ_id);
commit;
END;

Upvotes: 0

Views: 294

Answers (1)

user330315
user330315

Reputation:

Remove the declare it's not needed in a stored procedures (as documented in the manual).

A variable declaration needs a data type.

As the parameter order_date is supposed to be a date, it should be declared with that type.

You can't access the column order_limit outside of a statement that uses the table progressive_keys so you need to use the variable in the insert statement as well.

It's also good coding practice to always list the target columns in an INSERT statement (note that I just invented some column names for the orders table you will have to adjust them to reflect the real names in your table)

create or replace procedure Order_Create(date_order date, cust_id varchar, total float, employ_id number)
is
  NewKey number;
BEGIN
  NewKey := order_auto_inc.nextval;
  UPDATE Progressive_Keys set Order_Limit = NewKey;
  insert into ORDERS (some_key, order_date, customer_id, total, employee_id)
  VALUES (newkey, date_order, cust_id, total, employ_id);
  commit;
END;

The UPDATE looks a bit strange as it will update all rows in the thable progressive_keys not just one row.

Upvotes: 1

Related Questions