Hi Chris and Connor,
How can I give an alias a non named column resulting from a subquery?
the case

select *
from
(
select 6*7 from dual
);

I know it's easy to alias it in the subquery like that

select result
from
(
select 6*7 as result from dual
);

but the problem is here in that case when trying to alias the result of a pl/sql created table of varchar2 that wee need to direct to another created table. and here is the full case:


CREATE OR REPLACE TYPE ABC AS TABLE OF VARCHAR2(20);
CREATE TYPE EMPREC AS OBJECT
(
F_NAME VARCHAR2(20),
F_JOB VARCHAR2(20)
);
CREATE TYPE EMP_TABLE AS TABLE OF EMPREC;

DECLARE
L_ABC ABC;
L_EMP_TABLE EMP_TABLE;
BEGIN
SELECT FIRST_NAME BULK COLLECT INTO L_ABC
FROM EMPLOYEES
WHERE DEPARTMENT_ID=50;
DBMS_OUTPUT.PUT_LINE(L_ABC.COUNT);
--SELECT EMPREC(L_ABC.*,'DEVELOPER') -- that will not work
--SELECT EMPREC(TA.*,'DEVELOPER') -- that also will not work
SELECT EMPREC('USINE BOLT','DEVELOPER') -- that will work
BULK COLLECT INTO L_EMP_TABLE
FROM TABLE(L_ABC);
DBMS_OUTPUT.PUT_LINE(L_EMP_TABLE.COUNT);
END;
/

so how to reference the data of the L_ABC table in the select statement
Thanks in advance

select "6*7" from
  ( select 6*7 from dual
  ) ;

6*7  
42   

declare
  l_abc abc;
  l_emp_table emp_table;
begin
  select first_name bulk collect
  into l_abc
  from hr.employees
  where department_id=50;
  dbms_output.put_line ( l_abc.count ) ;
  
  select emprec(t.column_value, null)
  bulk collect
  into l_emp_table
  from table ( l_abc ) t ;
  dbms_output.put_line ( l_emp_table.count ) ;
end;
/

45
45

declare
  l_abc emp_table;
  l_emp_table emp_table;
begin
  select emprec(first_name , job_id)
  bulk collect
  into l_abc
  from hr.employees
  where department_id=50;
  dbms_output.put_line ( l_abc.count ) ;
  
  select emprec(t.f_name, t.f_job)
  bulk collect
  into l_emp_table
  from table ( l_abc ) t  ;
  dbms_output.put_line ( l_emp_table.count ) ;
end;
/

45
45