ORA-00911 error occurs using ExecuteSQL to make calls to multiple procedures

[mjames]

I'm getting the following error 'ORA-00911: invalid character' when I try to use ExecuteSQL to make calls to multiple stored procedures.

Here's what's happening...

I have a C++ application called Archiver. Archiver connects to either a SQL Server or Oracle database via ODBC. The Archiver passes data to the database by executing a string that is passed to the 'ExecuteSQL ' method on a CDatabase object. In prior releases we would pass a string to ExecuteSQL with a single INSERT INTO... statement to insert data into the specified table. In this release e have moved that INSERT INTO... statement into a stored procedure and are now calling the stored procedure from the Archiver. This stored procedure is named BHInsert and when the Archiver connects to SQL Server and calls BHInsert everything works fine. I can successfully execute either a single EXECUTE BHInsert... statement or multiple statements (such as EXECUTE BHInsert... EXECUTE BHInsert... EXECUTE BHInsert...)

When the Archiver connects to Oracle I can only get a single CALL statement (shown below) to execute:

CALL BHInsert (TO_DATE('2003.07.23 10:04:28','YYYY.MM.DD HH24:MI:SS'),'BATCH_ID','1:CLS_FRENCHVANILLA-1','
','Event File
Name','\\KILLIANS\BATCHCTL\SampleDemo1\JOURNALS\1.evt','','AREA1','','','','','','1','','','','','', ' ','');

When I try to execute a string with multiple CALL statements (shown below) it fails with error 'ORA-00911: invalid character'

CALL BHInsert (TO_DATE('2003.07.23 10:04:28','YYYY.MM.DD HH24:MI:SS'),'BATCH_ID','1:CLS_FRENCHVANILLA-1','
','Event File
Name','\\KILLIANS\BATCHCTL\SampleDemo1\JOURNALS\1.evt','','AREA1','','','','','','1','','','','','', ' ','');
CALL BHInsert (TO_DATE('2003.07.23 10:04:28','YYYY.MM.DD
HH24:MI:SS'),'BATCH_ID','1:CLS_FRENCHVANILLA-1','Version','Recipe Header','1.0','','AREA1',' ','
','','','','1','','','','','',' ','');
CALL BHInsert (TO_DATE('2003.07.23 10:04:28','YYYY.MM.DD
HH24:MI:SS'),'BATCH_ID','1:CLS_FRENCHVANILLA-1','Version Date','Recipe Header','5/18/2001 1:28:32
PM','','AREA1',' ',' ','','','','1','','','','','',' ','');
CALL BHInsert (TO_DATE('2003.07.23
10:04:28','YYYY.MM.DD HH24:MI:SS'),'BATCH_ID','1:CLS_FRENCHVANILLA-1','Author','Recipe Header','Mark
Shepard','','AREA1',' ',' ','','','','1','','','','','',' ','');

A couple of additional things worth noting...
I've tried adding a line feed in addition to the space at the end of each call but that doesn't seem to help. I haven't yet tried a - or a \ between each call although I know these characters are sometimes interpreted as a continuation character. Also if I try to Execute the multiple CALL statements from within Oracle's SQL Worksheet, connecting as the same user and password, as the Archiver it executes successfully. However something that it is interesting to note is that when I try this from within the Oracle ODBC test client, it fails with the same ORA-00911 error that I'm getting returned to my Archiver client.

Any ideas would be greatly appreciated.

[TomazZ]

ExecuteSQL executes only one statement at once. I bet that with insert into... statements it was the same. You can execute one pl/sql block at once, though.
Something like:

begin
  BHInsert(...);
  BHInsert(...);
  ...
end;

[mjames]

Wrapping the multiple statements within a BEGIN/END block fails with a ora-06550 error. I'm not sure why or what this indicates other than as was stated there is definitely a limitation of executing only single SQL statements.

Where would one look for more information regarding whether this single statement limitation is related to the Oracle ODBC drivers or the ODBC protocol?

Also is there a way I can see what my 'final' SQL statement looks like? In other words the actual statement which will be executed on the database. I'm thinking this might help me determine if it's a driver or protocol limitation.

[mjames]

The Developer's Guide and Reference Release 1 (9.0.1) Part Number A90212-01 states that calls to stored procedures currently are not 'batchable' (only insert, update, and deletes are) which goes along with what I'm seeing.

For now I can temporarily work around this by simply changing my app to execute my stored procedure calls individually. However ultimately I will probably bind and 'insert with arrays'.

[TomazZ]

Originally posted by mjames
Wrapping the multiple statements within a BEGIN/END block fails with a ora-06550 error.

I believe you also put "CALL" with procedure names. Omit it, like in my example.
This is not batch of calls to stored procedures. It's one anonymous pl/sql block doing whatever...

Also, document you are refering is actually called "Oracle9i SQLJ Developer's Guide and Reference", but I cannot find the part you are refering to.