Semi-colon between 2 SELECT statments in Oracle causes error with System.Data.OracleClient

Question

I am using the ADO.Net provider for Oracle to execute 2 select statements separated by a semi-colon. I am using system.Data.OracleClient data provider. However, it seems the semi-colon is not liked by Oracle becuase I get an invalid character error when the ADO.Net code runs. Any ideas on how I could solve this as I have to run both SELECT statements in same database trip?

            string sql = @"
                        WITH
                      x AS
                      (
                        SELECT
                          RowNum AS RowIndex,
                          CAST(Contract_Id AS VARCHAR2(1000)) Contract_Id,
                          Contract_Number,
                          Name AS Contract_Name,
                          Contract_Number
                          || ' '
                          || Name AS Contract_Description,
                          Eff_Date,
                          Expiry_Date
                        FROM
                         contracts
                        WHERE
                          contract_number LIKE :ContractSearchText
                        OR name LIKE :ContractSearchText
                      )
                    SELECT
                      *
                    FROM
                      x
                    WHERE
                      RowIndex  >= :StartingRowIndex
                    AND RowIndex < (:StartingRowIndex + :MaxRows) ;

                    SELECT
                          COUNT(*)
                        FROM
                          contracts
                        WHERE
                          contract_number LIKE :ContractSearchText
                        OR name LIKE :ContractSearchText;
                       ";

UPDATE

Alex's reply is the best answer I could find. However, I had come up with another alternate answer, about which I am not sure if the table is hit twice or only once. The alternate query relies on creating a CROSS JOIN with a count derived table. May be someone can tell me if the table is hit only once in this alternate answer?

                          WITH
                      x AS
                      (
                        SELECT
                          RowNum AS RowIndex,
                          CAST(Contract_Id AS VARCHAR2(1000)) Contract_Id,
                          Contract_Number,
                          Name AS Contract_Name,
                          Contract_Number
                          || ' '
                          || Name AS Contract_Description,
                          Eff_Date,
                          Expiry_Date
                        FROM
                          vha_int_contract
                        WHERE
                          contract_number LIKE :ContractSearchText
                        OR name LIKE :ContractSearchText
                      ),
                      y as ( select count(distinct contract_id)  TotalCount from x)
                    SELECT
                      *
                    FROM
                      x cross join y
                    WHERE
                      RowIndex  >= :StartingRowIndex
                    AND RowIndex < (:StartingRowIndex + :MaxRows)

@Satya - Added the query. I think it may be NOT possible to execute batch SQL statements in Oracle. A limitation of Oracle, I guess. — Sunil, Jul 28 '12 at 19:13
I hope I could, but we are not allowed to create stored procedures in Oracle database, and so I need to write a query in my ADO.Net code. — Sunil, Jul 29 '12 at 16:19
@Satya - Do you know how I could convert the above to a PL/SQL block and return cursors for multiple statements? — Sunil, Jul 29 '12 at 16:39
check this http://stackoverflow.com/questions/2153053/how-to-return-a-resultset-cursor-from-a-oracle-pl-sql-anonymous-block-that-exe — Satya, Jul 29 '12 at 16:57
If you aren't sure what's happening under the hood, you can use various tools, such as [`explain plan`](http://docs.oracle.com/cd/E14072_01/server.112/e10592/statements_9010.htm), to investigate. I'd expect your new query to only hit the table once, but you should check for yourself. — Alex Poole, Jul 30 '12 at 18:28

Alex Poole · Accepted Answer · 2012-07-30T17:08:10.557

You can't usually run more than one SQL statement in a call, and switching to PL/SQL isn't really necessary. It looks like you're trying to get a count for the number of matching rows at the same moment, I guess so you can do something like 'results 1 to 5 of 30'. While you could do two separate calls, there's a risk that they'd get inconsistent results as another transaction could commit between (so you don't want '25 to 30 of 29', or a partial last page like '25 to 29 of 30'?).

You could get the total count as an extra field in each returned row, which is a bit redundant but doesn't cost any more - actually less as you only hit the table once, you don't have two separate queries. This uses the analytic function version of count, with an empty over clause since you want to count over everything in this case:

SELECT x.*, COUNT(1) OVER () Range_Count
FROM
(
    SELECT
        RowNum AS RowIndex,
        CAST(Contract_Id AS VARCHAR2(1000)) Contract_Id,
        Contract_Number,
        Name AS Contract_Name,
        Contract_Number
        || ' '
        || Name AS Contract_Description,
        Eff_Date,
        Expiry_Date,
        COUNT(1) OVER () Total_Count
    FROM
        contracts
    WHERE
        contract_number LIKE :ContractSearchText
        OR name LIKE :ContractSearchText
    ) x
WHERE RowIndex >= :StartingRowIndex
AND RowIndex < (:StartingRowIndex + :MaxRows)

I've done two counts here, just for fun. The one you need is in the inner query, COUNT(1) OVER () Total_Count; this will give you a Total_Count column on every row returned, with a value equal to what your original separate COUNT(*) query would have found. I've put one on the outer query too though, COUNT(1) OVER () Range_Count, which will show how many records are in the range - which will be :Max_Rows until you get to the last 'page'. Might not be useful and you can just count the rows as you process them, but just showing it's possible really.

You don't currently have any ordering in the query, which means you might not get the results you expect. I'd recommend you either add an ORDER BY clause to the inner query, or change the RowNum to ROW_NUMBER() OVER(ORDER BY Contract_ID) to stick with the analytic theme.

Not quite sure what you want from your comment. If you want the number of records that match each Contract_Id, you can add another term to the inner query:

    COUNT(1) OVER (PARTITION BY Contract_Id) Contract_Id_Count

If you want the total number of distinct Contract_Id values, add this instead:

    COUNT(DISTINCT Contract_Id) OVER () Contract_Id_Count

Wow. That was an excellent reply. Thank you very much as it helped. What is Count(1) Vs Count(*) in your query and also what if I wanted the count over distinct Contract_Id? — Sunil, Jul 30 '12 at 16:47
Doesn't matter if you use `count(*)` or `count(1)`; see [this question](http://stackoverflow.com/q/1221559/266304), and [this article](http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1156151916789) that is also linked from that. Tom says '`count(1)` is `count(*)` in disguise'. — Alex Poole, Jul 30 '12 at 16:56
For count only of distinct contract_id, I tried Count(distinct contract_id) over( ) TotalCount and it did not give an error. Seems right to you or it can be misleading? — Sunil, Jul 30 '12 at 17:08
@Sunil - I was adding much the same thing to my answer, so that looks fine. — Alex Poole, Jul 30 '12 at 17:09

score 1 · Answer 2 · answered Aug 01 '12 at 15:43

So other than being able to provide a better method to the op, are we in a position to say that ado.net does not like semi-colon's? I run a number of batched queries typically a create table, do something then drop the table(s). In T-SQL, the above is possible as SQL Server does not require the semi-colon to terminate a statement in most operations unlike Oracle for which the terminator is obligatory.

The only way I can currently think to get around thin issue is to use stored procs (not as nice or easy to code as T-SQL) but it does allow for batch statements to be executed.

Semi-colon between 2 SELECT statments in Oracle causes error with System.Data.OracleClient

2 Answers2