Problem with several oracle queries

Question

I am trying to run a query from a .NET page, but it seems to be having problems with multiple queries.

My request is similar to this

SELECT * FROM table1; SELECT * from table2

But I seem to get an invalid character error while doing this from the .Net page. It works great in a SQL developer, but only fails when I put it on my .NET page.

I added the BEGIN and END tags to the request, as some websites offer you to execute several requests, but then I get the following error:

ORA-06550: row 1, column 7: PLS-00428: INTO clause expected in SELECT statement

Can anyone shed some light on this one?

Thanks in advance!

EDIT

Here is some code

  query = conn.CreateCommand() query.CommandText = "SELECT * from table1; SELECT * FROM table2;" DataSet = New DataSet() DataAdapter = New DataAdapter(query) DataAdapter.Fill(DataSet) datagrid1.DataSource = DataSet.Tables(0) datagrid1.DataBind() lbl1.Text = DataSet.Tables(1).Rows(0).Item("column1").ToString() 

Answer 1

If you want to extract from 2 tables and get a DataSet that you can populate in the DataAdapter, you need to use one of the following approaches:

• join 2 tables together (maybe or not possible depending on your tables)
• join two tables (this may or may not be applicable to your scenario)
• write a stored procedure that will create any result you need and return it to the ref cursor. You can read about how to do it here .

You cannot just run 2 SQL statements like this and get any meaningful result in the DataSet.

Answer 2

Apologies for pointing out the obvious, but:

1 .. Make 2 calls

or

2 .. Put the selection in the stored procedure and return 2 refcursors

here's a good link to working with multiple result sets: http://msdn.microsoft.com/en-us/library/ms971506.aspx#msdnorsps_topic13

Answer 3

If you need only the fields that are present in tables table1 and table2, you can do

 SELECT field1, field2, field3 FROM table1 UNION SELECT field1, field2, field3 FROM table2 

If the fields have different names, but the same type of content you can do

 SELECT tab1_id AS primary_key, tab1_name AS name, tab1_amount AS amount FROM table1 UNION SELECT tab2_id AS primary_key, tab2_name AS name, tab2_amount AS amount FROM table2 

This will give you the result with primary_key, name, amount columns (this is just a random example)

If two tables contain completely different content, you should really use two separate queries.

Answer 4

Possible solution could be

 query.CommandText = "BEGIN OPEN :1 FOR SELECT * FROM table1; OPEN :2 FOR SELECT * FROM table2; END;"; 

... as found in DataSet.Load: loading multiple tables using System.Data.OracleClient.OracleDataReader ... but not verified by itself.

Also check out http://forums.asp.net/t/629511.aspx/1 . It shows how, sort of. I added a link to Oracle.DataAccess.Types; but still there was a problem. It's close though.