save date and time in variable through a select query-CodePudding

friends,

I have a store procedure where I need to validate the date and time with respect to the date and time of another table.

How can I pass the date and time of a select * as a variable, just like this one only passes the date but the hours and minutes are lost.

the field is validated if it saves the date and time in the two tables.

thanks for any help.

PROCEDURE SPU_CUENTA
(
p_nro in varchar2,
   pr_Ret OUT number
) is
 vfecha date;
 vcount int;
begin


select COUNT(DFEC_SISTEMA) into vcount from TAB Where c=1;

IF vcount>0 THEN 
select DFEC_SISTEMA into vfecha from TAB Where c=1;

   EXECUTE IMMEDIATE  'SELECT COUNT(DFEC_ANULA) FROM tablab WHERE DFEC_ANULA>'''||vfecha||'''' into pr_Ret;
   
   END IF;

end;

CodePudding user response：

Why are you using EXECUTE IMMEDIATE? Why not this:

SELECT COUNT(DFEC_ANULA) into pr_Ret FROM tablab WHERE DFEC_ANULA > vfecha;

or combine everything into one query:

SELECT COUNT(DFEC_ANULA) into pr_Ret 
  FROM tablab 
 WHERE DFEC_ANULA > (select DFEC_SISTEMA from TAB Where c=1);

CodePudding user response：

In your current dynamic statement, where you have:

EXECUTE IMMEDIATE  'SELECT ... WHERE DFEC_ANULA>'''||vfecha||'''' into pr_Ret

you are concatenating in the string representation of your date variable. If your NLS_DATE_FORMAT is set to DD-MON-RR (and your date language is English) then for today's date that would evaluate to:

EXECUTE IMMEDIATE  'SELECT ... WHERE DFEC_ANULA>'''|| '02-MAR-22' ||'''' into pr_Ret

which is:

EXECUTE IMMEDIATE  'SELECT ... WHERE DFEC_ANULA>''02-MAR-22''' into pr_Ret

When that statement is parsed you're then relying on implicit conversion again, so:

SELECT ... WHERE DFEC_ANULA>'02-MAR-22'

SELECT ... WHERE DFEC_ANULA>TO_DATE('02-MAR-22')

which also then uses your NLS_DATE_FORMAT.

That's why "the hours and minutes are lost" - you are doing implicit conversions using a format model that ignores them, so you end up with final evaluated date value at midnight.

If you really wanted and needed to use dynamic SQL then you should use a bind variable:

EXECUTE IMMEDIATE  'SELECT COUNT(DFEC_ANULA) FROM tablab WHERE DFEC_ANULA>:vfecha'
  into pr_Ret
  using vfecha;

But as @pmdba already said, you don't need to use dynamic SQL for this at all - the query and all of the tables and columns are known at compile time, only a variable value changes at runtime; so just use static SQL:

SELECT COUNT(DFEC_ANULA) into pr_Ret FROM tablab WHERE DFEC_ANULA > vfecha;