Yep, you'll need a temp variable.
There are certain circumstances where you can pass SQL in, for example, functions called from SQL and pipelined functions
SQL> create or replace function take_a_date(p_date date) return number is
2 begin
3 return 1;
4 end;
5 /
Function created.
SQL>
SQL> select take_a_date((select sysdate from dual))
2 from dual;
TAKE_A_DATE((SELECTSYSDATEFROMDUAL))
------------------------------------
1
SQL>
SQL> create or replace function take_a_date(p_datecur sys_refcursor) return sys.odcinumberlist pipelined is
2 begin
3 pipe row ( 1 );
4 return;
5 end;
6 /
Function created.
SQL>
SQL> select * from table(take_a_date(cursor(select sysdate v from dual)));
COLUMN_VALUE
------------
1
but a straight procedure call is not one of those.