Oracle procedure (failure od date format)

Question

I have this procedure:

SQL> create or replace procedure KORELACJA (START IN DATE, END IN DATE) AS
  2  BEGIN
  3  SELECT T.City, Corr(T.Value, H.Value)
  4  FROM TEMP T 
  5  INNER JOIN HUMIDITY H 
  6  on T.City = H.City 
  7  and T.mDate = H.mDate 
  8  WHERE T.mDate between to_date(START,'YYYY-MM-DD') and to_date(END,'YYYY-MM-DD') 
  9  GROUP BY T.City
 10  END;
 11  /

with error: ORA-06550: line 1, column 7:

Anyone knows how to fix this problem?

[EDIT]

SQL> show error procedure KORELACJA;
Errors for PROCEDURE KORELACJA:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1      PLS-00428: an INTO clause is expected in this SELECT statement

Can you please paste the output of `show error procedure KORELACJA;`? — pvy4917, Dec 05 '18 at 17:04
`DECLARE` two variables say `t_city` and `t_h_corr`, then replace `SELECT T.City, Corr(T.Value, H.Value)` with `SELECT T.City, Corr(T.Value, H.Value) INTO t_city, t_h_corr `. — pvy4917, Dec 05 '18 at 17:21
I tried this but still doesn't work. Should I change every T.City to t_city? — giorgio.pier, Dec 05 '18 at 17:24
I cannot dig into your table, probably this might help you. https://www.plsql.co/ora-06550-pls-00428-an-into-clause-is-expected-in-this-select-statement.html AND https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/selectinto_statement.htm#LNPLS01345 — pvy4917, Dec 05 '18 at 17:26
Try replace T.City with to_city in every where. If there is an error please update it too. — pvy4917, Dec 05 '18 at 17:34
In PL/SQL queries must select INTO variables. [Find out more](https://stackoverflow.com/q/25486543/146325) — APC, Dec 05 '18 at 18:01

score 0 · Accepted Answer · answered Dec 05 '18 at 18:38

Have a look at this example; read comments within the code.

I've created sample tables, just to make sure that the procedure code compiles.

SQL> create table temp (city varchar2(10), value number, mdate date);

Table created.

SQL> create table humidity (city varchar2(10), value number, mdate date);

Table created.

The procedure itself:

SQL> create or replace procedure korelacja
  2    (p_start in date, p_end in date)           --> renamed parameters
  3  is
  4    l_city temp.city%type;                     --> declared local variables for SELECT
  5    l_corr number;                             --  statement's results
  6  begin
  7    select t.city, corr(t.value, h.value)
  8      into l_city, l_corr                      --> missing INTO clause
  9      from temp t join humidity h on t.city = h.city
 10                                 and t.mdate = h.mdate
 11      where t.mdate between p_start and p_end  --> parameters already are DATEs; you don't
 12      group by t.city;                         --  need TO_DATE against them
 13  end;
 14  /

Procedure created.

SQL>

Thank you very much for your help. I really appreciate it. – giorgio.pier Dec 05 '18 at 19:12 — giorgio.pier, Dec 05 '18 at 19:12

Oracle procedure (failure od date format)

1 Answers1