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

[cassie99]

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;

 

[SantaMufasa]

Cassie,

It appears that your problem is with your VB. Here is my execution of your code (from within SQL*Plus) showing no syntax errors. (Since your problem was with syntax, I didn't bother to create any rows of data.):

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;

no rows selected

If I were in your troubleshooting shoes, I would remark out large chunks of code until the code passes syntax muster. I would then add code chunks back in until the error recurred. That would, at least, help you isolate your problem area.

Please let us know what you discover.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

...And when I say, "...remark out large chunks of code until the code passes syntax muster...", here is a possible starting point for minimal code:

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
;

...then add back in the code, "dbms_lob.substr(a.data,2000,1) AS DATA_LOB" (where I suspect your VB problem lies anyway).

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Turkbear]

Hi,
One question I have is how you are constructing that query in your VB app..Is it concatenated? If so, try a response.write ( or similar) after constructing it to see if it has been built exactly like your SQL example.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[cassie99]

Thank you for your responses.

-Cassie

 

[SantaMufasa]

Cassie,

Your findings will help others who encounter problems similar to yours. What did you discover was the cause of your problem? What did you do to resolve the problem?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ChrisHunt]

The first thing I'd try is removing the semicolon from the end of the command. Whilst you need it when entering a SQL statement in SQL*Plus, other methods of delivering SQL to the database can sometimes choke on it.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[cassie99]

Thanks Chris. Removing the ";" solved the problem.

-Cassie