Home » SQL & PL/SQL » SQL & PL/SQL » How to pass date parameters in dynamic sql? (PL sql)
How to pass date parameters in dynamic sql? [message #657931] Mon, 28 November 2016 08:29 Go to next message
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 #657933 is a reply to message #657931] Mon, 28 November 2016 08:34 Go to previous messageGo to next message
BlackSwan
Messages: 26204
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read


Re: How to pass date parameters in dynamic sql? [message #657935 is a reply to message #657931] Mon, 28 November 2016 08:46 Go to previous messageGo to next message
John Watson
Messages: 7620
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 Go to previous messageGo to next message
cookiemonster
Messages: 13291
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 #657937 is a reply to message #657936] Mon, 28 November 2016 10:09 Go to previous messageGo to next message
nukala
Messages: 2
Registered: November 2016
Junior Member
i have tried some different ways, but i didn't get the results.

Anybody have the same example on dynamic sql, Could you please send me.

Thanks in advance .

[Updated on: Mon, 28 November 2016 10:11]

Report message to a moderator

Re: How to pass date parameters in dynamic sql? [message #657938 is a reply to message #657937] Mon, 28 November 2016 10:11 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

EXECUTE IMMEDIATE

Re: How to pass date parameters in dynamic sql? [message #657940 is a reply to message #657937] Mon, 28 November 2016 10:17 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
Have you tried following my suggestion to see what the problem is?
It's pretty obvious, and once you've worked it out it really shouldn't be difficult to correct.
Re: How to pass date parameters in dynamic sql? [message #657949 is a reply to message #657931] Mon, 28 November 2016 17:37 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
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 Go to previous message
EdStevens
Messages: 985
Registered: September 2013
Senior Member
nukala wrote on Mon, 28 November 2016 08:29
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;
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/



Previous Topic: Function returns Record - How to select
Next Topic: Star printing in PL/SQL Program Problem
Goto Forum:
  


Current Time: Sun Oct 21 08:35:40 CDT 2018