CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
LearnOracle
LearnOracle

Reputation: 43

How to update a date column with sysdate including timestamp in a dynamic sql

    CREATE OR REPLACE PROCEDURE TESTPROC
IS
   l_update_str   VARCHAR2 (4000);
BEGIN
   l_update_str :=
      'UPDATE XX_TABLE 
             last_update_date = ''' || SYSDATE || ''',
       WHERE     1=1';

   EXECUTE IMMEDIATE l_update_str;
END;
/

I have tried above but it is not working. I have aslo tried using to_date(to_char(sysate)) but the time stamp is missing

Upvotes: 4

Views: 91657

Answers (2)

PLSQL_007
PLSQL_007

Reputation: 213

Simply put systimestamp to set the timestamp in the column.

CREATE OR REPLACE PROCEDURE TESTPROC
IS
   l_update_str   VARCHAR2 (4000);
BEGIN
   l_update_str :=
      'UPDATE test1
set name1 = SYSTIMESTAMP(6)
where 1=1';

   EXECUTE IMMEDIATE l_update_str;
END;

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49102

I have aslo tried using to_date(to_char(sysate)) but the time stamp is missing

Makes no sense to do that.

Why PL/SQL for such a trivial task. Do it in plain SQL. The simple syntax for an UPDATE statement is -

UPDATE table_name SET column_name = SYSDATE WHERE <conditions>

To display the date column, simply use TO_CHAR along with proper FORMAT MODEL.

SELECT TO_CHAR(column_name, 'MM/DD/YYYY HH24:MI:SS') FROM table_name;

For example,

SQL> create table t(a date);

Table created.

SQL> insert into t values(sysdate - 10);

1 row created.

SQL> select * from t;

A
---------
25-JAN-15

SQL> update t set a = sysdate;

1 row updated.

SQL> select to_char(a, 'mm/dd/yyyy hh24:mi:ss') dt from t;

DT
-------------------
02/04/2015 11:29:21

SQL>

UPDATE Regarding dynamic SQL.

'UPDATE XX_TABLE last_update_date = ''' || SYSDATE || ''', WHERE 1=1';

  • Firstly, the update syntax is wrong. The SET keyword is missing.

  • Secondly, you can't use SYSDATE like that in dynamic SQL.

    Since you are forming the dynamic sql, the sysdate value is appended to the SQL. The best way to test any dynamic sql is to use DBMS_OUTPUT and see whether the dynamic SQL is properly formed.

For example,

SQL> CREATE OR REPLACE
  2  PROCEDURE TESTPROC
  3  IS
  4    l_update_str VARCHAR2 (4000);
  5  BEGIN
  6    l_update_str := 'UPDATE t
  7  SET a = ' || SYSDATE;
  8    --EXECUTE IMMEDIATE l_update_str;
  9
 10    DBMS_OUTPUT.PUT_LINE(l_update_str);
 11  END;
 12  /

Procedure created.

SQL> exec testproc;
UPDATE t
SET a = 02/04/2015 11:46:53

PL/SQL procedure successfully completed.

SQL>

So, do you see the SQL formed? The SYSDATE value is appended, instead of actually using the keyword SYSDATE.

So, you need to slightly change the dynamic sql.

SQL> CREATE OR REPLACE
  2  PROCEDURE TESTPROC
  3  IS
  4    l_update_str VARCHAR2 (4000);
  5  BEGIN
  6    l_update_str := 'UPDATE t
  7  SET a = SYSDATE';
  8    --EXECUTE IMMEDIATE l_update_str;
  9
 10    DBMS_OUTPUT.PUT_LINE(l_update_str);
 11  END;
 12  /

Procedure created.

SQL> exec testproc;
UPDATE t
SET a = SYSDATE

PL/SQL procedure successfully completed.

SQL>

Now the above would work fine.

Upvotes: 15

Related Questions