Oracle Procedure error (PLS-00428)

Question

This is the error message: PLS-00428: an INTO clause is expected in this SELECT statement. Meanwhile, this is the procedure for testing displaying the system date:

CREATE OR REPLACE 
PROCEDURE "TEST_PROCEDURE"
AS
BEGIN
    SELECT SYSDATE FROM DUAL;
END;

In the first place I don't need to use that INTO Oracle is insisting for me to do. Is there other way around beside using a cursor (I've seen it here https://stackoverflow.com/a/6029963/1983024)? I think it should not be like that, this does run normally just like in MS SQL without using INTO or cursor.

Answer 1

you can write

CREATE OR REPLACE 
PROCEDURE "TEST_PROCEDURE"
AS
BEGIN
for r_row in ( SELECT SYSDATE s FROM DUAL)
  dbms_output.put_line(r_row.s);
end loop;
END;

or you have to have a variable.

CREATE OR REPLACE 
PROCEDURE "TEST_PROCEDURE"
AS
  v_Date date;
BEGIN
    SELECT SYSDATE into v_date FROM DUAL;
  dbms_output.put_line(v_date );

END;

output format is dictated by your NLS_DATE_FORMAT setting, or you can be explicit like to_char(v_date, 'dd-mm-yyyy') etc.

Answer 2

You can use like

CREATE OR replace PROCEDURE Test_procedure
IS
  date CHAR(10);
BEGIN
    SELECT To_char(SYSDATE, 'MM/DD/YYYY')
    INTO   date
    FROM   dual;

    dbms_output.Put_line(date);
END; 

it will return date into char format.

If you want to get date into date format just declare the date type variable then assign the sysdate value INTO that variable.Then use DBMS_OUTPUT.PUT_LINE(variable) to print the DATE.

Answer 3

If you wanted to do it in one line, you could also use:

CREATE OR replace PROCEDURE Test_procedure
IS
BEGIN
  dbms_output.put_line(to_char(sysdate, 'MM/DD/YY'));
END;

Answer 4

In the first place I don't need to use that INTO Oracle is insisting for me to do.

The fact is, Oracle is correct: you do need to use an INTO to take the return value.

After all, if you want to display the result of the query you're going to need a variable to put it in first.

Answer 5

Finally found a solution to the output I want (based on your responses) :D

CREATE OR REPLACE 
PROCEDURE "TEST_PROCEDURE"
RET_DATE CHAR(10);
BEGIN
    SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY') INTO RET_DATE FROM DUAL;
    DBMS_OUTPUT.PUT_LINE(RET_DATE);
END;

This displays the SYSDATE with format of MM/DD/YYYY. Still, thanks for the replies and ideas (mostly @Jeffrey Kemp). Oracle just lengthens what MS SQL can normally do in one line :D