Multiple oracle queries problem

Question

I'm trying to run a query from a .NET page but I seem to having some problems with having multiple queries.

My query is similar to this

SELECT * FROM table1; SELECT * from table2

But i seem to get an invalid character error when executing this from a .Net page. It runs fine in SQL developer but only fails when i put it in my .NET page.

I've added the BEGIN and END to the query as some websites suggest you need this to run multiple queries but then I get the following error

ORA-06550: line 1, column 7: PLS-00428: an INTO clause is expected in this SELECT statement

Can anyone shed any light on this one?

Thanks in advance!

EDIT

Here's 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()

What do you expect to receive? PL-SQL explicitly denies to `SELECT` without using the result. — Maxim Razin, Jul 14 '11 at 10:44
@grep: Basically I want to databind a datagrid with the results of one table and populate a label with the results of the other table — Jamie Taylor, Jul 14 '11 at 10:48

score 4 · Accepted Answer · answered Jul 14 '11 at 10:51

If you want to pull from 2 tables and get a DataSet that you can fill into a DataAdapter, then you need to use one of the following approaches:

join the 2 tables together (may or may not be possible depending on your tables)
union the 2 tables (this may or may not be applicable to your scenario)
write a stored procedure which creates whatever result you need and returns it in a ref cursor. You can read about how to do that here.

You're not going to be able to just run 2 SQL statements like that and get any meaningful result into a DataSet.

Thanks - i'll try one of those appreciate the help :) – Jamie Taylor Jul 14 '11 at 10:57 — Jamie Taylor, Jul 14 '11 at 10:57

score 0 · Answer 2 · answered Jul 14 '11 at 10:52

0

Apologies for stating the obvious but:

1.. Make 2 calls

or

2.. Put the selects in a stored procedure and return 2 refcursors

here's a good link on Working with Multiple Result Sets: http://msdn.microsoft.com/en-us/library/ms971506.aspx#msdnorsps_topic13

answered Jul 14 '11 at 10:52

Kevin Burton

11,676
2
24
37

score 0 · Answer 3 · answered Jul 14 '11 at 11:01

If you only need fields that are present in both 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 a result with the columns primary_key, name, amount (this is just a random example)

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

score 0 · Answer 4 · edited Oct 02 '12 at 15:43

A 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 with System.Data.OracleClient.OracleDataReader ... but not tested myself.

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

Multiple oracle queries problem

4 Answers4

Linked