Re: INSERT INTO...RETURNING doesn't work ( ORA-00933)

From: Lewis C <lewisc_at_excite.com>
Date: Sun, 10 Apr 2005 23:49:32 GMT
Message-ID: <1aej51del1pj5qi4dqfpsrqoaft64rj7qq@4ax.com>

On 10 Apr 2005 15:37:30 -0700, agonenil_at_gmail.com wrote:

>hi , i get the following problem :
>
>SQL> Variable tot_sal number;
>SQL> begin
> 3 INSERT INTO emp select * from emp
> 5 RETURNING sum(sal) INTO :tot_sal;
> 7 dbms_output.put_line('Total Company Payroll now : ' ||
>to_char(:tot_sal,'$9
>99,999.00'));
> 9 end;
> 11 /
>RETURNING sum(sal) INTO :tot_sal;
> *
>ERROR at line 5:
>ORA-06550: line 5, column 11:
>PL/SQL: ORA-00933: SQL command not properly ended
>ORA-06550: line 3, column 1:
>PL/SQL: SQL Statement ignored
>
>altough accrding to http://www.remote-dba.cc/10g_65.htm , it should
>work.
>
>I'm using 10.1.0.4 on windows.
>
>Any idea ?

From one part of the SQL manual it looks like it should work but from a couple of other places, it looks like it can only be simple expressions, i.e. sal*10 or something of that nature.

From the oracle SQL manual:

<quote>
returning_clause

The returning clause retrieves the rows affected by a DML statement. You can specify this clause for tables and materialized views and for views with a single base table.

When operating on a single row, a DML statement with a returning_clause can retrieve column expressions using the affected row, rowid, and REFs to the affected row and store them in host variables or PL/SQL variables.

When operating on multiple rows, a DML statement with the returning_clause stores values from expressions, rowids, and REFs involving the affected rows in bind arrays. <end quote>

I believe you have to use BULK COLLECT when RETURNING INTO with a multiple record insert or update.

From the PL/SQL manual:

<quote>
By default, you can use this clause only when operating on exactly one row. When you use bulk SQL, you can use the form RETURNING BULK COLLECT INTO to store the results in one or more collections. <end quote>

That's the way I understand it at least. It's very confusing really. I don't have access to a database right now or I would ty it but I think the bulk collect is what you need.

I checked out the link to Don Burleson's site and if it does work the way he says it does, it would be a nice feature. From reading the docs I don't think it work's that way though. But I haven't personally tried it.

Hope that helps,

Lewis

Lewis R Cunningham

Author, ItToolBox Blog: An Expert's Guide to Oracle http://blogs.ittoolbox.com/oracle/guide/

Topic Editor, Suite101.com: Oracle Database http://www.suite101.com/welcome.cfm/oracle

Received on Sun Apr 10 2005 - 18:49:32 CDT