CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
VisualBean
VisualBean

Reputation: 5008

Procedure select 1 and delete

I am trying to create a procedure that
1. Selects the first record.
2. Deletes the record and returns it to the caller.

I have the following code (mind you I am fairly new to PLSQL). I got it from looking at other SO questions and the oracle docs.

CREATE PROCEDURE TAKE_1_DELETE_1
AS
BEGIN
DELETE FROM my_table
where rownum = 1
  RETURNING *
  INTO v_event;
END TAKE_1_DELETE_1;

I get the following error:

[Error] PLS-00103 (7: 3): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   ; <an identifier> <a double-quoted delimited-identifier>
   current delete exists prior <a single-quoted SQL st

I have tried numerous things, but none have worked thus far.

Our Data-Warehouse people are saying "We don't know what is wrong".

Dear SO gods... what do I need to do, to get my procedure to do what I want? :)

Upvotes: 0

Views: 61

Answers (1)

Ben
Ben

Reputation: 52863

There's a couple of errors here:

  1. You haven't declared v_event so you're trying to return data into a uninstantiated variable/out parameter
  2. You can't "return" * you have to explicitly state all columns - this is the actual error you're getting

Assuming a table that looks like this

create table my_table (a number, b number);

Your procedure would look like the following:

create or replace procedure take_1_delete_1 is
   v_event my_table%rowtype;
begin

   delete from my_table
    where rownum = 1
   returning a, b
     into v_event;

end take_1_delete_1;

Or, if you wanted to use the returning value outside the procedure you can use an OUT parameter rather than declaring a local variable

create or replace procedure take_1_delete_1 (p_event out my_table%rowtype) is
begin

   delete from my_table
    where rownum = 1
   returning a, b
     into p_event;

end take_1_delete_1;

Upvotes: 2

Related Questions