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.

Unfortunately record types (defined with `%rowtype`) are PL/SQL constructs and cannot be used in SQL statements (even SQL statements embedded in PL/SQL). There are alternative solutions but you need to be a bit clearer about your real intentions before we can suggest a better approach. — APC, Jan 16 '20 at 10:11
You are not using a temporary table. `type L1_store is table of MyTable%rowtype` is a collection data type declared in the PL/SQL scope and you cannot use it in the SQL scope. — MT0, Jan 16 '20 at 10:12

score 1 · Answer 1 · answered Jan 16 '20 at 09:53

1

The problem here seems to be the second select in the block. If you're trying to select the data from the record, I would be inclined to put it into a FOR loop. I.e, something like this:

for i in 1 .. L1.count() LOOP
   dbms_output.put_line (L1(i).MyColumn1);
end loop;

You could assign the value of MYColumn1 to a variable or use it in some other way.

answered Jan 16 '20 at 09:53

Shane Andreucetti

11
2

Hi Shane. Thanks for this, but if I do that, I get no output, although it does complete successfully. I tried to output the count of rows, but I see nothing in the script output, other than the successful completion message. I know there are rows in my source table and I even took my where clause out, still no luck. – SkinnyPete63 Jan 16 '20 at 09:59
OK, so now I can see the output, thanks for that. But unfortunately this doesn't really help me. What I need to do is create a couple of temporary tables, join them together and get the output from that combined result. In SQL I would do it like; with L1 as (select blah from table), L2 as (select blay from table2) select L1.Column from L1 left join L2 on some table join – SkinnyPete63 Jan 16 '20 at 10:13

score 1 · Accepted Answer · answered Jan 16 '20 at 10:15

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

That's awesome, exactly what I was looking for. Far more complicated that SQL Server :) , but at least I have the answer! Thanks for this. — SkinnyPete63, Jan 16 '20 at 10:18
@SkinnyPete63 This is an answer to the question that you've asked but i'm not convinced it is the solution that you should be looking for. However, without more details of what you are trying to do we can only answer the question you've asked rather than trying to find the best solution for the problem you actually have. — MT0, Jan 16 '20 at 10:19

Oracle error when selecting into temp table

2 Answers2