As you are not showing your script to us, lets work the other way around.
create a table in your database
Create table Test_call_count (
count number(10)
);
insert into Test_call_count values (0);
and put the following into your script file
update Test_call_count set count = count + 1;
After executing your code, you can check if it worked by selecting from the database.
The next step will be to use some begin end; block not returning any result sets.
Generally Ado can't process whole scripts. Using sql-server it can process batches, that are parts of a script separated by go statements. As far as I know for Oracle it can only process a single sql-statements or a begin end block.
Edit:
Here is a litte PowerShell you can use to try what is possible:
if ($ora_loaded -eq $null)
{
$ora_loaded = [System.Reflection.Assembly]::LoadWithPartialName("Oracle.DataAccess")
}
$ConnectionString = "Data Source=YOUR_TNS;User ID=YOUR_ID;Password=YOUR_Password"
function Execute-Query
{
param (
$sql
)
$conn = new-object Oracle.DataAccess.Client.OracleConnection
$conn.ConnectionString = $ConnectionString
$cmd = new-object Oracle.DataAccess.Client.OracleCommand($sql,$conn)
# $cmd.CommandTimeout=$timeout
$ds = New-Object system.Data.DataSet
$da = New-Object Oracle.DataAccess.Client.OracleDataAdapter($cmd)
$nuLL = $da.fill($ds)
$ds.tables[0]
Write-Host ''
}
function Execute-NonQuery
{
param (
$sql
)
$conn = new-object Oracle.DataAccess.Client.OracleConnection
$conn.ConnectionString = $ConnectionString
$cmd = new-object Oracle.DataAccess.Client.OracleCommand($sql,$conn)
$conn.open()
# $cmd.CommandTimeout=$timeout
$cmd.ExecuteNonQuery()
$conn.close()
}
Execute-Query "select * from Test_call_count;"
Execute-NonQuery "update Test_call_count set count = count + 1"
Execute-NonQuery "begin update Test_call_count set count = count + 1; end;"
For example you see, that the following fails ( I added a semicolon):
Execute-NonQuery "update Test_call_count set count = count + 1;"
And this fails too:
Execute-NonQuery "begin update Test_call_count set count = count + 1; end;
/"
In a first approximation you can think of the '/' as similar to the sql-server batch delimiter go.
It wouldn't perform as well, but to meet your requirements given your limitations, you could use a pipelined result set. Both your original procedure and your new procedure could then use the function. The original one would return the entire results, and the new procedure would wrap the function in a larger query that does the GROUP BY. In this way the core query code would not be duplicated. Here is a pipelined result set example along with two queries using it:
create or replace PACKAGE Example AUTHID Definer AS
Type tRow Is Record (
F1 Date
, F2 Date
);
Type tTable Is Table of tRow;
Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined;
END Example;
/
create or replace PACKAGE BODY Example AS
Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined AS
vYesterday Date := trunc(sysdate-1);
Begin
For vRow IN (
SELECT pDate, vYesterday FROM DUAL
UNION ALL
SELECT pDate, vYesterday-1 FROM DUAL
UNION ALL
SELECT pDate+1, vYesterday FROM DUAL
)
Loop
Pipe Row(vRow);
End Loop;
End;
END Example;
/
SELECT * FROM TABLE(Example.PipelineResult);
SELECT F1, count(*) FROM TABLE(Example.PipelineResult) GROUP BY F1;
Best Answer
Aswering Question2:
You can't show the result like Sql Server when you build an anonymous block in PL-SQL. You have to do it using procedure or function. ps: function must return a sys_refcursor.
But hey, I had similar problem... What I've done for this case was to make a stored procedure to pass a string of values and show it like a table using simple math and drawing techniques. (I don't have the stored procedure, it was from my previous work)