-
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
-
Try using LPAD/RPAD with c_POS_ID as POS_ID is CHAR type column, which will be of fixed length.
-
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
-
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
-
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..
-
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.
