1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

package error in timestamp

Discussion in 'SQL PL/SQL' started by sowmyav, Dec 18, 2008.

  1. sowmyav

    sowmyav Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Hi,
    This is regarding the error in the package.Please check out the below script and I have error in data time stamp as I don't know what is to be given there.Pls help me on this.

    Once the merge is completed, I wanted to insert the contents in the Log table details 'Mo_Rpt_Tbl_Updt_log '.
    I am getting error on the evt_timestamp as I don't know what is to be given in that "dd-mon-yy......"

    Code (Text):
    SQL> desc Mo_Rpt_Tbl_Updt_log
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     TABLE_NAME                                NOT NULL VARCHAR2(32 CHAR)
     EVENT                                     NOT NULL VARCHAR2(100 CHAR)
     EVT_TIMESTAMP                             NOT NULL TIMESTAMP(6)
     RTN_CODE                                           VARCHAR2(50 CHAR)
     COMMENTS                                           VARCHAR2(255 CHAR)
     
    and the timestamp in the table is in the format 12-DEC-08 04.07.48.801466 PM
    Code (Text):

    SQL> CREATE OR REPLACE PACKAGE BODY PAC_1
      2    IS
      3   procedure test_merge
      4    is
      5    begin
      6    delete from sup1;
      7    select count(*) into T_count from sup1;
      8    DBMS_OUTPUT.PUT_LINE(' ROW COUNT IN THE TABLE TEST_MERGE BEFORE MERGE  '|| T_COUNT);
      9   merge into sup1 a
     10      using sup2 b
     11     on
     12       (a.ASUP_ID=b.ASUP_ID and
     13        a.EVT_GMT_SEC= b.EVT_GMT_SEC and a.SYS_SERIALNO=b.SYS_SERIALNO and
     14       a.SYS_MODEL=b.SYS_MODEL and a.SYS_TYPE=b.SYS_TYPE and a.DVC_SERIALNO=b.DVC_SERIALNO and a.DVC_MODEL=b.DVC_ModEL
     15       when matched then
     16      update set
     17       a.sys_version = b.sys_version,
     18       a.sys_top_domain = b.sys_top_domain,
     19       a.DVC_SIZE_GB =b.DVC_SIZE_GB,
     20       a.DVC_FW_REV = b.DVC_FW_REV,
     21       a.ASUP_LINK =b.ASUP_LINK,
     22       a.S_ISENSEKEY = b.S_ISENSEKEY,
     23       a.S_IASC =a.S_IASC,
     24      a.S_IASCQ =b.S_IASCQ,
     25       a.S_IFRU =b.S_IFRU
     26        when not matched then
     27       insert values (
     28       b.ASUP_ID,
     29        b.EVT_GMT_SEC,
     30        b.SYS_SERIALNO,
     31        b.SYS_MODEL,
     32        b.SYS_VERSION,
     33       b.SYS_TOP_DOMAIN,
     34        b.SYS_TYPE,
     35        b.DVC_SERIALNO,
     36        b.DVC_MODEL,
     37        b.DVC_SIZE_GB,
     38        b.DVC_FW_REV,
     39        b.ASUP_LINK,
     40        b.S_ISENSEKEY,
     41        b.S_IASC,
     42        b.S_IASCQ,
     43        b.S_IFRU);
     44     select count(*) into T_count from sup1;
     45     DBMS_OUTPUT.PUT_LINE(' ROW COUNT IN THE TABLE TEST_MERGE AFTER MERGE  '|| T_count);
     [COLOR="Red"]46        select to_char(sysdate,'DD-MON-yy HH:MI:SS AM') INTO EndTiME from dual;
     47     INSERT INTO Mo_Rpt_Tbl_Updt_log  VALUES ('SUP1','END','01','ENDTIME','COMMENTS FIELD');[/COLOR] 48   commit;
     49   end test_merge;
     50     end  PAC_1;
     51  /

    Package body created.

    SQL> exec pac_1.test_merge
    BEGIN pac_1.test_merge; END;

    *
    ERROR at line 1:
    ORA-01840: input value not long enough for date format
    ORA-06512: at "RELIDATA.PAC_1", line 47
    ORA-06512: at line 1


    SQL>
     
    Thanks in advance,
    Sowmya
     
  2. sowmyav

    sowmyav Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Error solved.As I did mistake in the insert statment.

    Thanks,
    Sowmya
     
  3. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    hmmm... glad u solved the problem
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    It is nice you solved it yourself.

    Btw, There is no need to format the sysdate before inserting in to timestamp field. You can insert it directly as below.

    Code (Text):

    SQL> DROP TABLE TEST;

    Table dropped.

    SQL> CREATE TABLE TEST ( TO_DAY TIMESTAMP );

    Table created.

    SQL> SELECT SYSDATE FROM DUAL;

    SYSDATE
    ---------
    19-DEC-08

    SQL> INSERT INTO TEST VALUES (SYSDATE);

    1 row created.

    SQL> SELECT  TO_DAY FROM TEST;

    TO_DAY
    ---------------------------------------------------------------------------
    19-DEC-08 10.34.24.000000 AM

    SQL> DROP TABLE TEST;

    Table dropped.

    SQL>