Fetching from the Results of Table Functions

Question

I use a function and pipeline its return values. I call the above function as:

SELECT * FROM TABLE(FUNC(params)) ORDER BY somecolumn;

It returns the results as a 1 x 4 table, and I tried using cursors to retrieve them. But is shows an error, saying that a cursor is only for fieldnames or column names and not for type.

Is it still possible to use cursors for the same, or is there any other way to retrieve the individual fields.

stackoverflow.com/questions/58141/… for your answer on why to not use a cursor. I am not sure about oracle, so am not sure, but it might help to clarify that your func is a table function (my assumption?) — Justin Pihony
– Justin Pihony, Commented Apr 5, 2012 at 17:00
OK, what is your question here, actually? What exactly are you trying to do...that is not really clear to me at least...maybe some sample output or further explanation would help — Justin Pihony
– Justin Pihony, Commented Apr 5, 2012 at 17:08
@JustinPihony: I am using a function, which I use as: SELECT * FROM TABLE(FUNC(params)) ORDER BY somecolumn; (directly). Now, I need to execute it via pl sql and then retrive each of its fields, i.e. the output. — user980411
– user980411, Commented Apr 5, 2012 at 17:22

Justin Pihony · Accepted Answer · 2012-04-05 17:28:07Z

1

I think this is what you want?

SELECT MyTable.Column1, MyTable.Column3, etc
FROM TABLE(FUNC(params)) MyTable
ORDER BY somecolumn;

To access specific columns, just alias the table.

answered Apr 5, 2012 at 17:28

Justin Pihony

67.2k20 gold badges154 silver badges185 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user980411 Over a year ago

I used in pl sql using a cursor: SELECT MyTable.Column1, MyTable.Column3 FROM TABLE(FUNC(params)) MyTable ORDER BY somecolumn; It says MyTable.Column1 must be declared.

Collectives™ on Stack Overflow

Fetching from the Results of Table Functions

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related