I am running a unix *_sh script but with methods that use pl/sql in it.
Method in the script:
method(){
sqlplus -s ${DB_CONNECTION} << EOF
set echo on;
set timing on;
set serveroutput on;
DECLARE
v_monthNow Date:=to_date('2018-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
v_charMonthNow Varchar(5):=to_char(v_monthNow,'MM');
v_charMonthBefore Varchar(5) :=to_char(v_monthNow, 'MM')-1;
BEGIN
dbms_output.Put_line('SYSDATE is '|| v_monthNow ||' || MonthNow is '|| v_charMonthNow);
IF v_charMonthBefore = '0'
set v_charMonthBefore = '12'
dbms_output.Put_line('MonthBefore is '|| v_charMonthBefore);
ELSE
dbms_output.Put_line('MonthBefore is '|| v_charMonthBefore);
END;
/
EOF
}
My question is: how do I set value of v_charMonthBefore to what I want?
Actually if sysdate is Jan 2018, i need v_monthNow=1 and v_charMonthBefore=12
if sysdate is Feb 2018, i need v_monthNow=2 and v_charMonthBefore=1
Other way to do this is not required, i just need to fix what i have now as i am not really familiar with pl/sql. Thank you
Aucun commentaire:
Enregistrer un commentaire