Re: Select from a REF CURSOR

Daniel Morgan <damorgan_at_x.washington.edu> writes:

> Paul Moore wrote:
>
>>This may be a dumb question, but I've never got my head round the
>>strange TABLE(CAST(...)) syntax. I have a function which returns a REF
>>CURSOR. What I'd like to do is to use this function in a SELECT
>>statement. Something like:
>>
>> CREATE FUNCTION f(args) RETURNS SYS_REFCURSOR ...
>>
>> SELECT * FROM f(1,2,3);
>>
>>Is this possible, and if so what syntax should I be using?
>>
>>Thanks,
>>Paul.
>>
> I'd suggest using a stored procedure rather a function. Return the
> REF CURSOR as an OUT parameter.

Hmm. I think I probably am approaching my real problem the wrong way.

Taking a step back, what I'm trying to do is as follows:

I have a database, in which I'm trying to collect stats about a number of other databases I'm monitoring (lots of them - 100+) I have a series of DB links to each of the other databases, and my collection routines are basically along the lines of (trivial example to demonstrate the point - my real collection routines are not this simple)

    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).

So what I was hoping was to be able to create a function something like

function remote_table(table_name, link_name) as

    cursor c
begin

    open c for 'select * from ' || table_name || '@' || link_name;     return c;
end;

and then use remote_table('v$instance', c.dbname) as a table in my insert...select above.

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