Thread: ORA-01722 invalid number
08-12-14, 11:43 #1Registered User
- Join Date
- Aug 2014
Unanswered: ORA-01722 invalid number
I am facing ORA-1772: invalid number again and again when the query is run through .pc file. Same query works fine when executed directly on pl/sql developer.
SELECT queue_entry_id ,queue_urgency,
TO_CHAR (chg_dt, 'MM-DD-YY'),
TO_CHAR (queue_after_dt, 'MM-DD-YY'),
upd_cnt FROM BSD_QUEUE_WORK WHERE queue_entry_id IN (
SELECT queue_entry_id FROM (
SELECT /*+ INDEX (bsd_queue_work bsd_q_wrk_dt_idx) */
FROM BSD_QUEUE_WORK WHERE BSD_QUEUE_WORK.queue_after_dt <= SYSDATE
order by BSD_QUEUE_WORK.queue_urgency DESC, BSD_QUEUE_WORK.queue_after_dt)
WHERE rownum <=20) FOR UPDATE;
Old query looked like -
/*+ INDEX (bsd_queue_work bsd_q_wrk_dt_idx) +*/
TO_CHAR( BSD_QUEUE_WORK.queue_after_dt,:b0) ,
AND ROWNUM <=:b2)
ORDER BY BSD_QUEUE_WORK.queue_urgency DESC BSD_QUEUE_WORK.queue_after_dt FOR UPDATE;
This query was first selecting 20 rows and then sorting them on queue_urgency. This lead to high urgency rows waiting long for their turn if we had total of 10k rows. And, User wanted to find out high queue_urgency rows first and process them in chunks of 20 rows. Hence, i created the new query which is giving errors as above.
Could you please help me find out the problem here. Table structure is as- SQL> desc bsd_queue_work
Name Null? Type
----------------------------------------- -------- ----------------------------
QUEUE_ENTRY_ID NOT NULL NUMBER(18)
QUEUE_AFTER_DT NOT NULL DATE
UPD_CNT NOT NULL NUMBER(6)
CHG_WHO NOT NULL VARCHAR2(32)
CHG_DT NOT NULL DATE
QUEUE_URGENCY NOT NULL NUMBER(38)
08-12-14, 12:38 #2Registered User
Provided Answers: 1
- Join Date
- Aug 2003
- Where the Surf Meets the Turf @Del Mar, CA
how can reproduce what you report?You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Good judgement comes from experience. Experience comes from bad judgement.
08-12-14, 16:25 #3Registered User
- Join Date
- Jun 2003
- West Palm Beach, FL
How about this?:
SELECT * FROM ( SELECT Queue_Entry_Id , Queue_Urgency , TO_CHAR ( Chg_Dt, 'MM-DD-YY' ) , TO_CHAR ( Queue_After_Dt, 'MM-DD-YY' ) , Chg_Who , Upd_Cnt , ROW_NUMBER ( ) OVER ( ORDER BY Bsd_Queue_Work.Queue_Urgency DESC , Bsd_Queue_Work.Queue_After_Dt ) Rn FROM Bsd_Queue_Work ) WHERE Rn <= 20;The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb