0

I am trying to create stored procedure where I want to join two tables and save the result into one of the tables, but I am getting INTO clause is required error.

This is my code:

CREATE PROCEDURE DiorItemMaster
AS
SELECT *FROM pcdo_dior_item_master
INNER JOIN pcdo_itemdata on pcdo_itemdata.vpn = pcdo_dior_item_master.vpn;
GO;

ERROR:

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

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
cbirole
  • 27
  • 2

1 Answers1

2

You have a query that selects columns from a table in your pl/sql block. What do you want to do with the result of that query ? You cannot just select and not do anything with the results in pl/sql (you can in sql). Oracle expects that you to store the results of that select in variables. That can be done using the SELECT INTO clause. Example (based on sample schema emp/dept):

DECLARE
  l_emp emp%ROWTYPE;
BEGIN
  SELECT e.* INTO l_emp FROM emp e WHERE e.ename = 'KING';
END;
/

Note that you can SELECT INTO individual columns and into rows. You cannot use SELECT INTO arrays.

A couple of other remarks about your code:

  • You perform a SELECT * from a table with a join to another table without using aliases. This will return all columns from both tables. It is a lot more readable to prefix the "*" with a table alias like in the example.
  • The GO; is not part of the oracle syntax - this will cause a compilation error.
Koen Lostrie
  • 14,938
  • 2
  • 13
  • 19