Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2014
    Posts
    1

    Unanswered: ORA-01722 invalid number

    Hi,

    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'),
    chg_who ,
    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) */
    BSD_QUEUE_WORK.queue_entry_id
    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 -

    SELECT
    /*+ INDEX (bsd_queue_work bsd_q_wrk_dt_idx) +*/
    BSD_QUEUE_WORK.queue_entry_id,
    TO_CHAR(BSD_QUEUE_WORK.chg_dt,:b0) ,
    TO_CHAR( BSD_QUEUE_WORK.queue_after_dt,:b0) ,
    BSD_QUEUE_WORK.chg_who ,
    BSD_QUEUE_WORK.upd_cnt
    FROM BSD_QUEUE_WORK
    WHERE (BSD_QUEUE_WORK.queue_after_dt<=SYSDATE
    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)

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    How about this?:
    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •