如何在存储过程和语句之间传递日期参数？

Question

I did this statement and it's ok

SELECT a, b 
FROM T_COMMENTS 
WHERE DATE_COMMENTS BETWEEN DATE '2014-12-05' AND DATE '2017-05-16'

But I wanted to create a stored procedure and there is a mistake:

CREATE OR REPLACE PROCEDURE FACEBOOK.PR_SEL_ALL_COM_PH
(CONSULTA OUT SYS_REFCURSOR,COD_US INT,INDATE1 IN VARCHAR2, INDATE2 IN VARCHAR2) IS
BEGIN
    OPEN CONSULTA FOR SELECT a,b FROM T_COMMENTS WHERE DATE_COMMENTS BETWEEN DATE INDATE1 AND DATE INDATE2;
END;
/

What would be the problem in my stored procedure, thanks in advance. Update 1: I'm using php. When I push date for indate1 and indate2 appears this exception

Warning: oci_execute() [function.oci-execute]: ORA-01861: literal does not match format string

How can I solve that? Thanks in advance.

Answer 1

Pass dates as dates:

create or replace procedure facebook.pr_sel_all_com_ph
    ( consulta out sys_refcursor
    , cod_us int
    , indate1 in date
    , indate2 in date )
is
begin
    open consulta for
        select a,b
        from   t_comments
        where  date_comments between indate1 and indate2;
end;
/

If for some reason you absolutely have to pass them as strings, you will need to convert these to actual dates within the procedure using to_date(indate1,'YYYY-MM-DD'), although of course this gives your procedure a whole extra validation task. (How will the caller know what format to use? What if they pass them in the wrong format? How much should the procedure check the format prior to executing the query? and so on. It's a huge pain and not really the procedure's job.)

The reason date indate1 fails is that date literals only work with literal quoted text, not variables.