Oracle Table Variables

Oracle doesn't have local temporary tables, and global temporary tables don't look appropriate here.

You could use a common table expression (subquery factoring):

with tempSites (siteId) as (
  select siteid
  from site
  where state = 'TX'
  union all
  select siteid
  from site
  where state = 'OK'
  and (select count(*) from site where state = 'TX') < 10
)
select s.*
from tempSites ts
join site s on s.siteid = ts.siteid;

That isn't quite the same thing, but gets all the TX IDs, and only includes the OK ones if the count of TX ones - which has to be repeated - is less than 10. The CTE is then joined back to the original table, which all seems a bit wasteful; you're hitting the same table three times.

You could use a subquery directly in a filter instead:

select *
from site
where state = 'TX'
or (state = 'OK'
  and (select count(*) from site where state = 'TX') < 10);

but again the TX sites have to be retrieved (or at least counted) a second time.

You can do this with a single hit of the table using an inline view (or CTE if you prefer) with an analytic count - which add the count of TX rows to the columns in the actual table, so you'd probably want to exclude that dummy column from the final result set (but using * is bad practice anyway):

select *    -- but list columns, excluding tx_count
from (
  select s.*,
    count(case when state = 'TX' then state end) over (partition by null) as tx_count
  from site s
  where s.state in ('TX', 'OK')
)
where state = 'TX'
or (state = 'OK' and tx_count < 10);

From your description of your research it sounds like what you've been looking at involved PL/SQL code populating a collection, which you could still do, but it's probably overkill unless your real situation is much more complicated.