ORA-01722: invalid number in cursor [message #4343] |
Fri, 29 November 2002 23:04 |
Arfat
Messages: 25 Registered: August 2002
|
Junior Member |
|
|
Declare
recCnt number; --record Counter
ScrCode SEBIRPT.SCRIPCODE%TYPE;
cDebt Sebirpt.Debt%Type;
CURSOR cSecurityType is SELECT ScripCode FROM SEBIRPT
WHERE to_char(SEBIRPT.Download_Date,'yyyymmdd')='20021127'
And SEBIRPT.status=1;
BEGIN
FOR cSecType in cSecurityType LOOP
--Checking if scripcode exist in Scrip Table
SELECT count(*) into recCnt FROM Scrip
Where Scrip.ScripCode=cSecType.ScripCode;
--if scripcode exist then
IF recCnt > 0 THEN
--Checking if sectype of the scripcode exist in security type
SELECT count(*) into recCnt FROM Scrip,SecurityType
Where Trim(Scrip.ScripCode)=Trim(cSecType.ScripCode)
and Trim(Scrip.SecType)=Trim(SecurityType.SecType);
IF recCnt > 0 THEN
--if SecType Exist then getting it Debt indicator
SELECT SecurityType.Debt into cDebt FROM Scrip,SecurityType
Where Trim(Scrip.ScripCode)=Trim(cSecType.ScripCode)
and Trim(Scrip.SecType)=Trim(SecurityType.SecType);
update sebirpt
set Debt=cDebt
where Trim(ScripCode)=trim(cSecType.ScripCode)
and to_char(SEBIRPT.Download_Date,'yyyymmdd')='20021127'
and SEBIRPT.status=1;
END IF;
END IF;
End Loop;
END;
/
I am getting the following error when i run this in sql plus
or call this procedure written in a package.
Declare
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 10
wat i am trying to achive is
to update field DEBT in SEBIRPT table
having Scripcode in scrip table,
this scripcode have sectype field which is in security type table
and securitype table is having debt field whose value is to be updated
in sebirpt
can any one help with this
its really urgent
thanks in advance
|
|
|
|