Search code examples
sqlplsqlprocedure

PL/SQL procedure not compiling

I have a PL/SQL procedure that is not compiling. The errors are:

  1. Error(3,7): PLS-00103: Encountered the symbol "INTO" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge The symbol "INTO" was ignored.

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

My code:

CREATE OR REPLACE PROCEDURE findvisitsandlaborcosts
as
begin
select * from SI.customer;
end;
/

I have googled an online syntax checker and it says on the first line there is an error. But WHERE?!? It seems to be correct. I have googled the syntax of declaring a procedure and I have cross-checked many times. It must be something simple that I am overlooking...

Solution

  • in a PLSQL code, you need a placeholder to keep results of a SELECT query. Since PLSQL engine is expecting INTO clause within SELECT statement.

    To begin with, you can select a set of columns and assign their values to local variables.

    Your code should be like this -

    CREATE OR REPLACE PROCEDURE findvisitsandlaborcosts
as
v_column1 SI.customer.column1%type;
v_column2 SI.customer.column2%type;
begin
select column1, column2 into v_column1, v_column2 from SI.customer;
end;
/

    Note - you need to replace column1 and column2 with actual column names before running this code at your end.