Home » SQL & PL/SQL » SQL & PL/SQL » Code works but don't like extra burden when calling procedure.
Code works but don't like extra burden when calling procedure. [message #38514] Wed, 24 April 2002 10:26 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Sorry for repeated message, not done by me. [message #38529 is a reply to message #38514] Wed, 24 April 2002 16:22 Go to previous messageGo to next message
Sid
Messages: 38
Registered: May 1999
Member
Sorry for repeated message update.
This was done by the young one playing with the keyboard
Re: Code works but don't like extra burden when calling procedure. [message #38539 is a reply to message #38514] Thu, 25 April 2002 09:31 Go to previous message
Rick Cale
Messages: 111
Registered: February 2002
Senior Member
Could you pass the date in as a varchar2 instead of
date and use formatting inside of stored procedure?
Previous Topic: zero divide error...
Next Topic: how to generate sequence number?
Goto Forum:
  


Current Time: Fri Mar 29 02:02:04 CDT 2024