Home » SQL & PL/SQL » SQL & PL/SQL » how to pass date in sp as parameter
how to pass date in sp as parameter [message #36652] Mon, 10 December 2001 07:48 Go to next message
George
Messages: 68
Registered: April 2001
Member
Hi,

Could anybody know how to pass a parameter of type date into a sp? Below are something I tried:
1.
create or replace package empTest AS
PRAGMA SERIALLY_REUSABLE;
TYPE curType IS REF CURSOR;
function Details (
hd date)
RETURN curType;
END;
/
CREATE OR REPLACE PACKAGE BODY empTest AS
PRAGMA SERIALLY_REUSABLE;
function Details (
hd date)
RETURN curType IS
sum_cv curType;
err_num NUMBER;
err_msg VARCHAR2(200);
sql_statement VARCHAR2(3000);
BEGIN
DBMS_OUTPUT.PUT_LINE(hd);
sql_statement :=' SELECT * from emp where hiredate < to_date(''hd'', ''Dd-Mon-YY'') ';
DBMS_OUTPUT.PUT_LINE(sql_statement );
OPEN sum_cv FOR sql_statement ;
IF NOT sum_cv%ISOPEN THEN
OPEN sum_cv FOR sql_statement;
END IF;
RETURN sum_cv;
END Details ;
END;

RUN EXEC :CV := empTest.Details('10-DEC-01');
ORA-01858: a non-numeric character was found where a numeric was expected

2. sql_statement :=' SELECT * from emp where hiredate < to_date(hd) ';
Run in the same way:
ORA-00904: invalid column name

3. sql_statement :=' SELECT * from emp where hiredate < to_date(''hd'') ';
Run in the same way:
ORA-01858: a non-numeric character was found where a numeric was expected

Thanks

----------------------------------------------------------------------
Re: how to pass date in sp as parameter [message #36654 is a reply to message #36652] Mon, 10 December 2001 08:31 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
why you are converting hd parameter to date inside function,its already date.
sql_statement :=' SELECT * from emp where hiredate < '||''''||hd||'''';

----------------------------------------------------------------------
Re: how to pass date in sp as parameter [message #36656 is a reply to message #36652] Mon, 10 December 2001 08:35 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Since your input parameter is already of type date, there is no need to run it through the to_date conversion.

----------------------------------------------------------------------
Previous Topic: Trigger to display a message at Logon
Next Topic: HELP! Compatibility problem?
Goto Forum:
  


Current Time: Thu Mar 28 12:38:09 CDT 2024