PLSQL-0043 - cannot be v_day into select / fetch-CodePudding

I am trying to write a PLSQL code to get current day name using PLSQL and assigning it to variable

Code

CREATE OR REPLACE PROCEDURE ABC_STORE
(
    v_day varchar2
)
AS
BEGIN
     select to_char(CURRENT_TIMESTAMP,'DAY') into v_day from dual;
     DBMS_OUTPUT.PUT_LINE(v_day);
END;

Error :

CodePudding user response：

You should add al least clause OUT for value v_day:

create or replace PROCEDURE ABC_STORE
(
    v_day IN OUT varchar2
)
AS
BEGIN
     select to_char(CURRENT_TIMESTAMP,'DAY') into v_day from dual;
     DBMS_OUTPUT.PUT_LINE(v_day);
END;

Thank you

CodePudding user response：

You don't need the query, because PL/SQL has an assignment operator :=.

You also don't need dbms_output if the requirement is just to bring back the day name.

Also, the 'DAY' format element includes blank padding by default (e.g. SUNDAY ), so 'fmDAY' provides the expected result SUNDAY. If it doesn't need to be all capitals, you can use 'fmDay'.

create or replace procedure day_name
    ( v_day out varchar2 )
as
begin
    v_day := to_char(sysdate,'fmDay');
end day_name;

Just to add, if you are checking the day name as part of some business logic (for example, a batch step should not run at the weekend), you will also need to fix a date language to avoid the situation where you are checking for 'Sunday' and the procedure is called from a support desk in Paris and returns 'Dimanche'. You would do this using (for example - substitute any language you want)

to_char(sysdate,'fmDay','nls_date_language=English')

(Of course, if you were just checking for the weekend you would only need to get the English three letter abbreviation and check whether it's like 'S%', but this is not what you asked for. I mention it because I have seen production errors caused by poorly handled day name checks, and as a result I use it as an interview question, which it turns out very few candidates can answer.)