ORA-01722: invalid number in cursor [message #23314] |
Fri, 29 November 2002 23:07 |
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
|
|
|
Re: ORA-01722: invalid number in cursor [message #23318 is a reply to message #23314] |
Sat, 30 November 2002 14:12 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
Arfat, your whole procedural process can and should be handled in a single update statement. Note the difference in how the date comparison is handled. Those TRIM functions you have will totally prevent the use of indexes unless you use function-based indexes.
update sebirpt sb
set debt = (select st.debt
from script s, securitytype st
where s.scripcode = sb.scripcode
and st.sectype = s.sectype)
where download_date = to_date('20021127', 'yyyymmdd')
and status = 1
and exists (select null
from script s, securitytype st
where s.scripcode = sb.scripcode
and st.sectype = s.sectype);
|
|
|