Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2012
    Posts
    13

    Unanswered: Invalid Character Error

    Hi All,

    I have a problem regarding Invalid Character error.
    The below query which i have mentioned has been written for content search.
    The query executes once properly giving the result.
    If the same query is executed second time in the session it fails giving the following message.

    ORA-20000: Oracle Text error:
    ORA-00911: invalid character


    The query will run properly if its executed in one more session.
    Someone please help me out in fixing this.

    SELECT afm.record_id, afm.faq_id, afm.faq_title, afm.faq_response,
    afm.product, afm.country, afm.theraptic, afm.inquiry_category,
    afm.faq_keywords, afm.major_version, afm.minor_version,
    afm.completion_flag, afm.approved,
    find_wf_activity (afm.record_id, jbpm_process_inst_id),
    fn_faq_companyunit_list (afm.record_id),
    fn_faq_products_list (afm.record_id), afm.faq_expiry_date,
    afm.global_asset, afm.lang_code
    FROM agx_faq_maintainence afm, jbpm_token jt
    WHERE jt.processinstance_ = afm.jbpm_process_inst_id
    AND jt.node_ IN (65, 79, 85, 67, 69, 86)
    AND afm.latest_version = 1
    AND ( UPPER (afm.faq_id) LIKE 'MEDICATION%' ESCAPE '\'
    OR contains (afm.faq_keywords, '{MEDICATION%}') > 0
    )
    ORDER BY afm.faq_id ASC

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post results from following SQL

    SELECT * FROM V$VERSION;

    how can we 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
    Feb 2006
    Posts
    173
    Provided Answers: 1
    I don't know of the oracle function your using(contains), try the below sql:
    Code:
    select afm.record_id, afm.faq_id, afm.faq_title, afm.faq_response, afm.product, afm.country,
           afm.theraptic, afm.inquiry_category, afm.faq_keywords, afm.major_version, afm.minor_version,
           afm.completion_flag, afm.approved, find_wf_activity( afm.record_id, jbpm_process_inst_id ),
           fn_faq_companyunit_list( afm.record_id ), fn_faq_products_list( afm.record_id ),
           afm.faq_expiry_date, afm.global_asset, afm.lang_code
     from  agx_faq_maintainence afm, jbpm_token jt
    where  jt.processinstance_  =  afm.jbpm_process_inst_id
     and   jt.node_ in( 65, 79, 85, 67, 69, 86 )
     and   afm.latest_version  =  1
     and   ( upper( afm.faq_id ) like 'MEDICATION%' ESCAPE '\'
      or     regexp_instr( afm.faq_keywords, '{MEDICATION.}', 1, 1, 0 ) )  >  0
    order by afm.faq_id asc
    Last edited by spacebar; 09-28-12 at 16:29. Reason: Left out a closing ')'

  4. #4
    Join Date
    Sep 2012
    Posts
    18

    try this

    assalam u alaikum

    i think the error was you have entered a braces before UPPER
    i removed them

    so please try this




    SELECT afm.record_id, afm.faq_id, afm.faq_title, afm.faq_response,
    afm.product, afm.country, afm.theraptic, afm.inquiry_category,
    afm.faq_keywords, afm.major_version, afm.minor_version,
    afm.completion_flag, afm.approved,
    find_wf_activity (afm.record_id, jbpm_process_inst_id),
    fn_faq_companyunit_list (afm.record_id),
    fn_faq_products_list (afm.record_id), afm.faq_expiry_date,
    afm.global_asset, afm.lang_code
    FROM agx_faq_maintainence afm, jbpm_token jt
    WHERE jt.processinstance_ = afm.jbpm_process_inst_id
    AND jt.node_ IN (65, 79, 85, 67, 69, 86)
    AND afm.latest_version = 1
    AND UPPER (afm.faq_id) LIKE 'MEDICATION%' ESCAPE '\'
    OR contains (afm.faq_keywords, '{MEDICATION%}') > 0

    ORDER BY afm.faq_id ASC




    MUHAMMAD ARSALAN

Posting Permissions

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