Search code examples
sqloracle-databasedateprocedure

Oracle procedure saving SYSDATE

As part of a larger procedure I'm trying to write some code, which saves SYSDATE into a variable but I'm running into a problem. I narrowed it down to the following. Can someone please help me out.

CREATE OR REPLACE PROCEDURE XXX
AS
  l_date_string VARCHAR2(50);
BEGIN

select into l_date_string '['||TO_CHAR (SYSDATE, 'MMDDYYYY-HH24:MI:SS')||']' from dual;
 
END;
/
Solution

  • In PL\SQL you can just assign:

    BEGIN

...

  l_date_string := '[' || TO_CHAR (SYSDATE, 'MMDDYYYY-HH24:MI:SS') || ']'; 

...

END;

    if you insist on sql all you have to do is to add into after select:

    BEGIN

...

  -- standard select .. from .. with additional "into"  
  select '[' || TO_CHAR (SYSDATE, 'MMDDYYYY-HH24:MI:SS') || ']'
    into l_date_string 
    from dual;

...

END;