CODEWITHSUNDEEP
stored-proceduresoracle10gwhere-clausesql-insertexecute-immediate
narue1992
narue1992

Reputation: 1223

Stored procedure execute Immediate error with WHERE clause

I am trying to copy over one row from my archive table to my original table.

Without my WHERE clause, the whole table of table2 gets copied to table1. I don't want this of course. So based on the gridview's ID value listed, the table will copy over only the row whose ID is the same.

When I debug the lines I get the correct ID listed for DisplaySup.Rows(0).Cells(2).Text.

(     
val_ID table2.V_ID%type
) 

is 
begin 

execute immediate 'insert into table1 (select * from table2 where V_ID = val_ID)'; 
end;

Yet I get the error

ORA-00904: "VAL_ID": invalid identifier

Table2 and Table1 have identical columns; so they both have column titled V_ID. I am unsure why Val_ID is flagging an error.

VB.net line of coding:

SupArchive.Parameters.Add("val_ID", OleDbType.VarChar).Value = DisplaySup.Rows(0).Cells(2).Text

So I tried to reference: EXECUTE IMMEDIATE with USING clause giving errors

Like so to fix WHERE:

(     
val_ID table2.V_ID%type
) 

is 
begin 

execute immediate 'insert into table1 (select * from table2 where V_ID = '||val_ID||')'; 
end;

but I get error:

ORA-00904: "val_ID": invalid identifier

Any suggestions on how to fix my stored procedure?

UPDATE:

Tried to do the suggested: 

(
val_ID table2.V_ID%type
)
AS
BEGIN

execute immediate 'insert into table1 (col1, col2, col3...)(select col1, col2, col3... from table2 where V_ID = :val_ID)' using val_ID;

end;

but get error:

ORA-00904: "col72": invalid identifier

for col72 after Select statement

EXAMPLE OF MY TABLES (both are identical) purpose of table2 is when a row is deleted in table1, table2 can re-create the user that was deleted

Table1

ID CompanyName FirstName LastName ....(72 cols)

Table2

ID CompanyName FirstName LastName... (72 cols)

Upvotes: 0

Views: 648

Answers (1)

Boneist
Boneist

Reputation: 23588

You would do best to use a bind variable in your insert statement. Also, you need to list the columns you're inserting into as well as those you're inserting, to avoid the "too many values" error.

Eg:

declare
  val_ID table2.V_ID%type := 1;
begin 
  execute immediate 'insert into table1 (col1, col2, ...) (select col1, col2, ... from table2 where V_ID = :val_ID)' using val_id;
end;
/

Although in this instance there is absolutely no need to use dynamic sql at all, so you could just do:

declare
  val_id table2.v_id%type := 1;
begin 
  insert into table1 (col1, col2, ...)
  select col1, col2, ...
  from   table2
  where  v_id = val_id;
end;
/

Don't forget to commit after you've run the procedure!

Upvotes: 1

Related Questions