Hi there,
Has anyone experience this issue or know how to solve it? Your help would be greatly appreciated.
I recently encounter the error returns when executing Oraclecommand.ExecuteReader.
The command call the stored procedure that uses "execute immediate with using clause" to executes the anonymous pl/sql block which return the cursor as an output.
The cursor uses the bind variable pass from the using clause in both select statement, where clause and Sub-query Factoring (with clause).
Let's say there 5 bind variables are passed to the anonymous pl/sql, and they are referred for more than 15 times in the cursor statement.
The returning cursor is to bind with the datagrid display on the ASP.Net web page. It successfully execute for about 4 times, then on the 5th time, the ExecuteReader throw error:
Exception Type: Oracle.ManagedDataAccess.Client.OraException
Exception Message:
ORA-00604: error occured at recursive SQL level 1
ORA-06550: line 1, column 12:
PLS-00707: unsupported construct or internal error [2602]
ORA-06550: line 1, column 7:
PL/SQL: SQL Statement ignored
Here the mock up code for this event
Procedure myblk
(
P_cur out ref cursor,
P_msg out varchar,
P_b1 in char, . . . P_b5 in char -- there's 5 input parameters to be use as bind variable
) is
v_plsql varchar2(2000);
v_sql varchar2(2000) := 'The select statement with SubQuery factoring and the select with where clause. And they both repeatedly refers to the passing in variable';
begin
v_sql := 'begin open :cv for '|| v_sql ||'; end;';
Execute immediate v_sql using in out p_cur, p_b1, p_b2, p_b3, p_b4,p_b5;
exception
when other then p_msg := sqlerrm;
end;
.Net code is look like
internal myMethd ()
{
using (OracleConnection conn = ....)
{
using (OracleCommand cmd = new OracleCommand("myblk", conn)
{
cmd.BindByName = true;
[ this part define and assign value to the cmd parameter in-out]
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
using (OracleDataReader rd = cmd.ExecuteReader()) // Abort on the 5th with Ora error.
{
[loop data in reader];
}
}
}
}
Well, at first I did not use the dynamic pl/sql block, My store procedure directly call to open cursor for my dynamic sql statement and the error was throw within the plsql procedure execution (catch by exception handling block) on the 5th time as well; but with the different message i.e. Ora-01008: not all variable bound.
I'm using Vs.Net 2015 with ODP.NET 4.121.2 with Oracle 12.1.0.2