-
[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;
-
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;
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|