Hi, I am using odp.net 11g beta2 accessing Oracle 10g database. I use TableAdapter to call a stored procedure in a package. In my stored procedure, if I use static SQL statement
<pre>
open l_cur
for select lookup1_id as "ID"
, lookup1_desc as "DESC"
, isactive as "ACTIVE"
from lookup1 "LookUpTable"
order by Upper('DESC');
o_cursor := l_cur;
</pre>
it works fine. If I use an equivalent dynamic sql statement,
<pre>
l_select :=
'select '
|| l_id_col
|| ' as "ID"'
|| ', '
|| l_desc_col
|| ' as "DESC"'
|| ', '
|| l_is_active
|| ' as "ACTIVE"'
|| ' from '
|| i_table
|| ' "LookUpTable"'
|| ' order by Upper('
|| '''DESC'''
|| ')';
OPEN l_cur FOR l_select;
o_cursor := l_cur;
</pre>
it gives me ORA-00911 error. Does odp.net support dynamic sql statement ?
Can I use static sql statement to generate Table Adapter, then swap to use dynamic sql statement at runtime ? If yes, how ?
Below is the error and stack trace
<pre>
Source Error:
Line 1471: End If
Line 1472: Dim dataTable As LookupDataSet.LookupTableDataTable = New LookupDataSet.LookupTableDataTable
Line 1473: Me.Adapter.Fill(dataTable)
Line 1474: If ((Me.Adapter.SelectCommand.Parameters(5).Value Is Nothing) _
Line 1475: OrElse (Me.Adapter.SelectCommand.Parameters(5).Value.GetType Is GetType(System.DBNull))) Then
</pre>
<pre >
[OracleException (0x80004005): ORA-00911: invalid character
ORA-06512: at "VITALSTATSADM.PK_LOOKUP_TABLE", line 78
ORA-06512: at line 1]
Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure) +322
Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src) +45
Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior) +4316
Oracle.DataAccess.Client.OracleDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) +692
System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) +107
OracleConnectionTest.LookupDataSetTableAdapters.LookupTableTableAdapter.GetData(String I_TABLE, String I_ID_COL, String I_DESC_COL, String I_IS_ACTIVE, Nullable`1 I_SELECT_FLAG, Object& O_CURSOR) in C:\Documents and Settings\wongma\My Documents\Visual Studio 2005\Projects\OracleConnectionTest\OracleConnectionTest\LookupDataSet.Designer.vb:1473
[TargetInvocationException: Exception has been thrown by the target of an invocation.]
System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +0
System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +72
System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) +358
System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +29
System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method, Boolean disposeInstance, Object& instance) +482
System.Web.UI.WebControls.ObjectDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +2040
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +41
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360
</pre>
Thanks for the help.