Home » SQL & PL/SQL » SQL & PL/SQL » Using WITH clause values as parameters in a TABLE function (Oracle 11.2.0.4 on Windows 7 64bit)
Using WITH clause values as parameters in a TABLE function [message #665987] Thu, 05 October 2017 15:30 Go to next message
orabert
Messages: 5
Registered: October 2013
Junior Member
Am I missing something, or is it not permitted to use values from a WITH clause in a TABLE function in the following SELECT statement?

WITH param_values as (
  select 2017 as current_year from dual
  )
SELECT * FROM TABLE(my_pipelined_table_function(param_values.current_year));
Produces:
ORA-00904: "param_values"."current_year": invalid identifier

Of course, "my_pipelined_table_function" takes a single numeric value as a parameter.

The reason why I want to use the WITH clause is because the SELECT statement containing the TABLE function is actually much more complicated, but everything else works fine except the TABLE function clause.

I can't seem to find any documentation explaining the reason why Oracle may be throwing this error in this case.

Thanks so much for any insights!
Re: Using WITH clause values as parameters in a TABLE function [message #665988 is a reply to message #665987] Thu, 05 October 2017 15:57 Go to previous messageGo to next message
Alien
Messages: 273
Registered: June 1999
Senior Member
Hi,

you need to select from param_values as well:
WITH param_values as ( select 2017 as current_year from dual)
SELECT * FROM param_values, TABLE(my_pipelined_table_function(param_values.current_year));

Regards,

Arian
Re: Using WITH clause values as parameters in a TABLE function [message #665989 is a reply to message #665988] Thu, 05 October 2017 16:30 Go to previous message
orabert
Messages: 5
Registered: October 2013
Junior Member
doh! Of course!
Thanks Alien.
Egg on my face. Embarassed

[Updated on: Thu, 05 October 2017 16:30]

Report message to a moderator

Previous Topic: row-to-row navigation query
Next Topic: returning collection from a loop and bulk collect
Goto Forum:
  


Current Time: Sat Oct 21 07:31:05 CDT 2017

Total time taken to generate the page: 0.05605 seconds