Oracle using Temp tables in stored procedure

Question

I have a query that uses temp tables and I would like to add this to a stored procedure. However upon compiling I get "Error(10,1): PLS-00428: an INTO clause is expected in this SELECT statement"

as example

    WITH T1 as
    (
      SELECT ID, CREATED_DATE, LOOKUP_ID
      FROM TEST1
    ),T2 as
    (
      SELECT ID, CREATED_DATE, LOOKUP_ID
      FROM TEST2
    )
    SELECT * from T1
    minus
    SELECT * from T2

    RESULTS
    ID CREATED_D  LOOKUP_ID
---------- --------- ----------
    217322 11-DEC-16          1

Adding as a Stored Procedure:

create or replace PROCEDURE "TEST"
(
  T IN OUT SYS_REFCURSOR
) AS
BEGIN

  WITH T1 as
  (
    SELECT ID, CREATED_DATE, LOOKUP_ID
    FROM TEST1
  ), T2 as
  (
    SELECT ID, CREATED_DATE, LOOKUP_ID
    FROM TEST2
  )
  SELECT * from T1
  minus
  SELECT * from T2
end;

END;

Error(7,1): PLS-00428: an INTO clause is expected in this SELECT statement

I did see PLS-00428: an INTO clause is expected in this SELECT statement BUT that is using INSERTS and I do not want to,. I would like to use temp tables only.

The selected columns should always be read into PL/SQL variables and then printed using DBMS_OUTPUT.PUT_LINE. The query results are not displayed automatically when executed from a procedure or function. — shrek, Mar 23 '18 at 18:20

score 1 · Accepted Answer · answered Mar 23 '18 at 18:42

1

We need to handle the output of the query in a PL/SQL variable.

create or replace PROCEDURE "TEST"
(
  T IN OUT SYS_REFCURSOR
) AS

cursor c_cur is
 WITH T1 as
  (
    SELECT ID, CREATED_DATE, LOOKUP_ID
    FROM TEST1
  ), T2 as
  (
    SELECT ID, CREATED_DATE, LOOKUP_ID
    FROM TEST2
  )
  SELECT * from T1
  minus
  SELECT * from T2;
BEGIN
for r_cur in c_cur
loop
dbms_output.put_line('ID: '||r_cur.id ||'CREATED_DATE: ' ||r_cur.CREATED_DATE ||' LOOKUP_ID: '||r_cur.lookup_id);
end loop;
end;

answered Mar 23 '18 at 18:42

Avani

205
1
6

- Error(40,30): PLS-00364: loop index variable 'R_CUR' use is invalid – pthfndr2007 Mar 23 '18 at 19:02
@HOGAN - IN OUT (removed the IN) added Using this exit and above solution works for me. – pthfndr2007 Mar 23 '18 at 19:40

score 0 · Answer 2 · answered Mar 23 '18 at 18:47

You'll have to rewrite it; as procedure uses refcursor, I presume that you want to use that WITH factoring clause as the source for it.

A working example on Scott's DEPT table:

SQL> create or replace procedure p_test (t in out sys_refcursor)
  2  as
  3  begin
  4    open t for
  5       select t1.* from (select dname from dept) t1;
  6  end;
  7  /

Procedure created.

SQL>
SQL> var l_rc refcursor
SQL> exec p_test (:l_rc);

PL/SQL procedure successfully completed.

SQL> print :l_rc

DNAME
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS

SQL>

Or, your code rewritten in the same manner (hopefully, I didn't make a typo):

create or replace procedure test (t in out sys_refcursor)
as
begin
  open t for
    select * from (select * from (select id, created_date, lookup_id
                                  from test1) 
                   minus
                   select * from (select id, created_date, lookup_id
                                  from test2)
                  );
end;

I am not able to rewrite the query (the example I added was generic and the actual one is multiple temp tables and quite large). — pthfndr2007, Mar 23 '18 at 19:08
Why wouldn't you be able to rewrite it? CTE just substitutes inline views we've used *for ages* so - I don't see any reason for you not being able to do it. — Littlefoot, Mar 23 '18 at 19:09

Oracle using Temp tables in stored procedure

2 Answers2