Invalid character error when inserting data with select statement in Oracle

Question

I am trying to insert a data from another table using below query

cmd_write = new OracleCommand(@"insert into table1 (f_id, f_name, s_id, r_id,
       t_date, t_type, sys_entry_date, parent_sys_id, sys_f_source)
      select 
          f_id, f_name, s_id, r_id,
          t_date, 1, sysdate, sys_id, sys_file_source 
      from table2 
      where sys_id = :sy_id 
      returning sys_id
        into :sys_get_id ", ora_con);

cmd_write.Parameters.Add(new OracleParameter("sy_id", sys_id));

OracleParameter sys_get_id = new OracleParameter("sys_get_id", OracleDbType.Int64);
sys_get_id.Direction = ParameterDirection.Output;
cmd_write.Parameters.Add(sys_get_id);
cmd_write.CommandTimeout = 0;

cmd_write.ExecuteNonQuery();

I get this error:

ORA-00911: invalid character

returning into is a pl/sql construct while you have just a simple sql statement if I do understand what this extract of C# code means. please take a look [also here](https://stackoverflow.com/questions/29707220/oracle-insert-into-returning). Do you need so see the number of the rows affected? Isn't the `ExecuteNonQuery` method returning that already? — micklesh, May 15 '20 at 14:48
@micklesh iam moving some record to table1(interface) table not all files from table2. — Sachu, May 15 '20 at 15:24
@Sachu you are inserting multiple records here n trying to return one sys_id, that's the issue, u can get no of records u have inserted — Muhammed Nigil, May 15 '20 at 15:33
@MuhammedNigil this is inisde another loop..The condition is sys_id = :sy_id so only record will enter at a time — Sachu, May 15 '20 at 15:55
try this - place `....:sy_id returning sys_id into :sys_get_id` all on a same line — T.S., May 16 '20 at 01:38
@Sachu have you done, what I asked you? ^^^^ not much but I bet.... — T.S., May 16 '20 at 19:04
@T.S. its not possible..returning wont work with select..only work with value — Sachu, May 28 '20 at 16:26
@Sachu Damn, you're so right! I missed that completely. I think, this is possible with `select` but it must be a single record. I believe, your original error is due to placing returning into another line. Now, you have a different error. Am I correct. Yes. `INTO` requires a single row `Used only for single-row queries, this clause specifies the variables or record into which column values are retrieved. For each value retrieved by the query, there must be a corresponding, type-compatible variable or field in the INTO clause.` — T.S., May 28 '20 at 16:33

score 1 · Answer 1 · answered May 28 '20 at 17:41

It looks like this is not possible. I tried this

create table A (c1 varchar(10) null);

declare 
   x varchar2(10);
   TYPE myType IS TABLE OF A.c1%TYPE INDEX BY BINARY_INTEGER;
   vData myType;
begin
   insert into A select dummy from dual returning c1 bulk collect into vData;
   -- null;
end;
/

To no avail. Then I found this

Invalid character error when inserting data with select statement in Oracle

1 Answers1