OraFAQ Forum: SQL & PL/SQL » RETURNING CLAUSE

Home » SQL & PL/SQL » SQL & PL/SQL » RETURNING CLAUSE

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

RETURNING CLAUSE [message #149425]

Thu, 01 December 2005 14:06

pa1sas3
Messages: 29
Registered: June 2005

Junior Member

I get the following error when I do this:

DECLARE
pp number;
INSERT INTO Tab1_uv1 (
TO_ID,
SRCE_DESC,
STD_DESC,
TYP_CD
)
VALUES (
Tab1_s1.NEXTVAL,'111PAVAN11','PAVAN','PA1'
) returning to_id INTO pp;

dbms_output.put_line(pp);
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line(SQLCODE);
dbms_output.put_line(SQLERRM);
END;

-22816
ORA-22816: unsupported feature with RETURNING clause

Note:
Tab1_uv1 is a view on Tab1 table. There is an instead of trigger defined for insert on that table.

Can I still use the returning clause???? or should I increment the sequence, store it in the variable pp and then insert into the view(conventional way). I want to use the returning clause if it is possible. I am using oracle 9.2.0.4.

Regards,
Pavan

Report message to a moderator

Re: RETURNING CLAUSE [message #149465 is a reply to message #149425]

Thu, 01 December 2005 22:56

kiran
Messages: 503
Registered: July 2000

Senior Member

Quote:

DECLARE
pp number;
INSERT INTO Tab1_uv1 (
TO_ID,
SRCE_DESC,
STD_DESC,
TYP_CD
)
VALUES (
Tab1_s1.NEXTVAL,'111PAVAN11','PAVAN','PA1'
) returning to_id INTO pp;

Where is the BEGIN statement.keep after "pp number" and try.

If you still get any error , post that information.

--Kiran.

Report message to a moderator

Re: RETURNING CLAUSE [message #149485 is a reply to message #149465]

Fri, 02 December 2005 00:42

Frank
Messages: 7901
Registered: March 2000

Senior Member

The OP is right:

SQL> create table mytab
  2  ( id number
  3  , col1 varchar2(10)
  4  )
  5  /

Table created.

SQL> create or replace view myview
  2  as
  3  select id
  4  ,      col1
  5  from   mytab
  6  /

View created.

SQL> declare
  2    pp number;
  3  begin
  4    insert into myview
  5    ( id
  6    , col1
  7    ) values
  8    ( -10
  9    , 'Minus 10'
 10    ) returning id into pp;
 11  
 12    dbms_output.put_line('Returned value: '||pp);
 13  end;
 14  /
Returned value: -10

PL/SQL procedure successfully completed.

But WITH instead of trigger:

SQL> create or replace
  2  trigger myview_iot
  3  instead of insert on myview
  4  begin
  5    insert into mytab
  6    ( id
  7    , col1
  8    ) values
  9    ( -1 * :new.id
 10    , :new.col1
 11    );
 12  end;
 13  /

Trigger created.

SQL> declare
  2    pp number;
  3  begin
  4    insert into myview
  5    ( id
  6    , col1
  7    ) values
  8    ( -10
  9    , 'Minus 10'
 10    ) returning id into pp;
 11  
 12    dbms_output.put_line('Returned value: '||pp);
 13  end;
 14  /
declare
*
ERROR at line 1:
ORA-22816: unsupported feature with RETURNING clause
ORA-06512: at line 4

And again the docs come to the rescue:

Quote:

Restrictions

The following restrictions apply to the RETURNING clause:

*

The expr is restricted as follows:
o

Each expr must be a simple expression or a single-set aggregate function expression. You cannot combine simple expressions and single-set aggregate function expressions in the same returning_clause.
o

Single-set aggregate function expressions cannot include the DISTINCT keyword.
*

You cannot specify the returning_clause for a multitable insert.
*

You cannot use this clause with parallel DML or with remote objects.
*

You cannot retrieve LONG types with this clause.
*

You cannot specify this clause for a view on which an INSTEAD OF trigger has been defined.

hth

Report message to a moderator

Previous Topic:	substr, instr related sql
Next Topic:	ORA-27101 : SHARED MEMORY RELAM DOES NOT EXIST

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jan 14 04:25:12 CST 2026