0

Reproducer VB.net Windows Forms app using .Net Framework 4.6.1 and Oracle OCI Drivers 19 (plus a DSN entry of course)

Imports System.Data.OracleClient

Private Sub btnFakeStoredProcedure(sender As Object, e As EventArgs) Handles btnStoredProcedure.Click
        Dim OConn = New OracleConnection()

        OConn.ConnectionString = "Password=...; User ID=...; Data Source=...;"
        OConn.Open()

        Dim Command = New OracleCommand("TESTPARAMS_PKG.test1param", OConn)
        Command.CommandType = CommandType.StoredProcedure

        OracleCommandBuilder.DeriveParameters(Command)

        Dim txtParameters = "The parameters: " & vbCrLf & Command.Parameters.Count & vbCrLf
        OConn.Close()

        For Each parameter As OracleParameter In Command.Parameters
            txtParameters += "Parameter Name: " & parameter.ParameterName & vbCrLf
            txtParameters += "Parameter Direction: " & parameter.Direction & vbCrLf
            txtParameters += "Parameter Value: " & parameter.Value.ToString() & vbCrLf & vbCrLf
        Next

        txtStoredProcedure.Text = txtParameters

    End Sub

CREATE OR REPLACE PACKAGE TESTPARAMS_PKG AS 

  -- just 1 varchar param with and without default, implicit IN
  PROCEDURE test1param (param1 VARCHAR2);

END TESTPARAMS_PKG;
/

CREATE OR REPLACE PACKAGE BODY TESTPARAMS_PKG AS

  PROCEDURE test1param (param1 VARCHAR2) AS
  BEGIN
    -- TODO: Implementierung for PROCEDURE TESTPARAMS_PKG.test1param erforderlich
    NULL;
  END test1param;

END TESTPARAMS_PKG;
/

This worked (1 param returned) with 11g OCI drivers and an 11g backend (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production), but fails (0 parameters returned) with 12c++/19c OCI drivers (and the same 11g backend)

Versions:

  1. "Oracle in instantclient_11.2.0_x86" -> parameter count returned
  2. "Oracle in OraClient12cR2_x86" -> parameter count == 0

interesting: OracleCommandBuilder.DeriveParameters(cmd) not getting parameters from Oracle 18c StoredProcedure in .net framework 4.7.2 reports somewhat similar

clemens utschig
  • 21
  • 3
  • "fails" and "COMPLETELY BROKEN" are not descriptions that help in problem diagnosis! Start by upgrading to a supported client e.g. 19c. This will still let you connect to 11gR2 DB. If you still have problems, update the question with a complete testcase including SQL to create the data: https://stackoverflow.com/help/minimal-reproducible-example – Christopher Jones Aug 30 '23 at 00:42
  • @ChristopherJones - sorry for the wording (you are right!) the 11g driver(s) - work in the context of deriving the right parameters for an existing procedure (and return 0 parameters with a non existant procedure - which is a bug) the 12g driver(s) - do NOT work deriving the right parameters even for an existing procedure. testcase to come - we are building right now. For context - this is age old code, and we just have to get an env running to test - so I put the diagnosis up - that we can observe from the running application .. – clemens utschig Aug 30 '23 at 06:20
  • Let us know how you go with 19c ODBC – Christopher Jones Aug 30 '23 at 07:24
  • amended original post - now that we have 20 line reproducer – clemens utschig Aug 30 '23 at 14:21
  • What's the behavior with 19c ODBC? – Christopher Jones Aug 30 '23 at 22:51
  • Christopher - I read there is NO support for 19c drivers anyway - so this is rather pointless (https://stackoverflow.com/questions/65012425/can-we-use-system-data-oracleclient-in-net-against-oracle-19c). I guess (albeit the change is between two odbc versions 11g/12c) - we will have to talk to msft – clemens utschig Aug 31 '23 at 06:27
  • We support and can patch 19c. – Christopher Jones Aug 31 '23 at 22:14
  • Christopher - here is what we know .. behind the scenes it's using OCI to get the SP metadata - and there must be a change with 11g (all the way up to 19c) that returns an empty array. As this is (obviously) closed source, that's as much info as I can get you, does that help? – clemens utschig Sep 01 '23 at 17:17
  • It would be helpful if you could update the question with the exact versions you are using. All too often we test things and they work for us. – Christopher Jones Sep 02 '23 at 03:09

0 Answers0