Power By 专否社区

Fetching from the Results of Table Functions

Go To StackoverFlow.com

0

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.

2012-04-05 16:55
by user980411
Just a note...you should (almost) never use a curso - Justin Pihony 2012-04-05 16:56
http://stackoverflow.com/questions/58141/why-is-it-considered-bad-practice-to-use-cursors-in-sql-server 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 2012-04-05 17:00
@JustinPihony It is indeed one - user980411 2012-04-05 17:03
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 hel - Justin Pihony 2012-04-05 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 2012-04-05 17:22

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.

2012-04-05 17:28
by Justin Pihony
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 - user980411 2012-04-05 17:35

Ads