DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: [Microsoft][ODBC driver for Oracle][Oracle]ORA-00911: invalid character

  1. #1
    Join Date
    Jun 2009
    Posts
    2

    [Microsoft][ODBC driver for Oracle][Oracle]ORA-00911: invalid character

    Hello,

    I am receiving an invalid character error message when I attempt to execute the SQL below in my VBA application(db is Oracle 9i). Oddly enough, I can get this SQL to execute in TOAD without any warnings or errors. Any assistance would be greatly appreciated. Thanks in advance.

    -Cassie

    err.description =
    [Microsoft][ODBC driver for Oracle][Oracle]ORA-00911: invalid character

    SQL --

    SELECT DISTINCT SCHEMA_NAME, substr(DATA_LOB,
    instr(DATA_LOB,'|',1,7)+1, (INSTR(DATA_LOB, '|',1,8)-INSTR(DATA_LOB,'|',1,7))-1) AS SITE,
    substr(substr(DATA_LOB, instr (DATA_LOB, '|', 1, 10)+1,9),1,4) AS UNINIT_BSLN_SITE,
    substr(DATA_LOB, instr(DATA_LOB,'|',1,10)+1, (INSTR(DATA_LOB, '|',1,11)-INSTR(DATA_LOB,'|',1,10))-1) AS UNINIT_BSLN,
    substr(DATA_LOB, instr(DATA_LOB,'|',1,19)+1, (INSTR(DATA_LOB, '|',1,20)-INSTR(DATA_LOB,'|',1,19))-1) AS VISIT, substr(DATA_LOB, instr(DATA_LOB,'|',1,29)+1, (INSTR(DATA_LOB, '|',1,30)-INSTR(DATA_LOB,'|',1,29))-1) AS LABCOLLDATE,
    '0' AS START_DATE_STAMP,
    '0' AS STOP_DATE_STAMP,
    '0' AS ELAPSED_TIME,
    1 AS RECORDS_INSERTED
    FROM (SELECT RTRIM(a.trial_schema,'UID') AS SCHEMA_NAME, dbms_lob.substr(a.data,2000,1) AS DATA_LOB
    FROM rptimport.consolidated_cdisc_errors a
    WHERE a.error_code = 'ERR-003'
    AND a.adb_l_delivery_status = 'W')
    WHERE schema_name = 'MK0431102'
    ORDER BY UNINIT_BSLN, VISIT;

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    It would help if you knew what part of the query had an issue. Run this and if it works add in something else and try again. Then keep adding parts of the query and running it until you see what is causing the issue.

    I would guess that the error relates to a substring.
    But why guess when you can prove it.

    Code:
    SELECT RTRIM(a.trial_schema,'UID') AS SCHEMA_NAME
       FROM rptimport.consolidated_cdisc_errors a 
     WHERE 1=0;

  3. #3
    Join Date
    Jun 2009
    Posts
    2
    Removing the ";" solved the issue. Thanks.

Posting Permissions

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


Click Here to Expand Forum to Full Width