Create a delimited field when using a between to_date Oracle sqlplus

Question

I am creating a csv file from a query using ! as a delimiter (I did not create the query and I cannot modify the delimiter).

I have to modify the select statement to use a between to_date but my procedure is dying. The query is assigned to a variable which is then passed into the procedure that is used to create the csv file.

I believe the error is happening because I'm delimiting the two dates when there is only supposed to be a single date column

This is the original code that works using a single date

CREATE OR REPLACE PROCEDURE my_procedure(
  
    p_column1       IN               VARCHAR2
    ,p_column2   IN               VARCHAR2
)AS

v_query   VARCHAR2(5000);
v_column1 VARCHAR2(10);
v_column2 VARCHAR2(10);


v_column1 := p_column1;
v_column2 := p_column2;

v_query := q'!
SELECT column1
FROM mytable
WHERE 
column1= TO_DATE('!'
               || v_date
               || q'!','yyyy-mm-dd')!';
END;

This is the code I am currently trying to use

CREATE OR REPLACE PROCEDURE my_procedure(
  
    p_column1       IN               VARCHAR2
    ,p_column2   IN               VARCHAR2
)AS
v_query   VARCHAR2(5000);
v_column1 VARCHAR2(10);
v_column2 VARCHAR2(10);


v_column1 := p_column1;
v_column2 := p_column2;

v_query := q'!
SELECT column1
FROM mytable
where
 column1 BETWEEN TO_DATE('!' ||v_date || q'!,'yyyy-mm-dd')
        AND TO_DATE( '!' ||v_end_date || q'!,'yyyy-mm-dd')!';
END;

How can I delimit the between to_date to be one column?

Answer 1

You've lost the closing quote after the v_date and v_end_date values:

where
 column1 BETWEEN TO_DATE('!' ||v_date || q'!','yyyy-mm-dd')
--------------------------------------------^
        AND TO_DATE( '!' ||v_end_date || q'!','yyyy-mm-dd')!';
--------------------------------------------^

You can debug problems like this by doing:

dbms_output.put_line(v_query);

With a random date value that shows your original query as:

SELECT column1
FROM
 mytable
where
column1= TO_DATE('2011-12-13','yyyy-mm-dd')

and your modified one as:

SELECT column1
FROM
 mytable
where
 column1 BETWEEN TO_DATE('2011-12-13,'yyyy-mm-dd')
        AND TO_DATE( '2012-01-02,'yyyy-mm-dd')

which makes the missing quotes in both to_date() calls more obvious than they are during construction. (And trying to execute that gets ORA-00907: missing right parenthesis.)

With my modification that now comes out as:

SELECT column1
FROM
 mytable
where
 column1 BETWEEN TO_DATE('2011-12-13','yyyy-mm-dd')
        AND TO_DATE( '2012-01-02','yyyy-mm-dd')

which looks more reasonable.

---

Hopefully those v_* variables are strings. If they are dates then you should not be relying on NLS sessions to convert them to dates. Either way you should probably be using bind variablesm then supplying the actual values when you execute the dynamic query - though it isn't obvious why this is dynamic, so perhaps that constructed query is being passed back for something else to run.