CODEWITHSUNDEEP
sqloracle-databaseplsql
pthfndr2007
pthfndr2007

Reputation: 103

Oracle using Temp tables in stored procedure

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.

Upvotes: 2

Views: 2920

Answers (2)

Littlefoot
Littlefoot

Reputation: 142720

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;

Upvotes: 0

Avani
Avani

Reputation: 215

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;

Upvotes: 1

Related Questions