How to pass date parameters in dynamic sql? [message #657931] |
Mon, 28 November 2016 08:29 |
|
nukala
Messages: 2 Registered: November 2016
|
Junior Member |
|
|
How to pass date parameters in dynamic sql?
set serveroutput on;
declare
rec VARCHAR2(20);
v_query VARCHAR2(4000);
v_clm_actvty_from_dt date;
v_clm_actvty_to_dt date;
BEGIN
v_clm_actvty_from_dt := '10-JAN-1982';
v_clm_actvty_to_dt := '30-MAR-1989';
v_query := 'select count(1) from emp
where hiredate between '''
|| TO_CHAR(v_clm_actvty_from_dt,'YYYY-MM-DD') ||' and '||TO_CHAR(v_clm_actvty_to_dt,'YYYY-MM-DD')
||'''';
EXECUTE IMMEDIATE v_query INTO rec;
DBMS_OUTPUT.PUT_LINE('Rec: ' || rec);
END;
Getting error "Missing Keyword". I tried different ways didn't get. Could you please someone help on this?
|
|
|
|
Re: How to pass date parameters in dynamic sql? [message #657935 is a reply to message #657931] |
Mon, 28 November 2016 08:46 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I get a different error:orclz> set serveroutput on;
orclz> declare
2 rec VARCHAR2(20);
3 v_query VARCHAR2(4000);
4 v_clm_actvty_from_dt date;
5 v_clm_actvty_to_dt date;
6 BEGIN
7
8 v_clm_actvty_from_dt := '10-JAN-1982';
9
10 v_clm_actvty_to_dt := '30-MAR-1989';
11
12 v_query := 'select count(1) from emp
13 where hiredate between '''
14 || TO_CHAR(v_clm_actvty_from_dt,'YYYY-MM-DD') ||' and '||TO_CHAR(v_clm_actvty_to_dt,'YYYY-MM-DD')
15 ||'''';
16
17 EXECUTE IMMEDIATE v_query INTO rec;
18 DBMS_OUTPUT.PUT_LINE('Rec: ' || rec);
19
20 END;
21 /
declare
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 8
orclz>
You are confusing your data types and relying on implicit type casting. You need to be clear on whether variables and columns are strings or dates.
|
|
|
Re: How to pass date parameters in dynamic sql? [message #657936 is a reply to message #657935] |
Mon, 28 November 2016 09:17 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Whenever you have problems with dynamic SQL you should assign the dynamic string to a variable and output the string so you can see what you constructed (you can use dbms_output for this). If you can't spot the problem straight away copy and paste the string into sqlplus and that'll show where you're going wrong.
That said - there is zero reason for your example to be dynamic at all, so why are you bothering, unless this is a learning excercise. In which case you should probably learn how to use bind variables with execute immediate - have a look in the documentation.
|
|
|
|
|
|
Re: How to pass date parameters in dynamic sql? [message #657949 is a reply to message #657931] |
Mon, 28 November 2016 17:37 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
You completely misunderstand dates. You use dynamic SQL with hardcoded values instead of bind variables. Start from reading docs. Anyway:
SQL> set serveroutput on;
SQL> declare
2 v_rec number; -- you are selecting count into rec, so why declaring it as string? changing to number.
3 v_query VARCHAR2(4000);
4 v_clm_actvty_from_dt date;
5 v_clm_actvty_to_dt date;
6 begin
7 -- v_clm_actvty_from_dt := '10-JAN-1982'; -- '10-JAN-1982' is string, not date
8 -- v_clm_actvty_to_dt := '30-MAR-1989'; -- '30-MAR-1989' is string, not date
9 v_clm_actvty_from_dt := date '1982-01-10'; -- read docs about date literals
10 v_clm_actvty_to_dt := date '1989-03-30'; -- read docs about date literals
11
12 v_query := 'select count(1) from emp where hiredate between :from_date and :to_date'; -- use bind variables
13 execute immediate v_query
14 into v_rec
15 using v_clm_actvty_from_dt,
16 v_clm_actvty_to_dt;
17 dbms_output.put_line('Rec: ' || v_rec);
18 end;
19 /
Rec: 3
PL/SQL procedure successfully completed.
SQL>
SY.
|
|
|
Re: How to pass date parameters in dynamic sql? [message #657966 is a reply to message #657931] |
Tue, 29 November 2016 07:38 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
nukala wrote on Mon, 28 November 2016 08:29How to pass date parameters in dynamic sql?
set serveroutput on;
declare
rec VARCHAR2(20);
v_query VARCHAR2(4000);
v_clm_actvty_from_dt date;
v_clm_actvty_to_dt date;
Here you declare two variables as DATE data type. However . . .
Quote:
BEGIN
v_clm_actvty_from_dt := '10-JAN-1982';
v_clm_actvty_to_dt := '30-MAR-1989';
Here you assign STRING values to those DATE variables, forcing oracle to do an implicit TO_DATE, and possibly failing due to an incorrect format of the string vs the setting of NLS_DATE_FORMAT.
Quote:
v_query := 'select count(1) from emp
where hiredate between '''
|| TO_CHAR(v_clm_actvty_from_dt,'YYYY-MM-DD') ||' and '||TO_CHAR(v_clm_actvty_to_dt,'YYYY-MM-DD')
||'''';
and here you unnecessarily force those DATE variables back to a string before doing a "date" comparison. You really, really, REALLY need to get a better understanding of DATEs vs strings that just look like dates. See http://edstevensdba.com/oracle-data-types/understanding-oracle-date-formats/
|
|
|