Code works but don't like extra burden when calling procedure. [message #38514] |
Wed, 24 April 2002 10:26 |
Sid
Messages: 38 Registered: May 1999
|
Member |
|
|
I want to find out whether an employee's hiredate is within the following range:
{sysdate -50 years, sysdate + 3 months }. Below is a working solution:
SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE chk_hiredate( v_hiredate IN emp.hiredate%TYPE)
IS
v_constant_date50yrs NUMBER := -600 ;
e_invalid_hiredate EXCEPTION;
BEGIN
IF v_hiredate IS NULL THEN
RAISE e_invalid_hiredate;
ELSIF v_hiredate between add_months(sysdate,v_constant_date50yrs) and add_months(sysdate,3)
THEN
DBMS_OUTPUT.PUT_LINE('The hiredate ' || v_hiredate || ' falls between the years ' ||
to_char(add_months(sysdate,v_constant_date50yrs), 'DD-MM-YYYY') || ' and ' ||
to_char(add_months(sysdate,3) , 'DD-MM-YYYY'));
ELSE
RAISE_APPLICATION_ERROR(-20001, 'The hiredate ' || v_hiredate || ' DOES NOT falls between the
years ' || to_char(add_months(sysdate,v_constant_date50yrs), 'DD-MM-YYYY') || ' and ' ||
to_char(add_months(sysdate,3), 'DD-MM-YYYY') );
END IF;
EXCEPTION
WHEN e_invalid_hiredate THEN
DBMS_OUTPUT.PUT_LINE('Please enter a ligitimate hiredate ' );
END chk_hiredate;
/
The problem is the way I have to call this procedure to make it work, since the code thinks that 27-NOV-81 is 27-NOV-2081, not 27-NOV-1981.
EXECUTE chk_hiredate (TO_DATE ('27-NOV-1981', 'DD-MON-YYYY'));
I have tried modifying the code but it doesn’t work, note the code is abbreviated
CREATE OR REPLACE PROCEDURE chk_hiredate( v_hiredate IN DATE)
IS
v_constant_date50yrs NUMBER := -600 ;
….
END chk_hiredate;
/
Now to test:
SQL> EXECUTE chk_hiredate('27-NOV-1981');
begin chk_hiredate('27-NOV-1981'); end;
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at line 1
SQL> show error
Errors for PROCEDURE CHK_HIREDATE:
Have also tried the code below but this compiles with errors as well:
SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE chk_hiredate( v_hiredate IN date 'dd-mm-yyyy')
…
END chk_hiredate;
/
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/44 PLS-00103: Encountered the symbol "dd-mm-yyyy" when expecting one
of the following:
:= . ) , @ % default
The symbol ":=" was substituted for "dd-mm-yyyy" to continue.
|
|
|
Re: Code works but don't like extra burden when calling procedure. [message #38517 is a reply to message #38514] |
Wed, 24 April 2002 11:36 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
If you do not want to include the to_date conversion on your call, then your setting for nls_date_format must match your string.
First, check this value:
select value
from nls_session_parameters
where parameter = 'NLS_DATE_FORMAT';
If it does not match the DD-MON-YYYY format (which is how you are trying to call it), then:
alter session set nls_date_format = 'DD-MON-YYYY'
Even so, I would strongly suggest using the to_date conversion on the call - that way you are not reliant on an environment setting.
|
|
|
Re: Code works but don't like extra burden when calling procedure. [message #38519 is a reply to message #38514] |
Wed, 24 April 2002 11:41 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
If you do not want to include the to_date conversion on your call, then your setting for nls_date_format must match your string.
First, check this value:
select value
from nls_session_parameters
where parameter = 'NLS_DATE_FORMAT';
If it does not match the DD-MON-YYYY format (which is how you are trying to call it), then:
alter session set nls_date_format = 'DD-MON-YYYY'
Even so, I would strongly suggest using the to_date conversion on the call - that way you are not reliant on an environment setting.
|
|
|
have included varchar but it gives error at line 8 [message #38527 is a reply to message #38514] |
Wed, 24 April 2002 15:40 |
Sid
Messages: 38 Registered: May 1999
|
Member |
|
|
SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE chk_hiredate( v_date IN varchar2)
IS
v_constant_date50yrs NUMBER := -600 ;
e_invalid_hiredate EXCEPTION;
BEGIN
IF v_date IS NULL THEN
RAISE e_invalid_hiredate;
ELSIF TO_DATE( v_date, 'DD-MM-YYYY') between add_months(sysdate,v_constant_date50yrs) and
add_months(sysdate,3) THEN
DBMS_OUTPUT.PUT_LINE('The hiredate ' || v_date || ' falls between the years ' ||
to_char(add_months(sysdate,v_constant_date50yrs), 'DD-MM-YYYY') || ' and ' ||
to_char(add_months(sysdate,3) , 'DD-MM-YYYY'));
ELSE
RAISE_APPLICATION_ERROR(-20001, 'The hiredate ' || v_date || ' DOES NOT falls
between the years ' || to_char(add_months(sysdate,v_constant_date50yrs), 'DD-MM-YYYY') || '
and ' || to_char(add_months(sysdate,3), 'DD-MM-YYYY') );
END IF;
EXCEPTION
WHEN e_invalid_hiredate THEN
DBMS_OUTPUT.PUT_LINE('Please enter a legitimate hire date ' );
END chk_hiredate;
/
begin chk_hiredate('27-NOV-1981'); end;
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "SCOTT.CHK_HIREDATE", line 8
ORA-06512: at line 1
|
|
|
|
|