+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

Select from a REF CURSOR

  1. Select from a REF CURSOR

    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.
    --
    This signature intentionally left blank

  2. Re: Select from a REF CURSOR

    Hello Paul,

    Here's an example:

    create or replace type myType as object (
    a int,
    b varchar2(10)
    )
    /

    create or replace type myTable as table of myType;
    /

    create or replace function f1 return myTable as
    l_data myTable := myTable();
    begin
    for i in 1 .. 5 loop
    l_data.extend;
    l_data(i) := myType(i, 'Row #'||i );
    end loop;
    return l_data;
    end;
    /

    select * from TABLE ( cast( f1() as myTable ) );

    ---------- ----------
    1 Row #1
    2 Row #2
    3 Row #3
    4 Row #4
    5 Row #5


    Rgds.

    "Paul Moore" wrote in message
    news:u15gm0e0.fsf@yahoo.co.uk...
    > 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.
    > --
    > This signature intentionally left blank




  3. Re: Select from a REF CURSOR

    Hello Paul,

    Here's an example:

    create or replace type myType as object (
    a int,
    b varchar2(10)
    )
    /

    create or replace type myTable as table of myType;
    /

    create or replace function f1 return myTable as
    l_data myTable := myTable();
    begin
    for i in 1 .. 5 loop
    l_data.extend;
    l_data(i) := myType(i, 'Row #'||i );
    end loop;
    return l_data;
    end;
    /

    select * from TABLE ( cast( f1() as myTable ) );

    ---------- ----------
    1 Row #1
    2 Row #2
    3 Row #3
    4 Row #4
    5 Row #5


    Rgds.

    "Paul Moore" wrote in message
    news:u15gm0e0.fsf@yahoo.co.uk...
    > 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.
    > --
    > This signature intentionally left blank




  4. Re: Select from a REF CURSOR

    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.

    --
    Daniel Morgan
    http://www.outreach.washington.edu/e...ad/oad_crs.asp
    http://www.outreach.washington.edu/e...oa/aoa_crs.asp
    damorgan@x.washington.edu
    (replace 'x' with a 'u' to reply)


  5. Re: Select from a REF CURSOR

    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.

    --
    Daniel Morgan
    http://www.outreach.washington.edu/e...ad/oad_crs.asp
    http://www.outreach.washington.edu/e...oa/aoa_crs.asp
    damorgan@x.washington.edu
    (replace 'x' with a 'u' to reply)


  6. Re: Select from a REF CURSOR

    "VC" writes:

    > Hello Paul,
    >
    > Here's an example:
    >
    > create or replace type myType as object (

    [...]

    Thanks for this, but I already knew this approach. What I'm looking
    for is a way to cast a returned REF CURSOR (not an object table) into
    a table suitable for a FROM clause.

    See my other followup for a bit more detail...

    Paul
    --
    This signature intentionally left blank

  7. Re: Select from a REF CURSOR

    "VC" writes:

    > Hello Paul,
    >
    > Here's an example:
    >
    > create or replace type myType as object (

    [...]

    Thanks for this, but I already knew this approach. What I'm looking
    for is a way to cast a returned REF CURSOR (not an object table) into
    a table suitable for a FROM clause.

    See my other followup for a bit more detail...

    Paul
    --
    This signature intentionally left blank

  8. Re: Select from a REF CURSOR

    Paul,

    I am sorry to say but the way you already knew about is the only one to use
    the REF CURSOR in a select statement.

    Rgds.


    "Paul Moore" wrote in message
    news:fzgzg2yd.fsf@yahoo.co.uk...
    > "VC" writes:
    >
    > > Hello Paul,
    > >
    > > Here's an example:
    > >
    > > create or replace type myType as object (

    > [...]
    >
    > Thanks for this, but I already knew this approach. What I'm looking
    > for is a way to cast a returned REF CURSOR (not an object table) into
    > a table suitable for a FROM clause.
    >
    > See my other followup for a bit more detail...
    >
    > Paul
    > --
    > This signature intentionally left blank




  9. Re: Select from a REF CURSOR

    Paul,

    I am sorry to say but the way you already knew about is the only one to use
    the REF CURSOR in a select statement.

    Rgds.


    "Paul Moore" wrote in message
    news:fzgzg2yd.fsf@yahoo.co.uk...
    > "VC" writes:
    >
    > > Hello Paul,
    > >
    > > Here's an example:
    > >
    > > create or replace type myType as object (

    > [...]
    >
    > Thanks for this, but I already knew this approach. What I'm looking
    > for is a way to cast a returned REF CURSOR (not an object table) into
    > a table suitable for a FROM clause.
    >
    > See my other followup for a bit more detail...
    >
    > Paul
    > --
    > This signature intentionally left blank




  10. Re: Select from a REF CURSOR

    Daniel Morgan 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@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

+ Reply to Thread
Page 1 of 2 1 2 LastLast