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. Do you need so see the number of the rows affected? Isn't the ExecuteNonQuery method returning that already? — micklesh
– micklesh, Commented May 15, 2020 at 14:48
@micklesh iam moving some record to table1(interface) table not all files from table2. — Sachu
– Sachu, Commented May 15, 2020 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
– Muhammed Nigil, Commented May 15, 2020 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
– Sachu, Commented May 15, 2020 at 15:55
try this - place ....:sy_id returning sys_id into :sys_get_id all on a same line — T.S.
– T.S., Commented May 16, 2020 at 1:38

T.S. · Accepted Answer · 2020-05-28 17:41:06Z

1

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

answered May 28, 2020 at 17:41

T.S.

19.6k11 gold badges70 silver badges96 bronze badges

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

Collectives™ on Stack Overflow

Invalid character error when inserting data with select statement in Oracle

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