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.

user980411
  • 1,179
  • 3
  • 12
  • 18
  • 2
    Just a note...you should (almost) never use a cursor – Justin Pihony Apr 05 '12 at 16:56
  • 1
    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 Apr 05 '12 at 17:00
  • @JustinPihony It is indeed one. – user980411 Apr 05 '12 at 17:03
  • 1
    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 Apr 05 '12 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 Apr 05 '12 at 17:22

1 Answers1

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.

Justin Pihony
  • 66,056
  • 18
  • 147
  • 180
  • 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 Apr 05 '12 at 17:35