Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00933 when trying out returning clause (10g)
ORA-00933 when trying out returning clause [message #311232] Thu, 03 April 2008 16:18 Go to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
i was exploring 10g new features...the single set aggregate in
dml...

and i tried doing this
variable tot_sal number;

begin
insert into empl1 select * from empl
RETURNING sum(sal) into :tot_sal;
dbms_output.put_line('total is '|| to_char(:tot_sal,'$999,999.00'));
end;
/



what i am getting is

SQL> variable tot_sal number;
SQL> begin
  2  insert into empl1 select * from empl
  3  RETURNING sum(sal) into :tot_sal;
  4  dbms_output.put_line('total is '|| to_char(:tot_sal,'$999,999.00'));
  5  end;
  6  /
RETURNING sum(sal) into :tot_sal;
          *
ERROR at line 3:
ORA-06550: line 3, column 11:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 2, column 1:
PL/SQL: SQL Statement ignored




so i am using a group function in the insert that is causing
the issue, but the book says, its 10g feature....

Re: ORA-00933 when trying out returning clause [message #311233 is a reply to message #311232] Thu, 03 April 2008 16:27 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Since it appears you are incapable or unwilling to use the Search function on this forum, here is a free clue.

http://www.orafaq.com/node/34
Re: ORA-00933 when trying out returning clause [message #311235 is a reply to message #311232] Thu, 03 April 2008 16:36 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
thanks for the clue, but to let u know.....the matter in the
link and the book i am referring to is the same...

appreciate if anyone can explain why i was getting the error
though the version is correct
Re: ORA-00933 when trying out returning clause [message #311237 is a reply to message #311232] Thu, 03 April 2008 16:38 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
which also brings to another doubt,
using "returning" in plsql,, is it a 10g new feature again?


BEGIN
                Insert Into table(columns )
                Values(SEQ.NextVal,.....)
                returning ID Into variable ;
            EXCEPTION
                WHEN OTHERS THEN 
	            blah blah
	    END;

Re: ORA-00933 when trying out returning clause [message #311238 is a reply to message #311232] Thu, 03 April 2008 16:43 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
When all else fails, RTFM
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#SQLRF01604

Restrictions The following restrictions apply to the RETURNING clause:

    *

      The expr is restricted as follows:
          o

            For UPDATE and DELETE statements 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. 
For INSERT statements, each expr must be a simple expression. 
Aggregate functions are not supported in an INSERT statement 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.

[Updated on: Thu, 03 April 2008 16:44] by Moderator

Report message to a moderator

Re: ORA-00933 when trying out returning clause [message #311250 is a reply to message #311238] Thu, 03 April 2008 17:41 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
Registered: November 2002
Location: California, USA
Senior Member
I did a little research by searching the internet and the insert statement that the original poster tested was included in some book on Oracle 10g new features. Apparently it may have run without producing an error in some beta version, but there was some associated bug. This code has apparently been copied and pasted into various places without testing and still produces an error in 11g. This type of returning of a sum apparently only works with update and delete statements, not insert statements.

Here are a couple of relevant links. If you scroll down to the errata section for the book in the first link, it indicates, "Single-set aggregates cannot be used with an INSERT statement." In the second link, one of the authors provides some explanation.

http://www.rampant-books.com/book_2003_2_oracle10g.htm

http://dba.ipbhost.com/lofiversion/index.php/t1031.html

[Updated on: Thu, 03 April 2008 17:48]

Report message to a moderator

Re: ORA-00933 when trying out returning clause [message #311264 is a reply to message #311232] Thu, 03 April 2008 19:40 Go to previous message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
oops..thanks a lot !
Previous Topic: Query rewrite problem related to FGAC/RLS/VPD
Next Topic: Timestamp correction
Goto Forum:
  


Current Time: Fri Dec 02 14:29:37 CST 2016

Total time taken to generate the page: 0.53873 seconds