Returning parameters in Oracle SQL insert statements

Question

I have an insert statement which I need to return a parameter from in order to use it later. I'm getting a strange error which I can't seem to find a solution for. Can anyone shed any light on it?

set define off;
variable videoID number;

insert into VM_VIDEO (VIDEO_ID, ...)
  values (SEQ_VMMIGVIDEO_ID.NEXTVAL, ...)
  returning VIDEO_ID into :videoID;

insert into IMAGES (IMAGE_ID, ...)
  values (SEQ_IMAGE_ID.NEXTVAL, ...);

update VM_VIDEO
  set THUMB_IMAGE_ID = SEQ_IMAGE_ID.CURRVAL
  where VIDEO_ID = :videoID;

Error:

Error starting at line 4 in command:
insert into VM_VIDEO (VIDEO_ID, ...) values (SEQ_VMMIGVIDEO_ID.NEXTVAL, ...) returning VIDEO_ID into :videoID
Error report:
SQL Error: Not all return parameters registered

A SQL*Plus variable may not work here. Try a PL/SQL variable (DECLARE). — Thilo
– Thilo, Commented Aug 24, 2012 at 8:30

Xophmeister · Accepted Answer · 2012-08-24 08:33:03Z

7

You need to put it into a PL/SQL block:

declare
  videoID number;
begin
  insert into VM_VIDEO (VIDEO_ID, ...)
    values (SEQ_VMMIGVIDEO_ID.NEXTVAL, ...)
    returning VIDEO_ID into videoID;

  insert into IMAGES (IMAGE_ID, ...)
    values (SEQ_IMAGE_ID.NEXTVAL, ...);

  update VM_VIDEO
    set THUMB_IMAGE_ID = SEQ_IMAGE_ID.CURRVAL
    where VIDEO_ID = videoID;
end;

answered Aug 24, 2012 at 8:33

Xophmeister

9,2495 gold badges53 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Returning parameters in Oracle SQL insert statements

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related