Re: Select from a REF CURSOR

"VC" <boston103_at_hotmail.com> writes:

> I do not think it's that complicated:
>
> begin
> for i in (select name from t2) loop
> execute immediate 'insert into t1 select instance_name, host_name,
> version from v$instance@'||i.name;
> end loop;
> end;

Thanks for the comments - while this is indeed pretty simple for a SQL statement like this, it gets substantially more messy when I'm doing a complex query covering multiple tables. But overall, you're right, and this is probably the best way of handling things.

> Alternatively, instead of interrogating your 100 databases via 100
> dblinks, you can distribute a simple job to each of the databases
> and do an insert(s) from each of the remote databases into a single
> table in your central location. You'd need only one dblink
> specification on each of those databases, and, as a new databases
> comes on-line, it can be made known automatically. I think this
> approach would be more flexible.

I agree with you. The problem here is twofold - first is that my ability to install jobs on the remote databases is limited (they are 3rd party customer databases which we support, but some of them have pretty stringent change-control limitations), and second is that our network connectivity is, let's just say, unhelpful. I'm not sure that all of the remote DBs can initiate a connection back to me, even though I can connect out. (Don't ask, I really can't face explaining :-))

But I think I'll go with the execute immediate solution, as you suggest.

Thanks for the help.
Paul

-- 
This signature intentionally left blank