ora-01008 error doing insert with select

Hi,

I'm getting an ORA-01008 error (not all variables bound) doing a pretty basic procedure which just does an insert with a select statement.

The proc looks something like:

PROCEDURE insert_rec(foo_in in varchar2)

   is
BEGIN    insert into foo

          (foo1,foo2)
   select distinct bar1, bar2
   from bar
   where bar1 = foo_in
   group by bar1, bar2;
END; Doing an execute insert_rec('someval') gives me the ORA-01008 error. The same query with a hardcoded value instead of 'foo_in' works just fine. I've tried using dynamic sql with bind vars, and doing an 'execute immediate some_query using some_val' and I still get the error.

Has anyone run into anything like this? I'm running on 8.1.5 on NT. I tried doing a simpler test case like this and I couldn't reproduce the problem. One more random note is that the bar table is a synonym for a table through a db link. Any ideas?

Howie

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Sep 11 2000 - 18:05:04 CDT