DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: ORA-01861 : WHILE USING LTRIM(RTRIM(VAR_NAME));

  1. #1
    Join Date
    Jan 2001
    Posts
    642
    Hi,

    I am updating a table with the amount field where ever my pros_id match.
    Pros_id is a char(12) field.

    **** procedure being run***
    begin
    c_amount account.acc_field%type;
    c_pros_id account.pros_id%type;
    update account
    set account = c_amount
    where pros_id = ltrim(rtrim(c_pros_id);
    end;
    ****
    sql> exec proc_name;
    I get this error.
    ORA-01861: literal does not match format string
    ORA-06512: at "MNDB.UPD_CRD_RATE", line 40
    ORA-06512: at line 1
    If I donot use the ltrim/rtrim then I don't get the error but the records donot get updated.

    Can you help me
    Badrianth



  2. #2
    Join Date
    Nov 2000
    Location
    New Delhi, India
    Posts
    81
    Try using LPAD/RPAD with c_POS_ID as POS_ID is CHAR type column, which will be of fixed length.

  3. #3
    Join Date
    Feb 2001
    Posts
    184
    Hi,

    I have gone through this...
    If Possible change char12 to varchar12, then Ltrim and Rtrim would be able to find... This way CHAR 12 has fixed length which even the Ltrim amd Rtrim can't Trim..., This is stored as blank space.

    If you know the length of column stored.. like out of 12 length it may have 5 characters long, This way use Substr to find exact values... Like
    Update Table
    Set Col1 = Col2
    where Col1 = Substr(Col1,1,5)

    This way you would be able to find...
    Third option is to have both as Char 12...

    Take care... Thanks

  4. #4
    Join Date
    Jan 2001
    Posts
    642
    Hi,

    I have changed the c_pros_id to varchar2(12) and as I don't want to restrict the size of the column, I am not using substr(c_pros_id,1,5).

    Still the problem persists

    Badrinath


  5. #5
    Join Date
    Feb 2001
    Posts
    184
    I Really don't see any Trouble using even Ltrim/Rtrim
    Here is Example
    SQL> desc t1
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    EMPNO NUMBER(4)
    ENAME VARCHAR2(10)
    JOB VARCHAR2(12)

    SQL> desc t2
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    ID NUMBER
    NAME CHAR(15)
    TITLE VARCHAR2(15)

    SQL> select a.empno, b.title
    2 from t1 a, t2 b
    3 where a.ename = rtrim(ltrim(b.name));

    EMPNO TITLE
    ========= ===============
    8062 CLERK
    8063 CLERK
    8066 SALESMAN
    8066 SALESMAN
    8066 SALESMAN
    8066 SALESMAN
    8064 SALESMAN
    8065 MANAGER
    8712 DBA
    1212 Manager
    8061 SALESMAN
    8061 SALESMAN
    8061 SALESMAN
    8061 SALESMAN

    Can you please send me the Code, There could be a problem in code.

    Have you Tried just in SQL... If it works in SQL it should also work in PL/SQL

    Thanks..

  6. #6
    Join Date
    Feb 2001
    Posts
    184
    Here is the Description of the Error

    ORA-01861 literal does not match format string

    Cause: Literals in the input must be the same length as literals in the format string (with the exception of leading white space). If the "FX" modifier has been toggled on, the literal must match exactly, with no extra white space.

    Action: Correct the format string to match the literal.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width