RE: Multiple SQL commands in one batch

 
You can dynamically generate sql within Oracle stored procedure by utilizing DBMS_SQL package. 

[email protected] wrote:Thanks for the tip. But I want to send the hole batch of SELECT statements 
in one text string directly via ADO to Oracle ODBC driver, because the 
statements are composed according to input parameters from the user. And 
it seems as if the ODBC driver don't like / either. It could be some 
limitation of the ODBC driver. But in that case I don't know which ODBC 
driver to use.
Regards,
Inge

> take out the ; and place / on a new line after the end of each sql.
example:
select * from table_so where soandso = 'valuetofind'
/
select * from table_name where .....
/
or you can put each sql in a directory and then you can execute them in
sqlplus like:
@D:\mydirectory\myfirst.sql
@D:\mydirectory\mysecond.sql
and so on.

-----Original Message-----
From: [email protected] [mailto:[email protected]]
Sent: Thursday, March 27, 2003 12:19 PM
To: Oracle
Subject: [oracle] Multiple SQL commands in one batch


Hi,

I have managed to send multiple SQL commands in one batch to both MS SQL 
Server and Sybase ASE. But now I have to get data from an Oracle database, 
and this does no longer work as easy as I have got accustomed to.

I use a blank as a delimiter between the different SQL commands. Have also 
tried the ";" character which works in the Oracle Client tool SQL *Plus 
Worksheet, but it does not work when I use MS Query or ADO via Oracle ODBC 
Driver 9.0.1.4.0. I get the same result when I use a data source with 
driver "Microsoft ODBC for Oracle". I don't know if my problem is an issue 
of Oracle syntax, ODBC or something else.

When using a blank delimiter I get the message ORA-00933 (the SQL command 
incorrect ended, which I suppose mean that only the first SQL command is 
sent and/or read). When using the ";" character I get the message ORA-
00911 (bad character, which should mean the ";" character).

Thanks in advance for any help.

Inge
---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to %%email.unsub%%.

---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to %%email.unsub%%.


---------------------------------
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more

---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to [email protected].