Re: Select from a REF CURSOR

Paul,

Please see in-line:

"Paul Moore" <pf_moore_at_yahoo.co.uk> wrote in message news:brrng23q.fsf_at_yahoo.co.uk...
>
> insert into uptime_table (db, startup_time)
> select 'db_name', startup_time
> from v$instance_at_db_name;
>
> Now I want to keep this as an insert...select, as messing about with
> cursor loops and the like is a lot more fiddly and error prone.
>
> But having to write the same code for 100+ databases isn't much fun
> either.
>
> So I was hoping to be able to do something along the lines of
>
> for c in (select dbname from databases) loop
> insert into.... (as above, using c.dbname)
> end loop
>
> (with a little fiddling to get around the OPEN_LINKS limit).
>
> The problem is that the "@db_name" syntax kills this, as the db_name
> there is part of the syntax, not a value. I know I could use execute
> immediate, but for something like this, I find it pretty unreadable
> (because of the need to double up the quotes, concatenate bits
> together, etc).
>

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;

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.

Rgds.

> If I can't do it like this, that's fair enough - I'll use one of the
> other ways, and put up with the (to my mind) slightly worse
> maintainability. But the idea of using a ref cursor as a table seemed
> natural enough that I thought it might just be a case of me not
> knowing the right syntax. Hence my question.
>
> Thanks,
> Paul.
> --
> This signature intentionally left blank
Received on Fri Nov 07 2003 - 19:00:57 CST