Oracle Procedure Error (PLS-00428)

Question

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

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

First of all, I do not need to use this INTO Oracle, which insists that I do this. Is there any other way to use the cursor (I saw it here https://stackoverflow.com/a/167385/ ... )? I think that this should not be so, it works fine, as in MS SQL without using INTO or cursor.

Answer 1

Finally found the output solution I want (based on your answers): 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; 

SYSDATE displayed with the format MM / DD / YYYY. However, thanks for the answers and ideas (mostly @Jeffrey Kemp). Oracle simply extends what MS SQL can usually do on a single line: D

Answer 2

First of all, I do not need to use this INTO Oracle, which insists for me.

The fact is that Oracle is correct: you need to use INTO to get the return value.

In the end, if you want to display the result of a query, you will need a variable to put it first.

Answer 3

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 must 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

defined by your NLS_DATE_FORMAT parameter, or you can be explicit like to_char(v_date, 'dd-mm-yyyy') , etc.

Answer 4

You can use as

 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 the date in char format.

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

Answer 5

If you want to do this on one line, you can also use:

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