Oracle error when selecting into temp table

Question

My ultimate goal is far more complex than this, but this is the cut down version of what is causing my error. I want to put some rows into a temporary table (actually several temp tables, but I can't get by this first hurdle). Here is my PL/SQL;

DECLARE
    type L1_store is table of MyTable%rowtype;
    L1 L1_store;
BEGIN
    select
        * bulk collect
    into L1
    from MyTable
    WHERE 1=1
    and length(MyColumn1) = 2;

    select 
        L1.MyColumn1 
        ,L1.MyColumn2
    from L1;
END;

And here is the error I get;

ORA-06550: line 19, column 6:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 16, column 1:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

The line numbers may be incorrect as I have edited the actual PL/SQL for clarity

****EDIT****

OK, so I originally accepted the answer offered below as it looks like it answers my question, and I can see how I could use it. However, for clarity, here is my ultimate goal, in case there is a better answer than the one I have in my head.

If I was just doing this in SQL I would do something like;

with L1 as
(select * from table),
L2 as 
(select * from anothertable)

select L1.Column
from L1
left join L2 on L1.somecolumn = L2.somecolumn

I don't know if this helps or hinders, but thanks all in anticipation of your continued patience.

Answer 1

type L1_store is table of MyTable%rowtype; is not a temporary table; it is a collection data type declared in the PL/SQL scope and cannot be used in the SQL scope. Similarly, %ROWTYPE is a PL/SQL construct.

If you want to use a collection in SQL then declare it in the SQL scope:

CREATE TYPE mytable_data is OBJECT (
  mycolumn1 VARCHAR2(50),
  mycolumn2 NUMBER,
  mycolumn3 DATE
);

CREATE TYPE mytable_data_table IS TABLE OF mytable_data;

so for some test data:

CREATE TABLE MyTable(
  mycolumn1 VARCHAR2(50),
  mycolumn2 NUMBER,
  mycolumn3 DATE
);

INSERT INTO MyTable VALUES ( 'AA', 42, SYSDATE );

Then you can do:

DECLARE
    L1 mytable_data_table;
BEGIN
    select mytable_data( mycolumn1, mycolumn2, mycolumn3 )
    bulk collect into L1
    from  MyTable
    WHERE length(MyColumn1) = 2;

    FOR i IN 1 .. L1.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE( L1(i).mycolumn1 || ' ' || l1(i).mycolumn2 );
    END LOOP;
END;
/

Which outputs:

AA 42

db<>fiddle here