Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01722 in Bulk Bind
ORA-01722 in Bulk Bind [message #201902] Tue, 07 November 2006 05:11 Go to next message
sayuri
Messages: 20
Registered: October 2006
Location: Cape Town
Junior Member
My cursor looks like this:
---------------------------

CURSOR c_reg (p_date IN DATE) IS
SELECT TRUNC(PROFILES.PROFILE_START_DATE) event_date,
REQUEST_ORIGINS.ORIGIN_NAME,
COUNT(*) count_reg
FROM SUB_PROFILES PROFILES,
RDSCONF.REQUEST_ORIGINS REQUEST_ORIGINS
WHERE TRUNC(PROFILE_START_DATE) = TRUNC(p_date)
AND SUBSCRIBER_EVENT_TYPE = 'REGPLS'
AND PROFILES.PROFILE_REQUEST_ORIGIN = REQUEST_ORIGINS.ORIGIN_ID
GROUP BY TRUNC(PROFILES.PROFILE_START_DATE),
REQUEST_ORIGINS.ORIGIN_NAME;


I get ORA-01722 when I try to run this piece of code. What can be wrong?


OPEN c_reg (v_date);
LOOP
FETCH c_reg BULK COLLECT INTO
r_event_date,
r_origin_name,
r_count_reg
LIMIT v_bulk_limit;

IF r_count_reg.COUNT > 0 THEN
FORALL i IN r_count_reg.FIRST..r_count_reg.LAST
INSERT INTO LOYALTY_REGISTRATIONS
(REGISTRATION_EVENT_DATE,
REGISTRATION_REQUEST_ORIGIN,
REGISTRATION_COUNT)
VALUES (r_event_date(i),
r_origin_name(i),
r_origin_name(i));
END IF;

EXIT WHEN c_reg%NOTFOUND;
END LOOP;
CLOSE c_reg;


Thanks!
Re: ORA-01722 in Bulk Bind [message #201907 is a reply to message #201902] Tue, 07 November 2006 05:43 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Try running your cursor as a separate select statement to see if that is causing the error. Run it all the way through to conclusion if you can, as the error may not show up in the first few rows. The line:

AND PROFILES.PROFILE_REQUEST_ORIGIN = REQUEST_ORIGINS.ORIGIN_ID

would be my favourite. Are both of these columns of type number ?
Re: ORA-01722 in Bulk Bind [message #201920 is a reply to message #201907] Tue, 07 November 2006 06:40 Go to previous messageGo to next message
sayuri
Messages: 20
Registered: October 2006
Location: Cape Town
Junior Member
Both fields are varchar2(2)

Re: ORA-01722 in Bulk Bind [message #201921 is a reply to message #201920] Tue, 07 November 2006 06:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You try to store r_origin_name(i) in REGISTRATION_COUNT
Re: ORA-01722 in Bulk Bind [message #201923 is a reply to message #201921] Tue, 07 November 2006 07:20 Go to previous message
sayuri
Messages: 20
Registered: October 2006
Location: Cape Town
Junior Member
What a silly mistake I made !!

Thanks for pointing it out. Sorry for wasting everyone's time ....

Previous Topic: Triggers / Cursors with outer join (merged)
Next Topic: ref cursor to return boolean value
Goto Forum:
  


Current Time: Sat Dec 03 06:07:33 CST 2016

Total time taken to generate the page: 0.10502 seconds