3 Replies Latest reply: Nov 15, 2012 7:26 AM by DBA112 RSS

    DML in PL/SQL

    DBA112
      Dear Experts,

      I need to update table using PL/SQL procedure, I've built following procedure. The updates are run on date columns and follow format - DD-MON-RRRR HH24:MI:SS.

      Any suggestions on how do I retain this format, can I input in the format when I'm entring the date values, If so, could you kindly suggest what's the modification?
      CREATE OR REPLACE PROCEDURE USER1.UPDATE_PROC(STP_ACT_CMPL_DTTM_1 IN DATE, ACSL_SRV_ACT_END_DTTM_1 IN DATE, ACSL_MNTR_STP_ID_1 IN NUMBER)
      IS
      BEGIN
      UPDATE SNIOTM.ACSL_MNTR_STP 
      SET STP_ACT_CMPL_DTTM = TO_DATE(STP_ACT_CMPL_DTTM_1, 'DD-MON-RRRR HH24:MI:SS'),
      ACSL_SRV_ACT_END_DTTM = TO_DATE(ACSL_SRV_ACT_END_DTTM_1,'DD-MON-RRRR HH24:MI:SS')
      WHERE ACSL_MNTR_STP_ID = ACSL_MNTR_STP_ID_1;
      dbms_output.put_line(SQL%ROWCOUNT||' row(s) updated');
      END ACCESSORIAL_UPDATE_PROC;
      /
      
      EXEC USER1.UPDATE_PROC('&STP_ACT_CMPL_DTTM_1', '&ACSL_SRV_ACT_END_DTTM_1', &ACSL_MNTR_STP_ID_1);
        • 1. Re: DML in PL/SQL
          jeneesh
          Date columns are stored in byts - dont have any format. Formats are used only for display purpose, not for storage.
          You could pass date itself to the procedure...
          CREATE OR REPLACE PROCEDURE USER1.UPDATE_PROC
          (
           STP_ACT_CMPL_DTTM_1 IN DATE, 
           ACSL_SRV_ACT_END_DTTM_1 IN DATE, 
           ACSL_MNTR_STP_ID_1 IN NUMBER
          )
          IS
          BEGIN
          UPDATE SNIOTM.ACSL_MNTR_STP 
          --"Remove TO_DATE as the colums are already date datatype"
          SET STP_ACT_CMPL_DTTM = STP_ACT_CMPL_DTTM_1,
          ACSL_SRV_ACT_END_DTTM = ACSL_SRV_ACT_END_DTTM_1
          WHERE ACSL_MNTR_STP_ID = ACSL_MNTR_STP_ID_1;
          dbms_output.put_line(SQL%ROWCOUNT||' row(s) updated');
          END ACCESSORIAL_UPDATE_PROC;
          / 
          --"Pass date type arguments in the procedure call.
          EXEC USER1.UPDATE_PROC(to_date('&STP_ACT_CMPL_DTTM_1', 'DD-MON-RRRR HH24:MI:SS'),to_date('&ACSL_SRV_ACT_END_DTTM_1', 'DD-MON-RRRR HH24:MI:SS'),&ACSL_MNTR_STP_ID_1);
          {code}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          • 2. Re: DML in PL/SQL
            Purvesh K
            Ora DBA wrote:
            CREATE OR REPLACE PROCEDURE USER1.UPDATE_PROC(STP_ACT_CMPL_DTTM_1 IN DATE, ACSL_SRV_ACT_END_DTTM_1 IN DATE, ACSL_MNTR_STP_ID_1 IN NUMBER)
            IS
            BEGIN
            UPDATE SNIOTM.ACSL_MNTR_STP
            SET STP_ACT_CMPL_DTTM = TO_DATE(STP_ACT_CMPL_DTTM_1, 'DD-MON-RRRR HH24:MI:SS'),
            ACSL_SRV_ACT_END_DTTM = TO_DATE(ACSL_SRV_ACT_END_DTTM_1,'DD-MON-RRRR HH24:MI:SS')
            WHERE ACSL_MNTR_STP_ID = ACSL_MNTR_STP_ID_1;
            dbms_output.put_line(SQL%ROWCOUNT||' row(s) updated');
            END ACCESSORIAL_UPDATE_PROC;
            /

            EXEC USER1.UPDATE_PROC('&STP_ACT_CMPL_DTTM_1', '&ACSL_SRV_ACT_END_DTTM_1', &ACSL_MNTR_STP_ID_1);
            STP_ACT_CMPL_DTTM_1, ACSL_SRV_ACT_END_DTTM_1 these are already being passed as Date datatype, so why do you need to use a TO_DATE on it again?
            Just to preserve the Time details?

            If yes, you must read Oracle Date Datatype.

            Date datatype always stores, Time information.

            See the below demo:
            create table test_table
            (
              col1      date,
              col2      date
            );
            
            insert into test_table values (sysdate, trunc(sysdate));
            
            select to_char(col1, 'DD-MON-YYYY HH24:MI:SS') col1, to_char(col2, 'DD-MON-YYYY HH24:MI:SS') col2
              from test_table;
            
            COL1                          COL2                          
            ----------------------------- ----------------------------- 
            15-NOV-2012 13:00:31          15-NOV-2012 00:00:00
            If, while updating, your variable, STP_ACT_CMPL_DTTM_1 as in this case, contains time information, it will automatically be stored into the Column. You need not do any extra efforts to ensure this.

            Hence, I suggest you remove the TO_DATE functions.
            • 3. Re: DML in PL/SQL
              DBA112
              Thanks Buddy.