how to pass date in sp as parameter [message #36652] |
Mon, 10 December 2001 07:48 |
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
----------------------------------------------------------------------
|
|
|
|
|