Stored procedure execute Immediate error with WHERE clause

Question

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)

Boneist · Accepted Answer · 2015-12-01T17:40:58.860

1

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!

edited Dec 01 '15 at 17:40

answered Dec 01 '15 at 17:19

Boneist

22,910
1
25
40

If you don't mind me asking what `:=1` does? Because my val_id gets filled by my gridview cell value – narue1992 Dec 01 '15 at 17:21
I guess you had this defined as a stored procedure with a parameter - ie. `create or replace procedure ...`, right? I defined mine as an anonymous block that had a variable declared. Since it's not a parameter, you need to assign a value to it, otherwise it'll be null, hence the `:=1`. It was purely for demo purposes; you just need to focus on removing the execute immediate and just using the insert statement directly - and changing the names of the columns, of course! – Boneist Dec 01 '15 at 17:26
I get the same issue though with your second setup. My last col after `SELECT` flags an error saying it is an `invalid identifier` and says `Error Text = PL/SQL: SQL Statement ignored ` for line 7. I had these same issues which is why I tried to attempt execute immediate. however, when I do your execute immediate I get the `FROM keyword not found where expected` error – narue1992 Dec 01 '15 at 17:39
1

That's because there was an extra ) at the end of the column list in the select. I've updated my answer to remove it. As for why the static sql version fails... perhaps if you updated your question to include create table statements for table1 and table2, we could attempt to debug things. – Boneist Dec 01 '15 at 17:43
And don't forget you'll need to edit the column lists (`col1, col2, ...`) to reflect your actual column lists from both table1 and table2 – Boneist Dec 01 '15 at 17:44
My `col1..` are edited for my coding. If I remove the extra ) I get the same error as I do with your second example that my last `col` listed after the SELECT statement is `invalid identifier` which makes no sense. it is as if I am missing some closing statement. I will update my post with what my coding looks like – narue1992 Dec 01 '15 at 17:50
Please supply a complete test case - eg. create table statements, insert statements with some sample data for table1 and the actual procedure that you're running with. Make sure you can replicate the issue with your test case - if you can't, then maybe you've mistyped a column name or something, somewhere in your original code. – Boneist Dec 01 '15 at 17:52
My actual procedure I am using is in my post under `Update` title. – narue1992 Dec 01 '15 at 17:52
I added a short snippet of how my tables look. They are identical with 72 columns – narue1992 Dec 01 '15 at 17:55
1

They can't be identical, otherwise you wouldn't have got the too any values error! – Boneist Dec 01 '15 at 17:58
Oh ya. I forgot that when I re-pipe my table1 I always have to manually add my col72 to match table2. My issue was after I made my new Stored Procedure from Boneist's example, I forgot that I re-piped my table again – narue1992 Dec 01 '15 at 18:00
Sorry for extended chat. Your removal of the extra parenthesis did fix the issue and I just needed to re-add my last column. Thank you for being patient with me – narue1992 Dec 01 '15 at 18:01
No worries; I would recommend you use the static SQL version rather than the dynamic SQL version! I'm glad you got to the bottom of the issue *{:-) – Boneist Dec 01 '15 at 18:04

Stored procedure execute Immediate error with WHERE clause

1 Answers1