Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    14

    Unanswered: how to reference cursor columns?

    Hi-

    I don't know how basic this is, but my problem is that I've got a stored function that returns the results of a SELECT via a cursor. i'd like to iterate through that cursor, but every example i've looked at references the elements of the cursor by their column names. the poblem is i don't know the colmn names (and nor do i want to) as a number of different functions return different cursors, each with varying columns but all of which i need to iterate through with the same function. is there a simple way to get the number of columns and then reference those columns by number?

    simply, i want to do something like

    for eval_results in cursor loop

    update myVar
    set var = [value of first column in cursor]
    where...

    end loop;

    but i'm not clear on that [] part.

    thanks to anyone that can help!
    Last edited by earlbradley; 01-07-04 at 19:30.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    No, there is no simple way.
    I'm not convinced there is even a "difficult" way.
    Rhetorical question - How do you proposed to handle different data types; ie VARCHAR2, NUMBER DATE, BOOLEAN, CLOB, etc. being returned by the function?

  3. #3
    Join Date
    Dec 2003
    Posts
    14
    the datatypes are the one thing i do know.

    its just that depending on the provider of the data, the names of the columns will be different.

    the nature of the problem is that i have a number of different equations, each of which returns a string of different numerical values. within each equation, the values are calculated differently, but after each equation has done its job, a separate script does some fairly uniform stuff with the data- regardless of which equation provided it. so perhaps another way of looking at the problem is how can i label the cursor fields uniformly before i iterate through them. currently i do something like this in each equation generator.

    create or replace function equation3 () return types.cursortype
    as
    l_cursor types.cursorType;
    begin
    open l_cursor for
    select stddev(one/two), sum(one)
    from table1 one, table2 two
    where...
    return l_cursor;
    end;
    /
    Last edited by earlbradley; 01-07-04 at 23:05.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by earlbradley
    how can i label the cursor fields uniformly before i iterate through them. currently i do something like this in each equation generator.

    create or replace function equation3 () return types.cursortype
    as
    l_cursor types.cursorType;
    begin
    open l_cursor for
    select stddev(one/two), sum(one)
    from table1 one, table2 two
    where...
    return l_cursor;
    end;
    /
    If your select statement looks like this:

    SELECT STDDEV(ONE/TWO) AS F1, SUM(ONE) AS F2 FROM ...

    then you can refer to aliases the same way as you would do with field names. Now you only need to use the same aliases in all your "equation" functions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •