0

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;
Paul_00
  • 11
  • 3

1 Answers1

1

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.

  • While very seldom seen "string" actually is a valid pl/sql data type; it is defined in package STANDARD as "subtype string is varchar2;" See [fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=cb6ef86e0231746ccf893cdd9e2652b7) using it. – Belayer Jan 15 '21 at 05:24
  • @Belayer: wow. I have been working with PL/SQL for over 20 years and never new that ;) –  Jan 15 '21 at 06:31
  • Yea, like I said it's rare. After close to 20 years, it still amazes me that I still come across little gems like that every now and then. – Belayer Jan 15 '21 at 18:17