Home » SQL & PL/SQL » SQL & PL/SQL » problem in pl/sql code
problem in pl/sql code [message #420287] Fri, 28 August 2009 03:24 Go to next message
simcky
Messages: 38
Registered: July 2009
Member
hii guy's,
i m writing this pl/sql code this is running but i m not getting any updation at the backend.

DECLARE
  CURSOR C IS 
    SELECT TO_DATE(STARTYEARMM,'YYYYMM'),TO_DATE(ENDYEARMM,'YYYYMM')
    FROM PRMALOAN28AUG
    WHERE INSTALMENTRECOVERED IS NULL;
 
  D DATE :=TO_DATE('200908','YYYYMM');
  S DATE;
  E DATE;
BEGIN
  OPEN C;
  LOOP
    FETCH C INTO S,E;
    IF E IS NULL THEN 
      UPDATE PRMALOAN28AUG SET INSTALMENTRECOVERED = MONTHS_BETWEEN(D,S);
      EXIT WHEN C%NOTFOUND;
    END IF;
  END LOOP;
END;


and the database table on which i m working having description as follows:-
DESC PRMALOAN28AUG
Describing PRMALOAN28AUG....
NAME Null? Type
---------
EMPNO VARCHAR2(8)
EARNINGDEDUCTION VARCHAR2(6)
REFNO VARCHAR2(15)
SANCDATE DATE
PRINCIPALAMT NUMBER(8,0)
SANCAMT NUMBER(8,0)
RATERECOVERY NUMBER(5,0)
TOTINSTALMENT NUMBER(3,0)
AMTRECOVERED NUMBER(8,0)
INSTALMENTRECOVERED NUMBER
STARTYEARMM VARCHAR2(6)
ENDYEARMM VARCHAR2(6)
CHANGEDATE DATE
USERID VARCHAR2(30)
PAYCALPERIOD VARCHAR2(6)


{added code tags}

[Updated on: Fri, 28 August 2009 03:32] by Moderator

Report message to a moderator

Re: problem in pl/sql code [message #420288 is a reply to message #420287] Fri, 28 August 2009 03:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You don't commit, so you won' see any changes from a session other than the one you run this in.

Re: problem in pl/sql code [message #420289 is a reply to message #420288] Fri, 28 August 2009 03:40 Go to previous messageGo to next message
simcky
Messages: 38
Registered: July 2009
Member
since i m using sql navigator so i m commit by the icon
Re: problem in pl/sql code [message #420290 is a reply to message #420287] Fri, 28 August 2009 03:43 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Why are you bothering with PL/SQL? Do it in a single SQL statement.

UPDATE tablename SET column_name = MONTHS_BETWEEN(datecalc1, datecalc2)
WHERE column IS NULL
Re: problem in pl/sql code [message #420293 is a reply to message #420290] Fri, 28 August 2009 03:58 Go to previous messageGo to next message
simcky
Messages: 38
Registered: July 2009
Member
hi
as u suggesting then i m writing this statement:-
update prmaloan
set instalmentrecovered=months_between(to_date('200908','yyyymm'),to_date(STARTYEARMM,'yyyymm'))
where empno='00000050'
and instalmentrecovered is null
then i m getting changes at backend

but if i m writing like this :-
update prmaloan
set instalmentrecovered=months_between(to_date('200908','yyyymm'),to_date(STARTYEARMM,'yyyymm'))
where
instalmentrecovered is null
then i m getting the error :-
: (Error): ORA-01858: a non-numeric character was found where a numeric was expected

what should i do to remove this error.
Re: problem in pl/sql code [message #420295 is a reply to message #420289] Fri, 28 August 2009 04:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You may well not see the values you expect - each record from the cursor will update the whole table, as there's no WHERE clause in the update.

I'd need to see a test case (create table statement and a some insert statements to produce a set of data on which the problem can be reproduced) to go much further.
Based on the code you've posted, assuming the cursor returns any rows, then updates will happen.
It's quite possible that the cursor returns no rows, but I can't know that.
Re: problem in pl/sql code [message #420296 is a reply to message #420293] Fri, 28 August 2009 04:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
First of all, you seem to be missing a "WHERE ENDDATE IS NULL", compared to your original code.
Now, the error is probably due to a startyearmm value that does not comply to the YYYYMM format.
Re: problem in pl/sql code [message #420297 is a reply to message #420287] Fri, 28 August 2009 04:12 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
I think problem is in values stored in STARTYEARMM column of the table.
Check the values of that column. Does it match with 'yyyymm' format?

regards,
Delna
Re: problem in pl/sql code [message #420298 is a reply to message #420293] Fri, 28 August 2009 04:13 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Please post the result of
SELECT empno
from prmaloan
where length(TRANSLATE(STARTYEARMM,'1234567890','s')) >1


[Updated on: Fri, 28 August 2009 04:16]

Report message to a moderator

Re: problem in pl/sql code [message #420299 is a reply to message #420296] Fri, 28 August 2009 04:14 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Sorry Frank sir for replying almost same.
But your post was not there when I clicked on POST REPLY.

regards,
Delna
Previous Topic: Send Email With Attachment
Next Topic: PL/SQL: numeric or value error
Goto Forum:
  


Current Time: Tue Dec 06 04:41:45 CST 2016

Total time taken to generate the page: 0.28191 seconds