Environment: Oracle 10g,11g
Problem recurrence: Under the Command window in PL/SQL, the time format found by the stored procedure does not meet the expected requirements.
Sql> SelectSysdate fromdual; Sysdate----------- the- A- -Executedinch 0Secondssql> SetServeroutput onSQL> Declare2pro_date date;3 begin4 SelectSysdate intoPro_date fromdual;5Dbms_output.put_line (pro_date);6 End;7 / --December- -PL/Sqlproceduresuccessfully completedexecutedinch 0.016Seconds
Process Method 1: Convert the result to a string format:
Sql>SQL> Declare2pro_date date;3 begin4 SelectSysdate intoPro_date fromdual;5Dbms_output.put_line (To_char (Pro_date,'YYYY-MM-DD'));6 End;7 / the- A- -PL/Sqlproceduresuccessfully completedexecutedinch 0.016Secondssql>SQL> Declare2pro_date date;3 begin4 SelectSysdate intoPro_date fromdual;5Dbms_output.put_line (pro_date);6 End;7 / --December- -PL/Sqlproceduresuccessfully completedexecutedinch 0Seconds
Treatment Method 2: Change the Nls_date_format of the session
Sql> AlterSessionSetNls_date_format='YYYY-MM-DD Hh24:mi:ss'; Session alteredexecutedinch 0.015Secondssql>SQL> Declare2pro_date date;3 begin4 SelectSysdate intoPro_date fromdual;5Dbms_output.put_line (pro_date);6 End;7 / the- A- - One: -: thePL/Sqlproceduresuccessfully completedexecutedinch 0Secondssql> AlterSessionSetNls_date_format='YYYY-MM-DD'; Session alteredexecutedinch 0Secondssql>SQL> Declare2pro_date date;3 begin4 SelectSysdate intoPro_date fromdual;5Dbms_output.put_line (pro_date);6 End;7 / the- A- -PL/Sqlproceduresuccessfully completedexecutedinch 0Seconds
Summary: in Oracle stored procedures want to get YYYY-MM-DD time format, can be converted to string processing, you can temporarily specify the Nls_date_format variable of the session, you can also modify the client's environment variables as a whole.
Oracle stored procedure gets the YYYY-MM-DD time format