none
DeriveParameters method and Boolean parameters in stored procedures RRS feed

  • Question

  • Hello,

    I use Oracle ODP.NET driver.

    I just want to know if it's possible to derive Boolean parameters from Oracle stored procedures using OracleCommandBuilder's DeriveParameters method?

    I have this stored procedure:

    create or replace procedure "GET_BOOL"
    (last_name IN PERSON.LastName%TYPE,
    flag_count OUT BOOLEAN)
    is
    r_count NUMBER;
    begin
    SELECT COUNT(*) INTO r_count FROM PERSON
    WHERE LASTNAME = last_name;

    IF r_count > 10 THEN
    flag_count := true;
    ELSE
    flag_count := false;
    END IF;
    end;

    I use C# code, but there's an error:

    System.InvalidOperationException was unhandled
    Message=Unsupported PL/SQL datatype at parameter position 2
    Source=Oracle.DataAccess
    StackTrace:
    at Oracle.DataAccess.Client.OracleCommandBuilder.DeriveParameters(OracleCommand command)
    at GetOracleData.OracleClient.GetScalarFromStoredProcedure(String spName, Object[] parameterList) in D:\Programiranje\Oracle\Oracle\GetOracleData\GetOracleData\OracleClient.cs:line 308
    at GetOracleData.Form1.button7_Click(Object sender, EventArgs e) in D:\Programiranje\Oracle\Oracle\GetOracleData\GetOracleData\Form1.cs:line 202
    at System.Windows.Forms.Control.OnClick(EventArgs e)
    at System.Windows.Forms.Button.OnClick(EventArgs e)
    at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
    at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
    at System.Windows.Forms.Control.WndProc(Message& m)
    at System.Windows.Forms.ButtonBase.WndProc(Message& m)
    at System.Windows.Forms.Button.WndProc(Message& m)
    at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
    at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
    at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
    at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
    at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
    at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
    at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
    at System.Windows.Forms.Application.Run(Form mainForm)
    at GetOracleData.Program.Main() in D:\Programiranje\Oracle\Oracle\GetOracleData\GetOracleData\Program.cs:line 18
    at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
    at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
    at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
    at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
    at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
    at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
    at System.Threading.ThreadHelper.ThreadStart()
    InnerException:

    Thank you in advance.

    Goran

     

    Friday, November 5, 2010 10:35 AM