Re: Oracle Client Parameters

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Paul Clement (UseAdddressAtEndofMessage_at_swspectrum.com)
Date: 04/26/04 

Date: Mon, 26 Apr 2004 08:07:08 -0500

On Fri, 23 Apr 2004 11:17:09 +1200, bclegg <bclegg@adriley.co.nz> wrote:

¤ Paul Clement wrote:
¤ > On Thu, 22 Apr 2004 20:43:25 +1200, "Bob Clegg" <bclegg@clear.net.nz> wrote:
¤ >
¤ > ¤ Hi,
¤ > ¤ I am trying my first attempt at calling an Oracle Procedure using the
¤ > ¤ Micosoft Oracle data client.
¤ > ¤ Can any one spot what I am doing wrong?
¤ > ¤ I get Oracle Error 06550 'Wrong number or types of arguments in call'
¤ > ¤ The table contains 14 columns number(10)
¤ > ¤ The proc was generated by Oracle Migration Workbench from an SQL server
¤ > ¤ proc. I have altered the RC1 Parameter type from 'IN OUT' to 'OUT' to match
¤ > ¤ the code which was drawn from one of the help examples.
¤ > ¤ thanks
¤ > ¤ bob
¤ > ¤
¤ > ¤ CALLING CODE:
¤ > ¤ *********************
¤ > ¤ Dim cmdConn As New OracleClient.OracleCommand
¤ > ¤
¤ > ¤ Dim cmdSource As OracleClient.OracleDataReader
¤ > ¤
¤ > ¤ Dim myDBNUll as object
¤ > ¤
¤ > ¤ Try
¤ > ¤
¤ > ¤ myDBNull = Convert.DBNull
¤ > ¤
¤ > ¤ strSQL = "proc_GetColWidths"
¤ > ¤
¤ > ¤ cmdConn = New OracleClient.OracleCommand(strSQL, New
¤ > ¤ OracleClient.OracleConnection(mstrConn))
¤ > ¤
¤ > ¤ cmdConn.Parameters.Add(New OracleClient.OracleParameter("xx",
¤ > ¤ OracleClient.OracleType.Cursor, 2000, ParameterDirection.Output, True, 0, 0,
¤ > ¤ "", DataRowVersion.Default, myDBNUll))
¤ > ¤
¤ > ¤ cmdConn.CommandType = CommandType.StoredProcedure
¤ > ¤
¤ > ¤ cmdConn.CommandText = strSQL
¤ > ¤
¤ > ¤ cmdConn.Connection.Open()
¤ > ¤
¤ > ¤ cmdSource = cmdConn.ExecuteReader(CommandBehavior.CloseConnection)'FAILS
¤ > ¤ HERE!!!!!
¤ > ¤
¤ > ¤ ************************************************
¤ > ¤
¤ > ¤ ORACLE Procedure :
¤ > ¤ ########################################
¤ > ¤ PROCEDURE PROC_GETCOLWIDTHS(
¤ > ¤
¤ > ¤ RC1 OUT Omwb_emulation.globalPkg.RCT1)
¤ > ¤
¤ > ¤ AS
¤ > ¤
¤ > ¤ StoO_selcnt INTEGER;
¤ > ¤
¤ > ¤ StoO_error INTEGER;
¤ > ¤
¤ > ¤ StoO_rowcnt INTEGER;
¤ > ¤
¤ > ¤ StoO_crowcnt INTEGER := 0;
¤ > ¤
¤ > ¤ StoO_fetchstatus INTEGER := 0;
¤ > ¤
¤ > ¤ StoO_errmsg VARCHAR2(255);
¤ > ¤
¤ > ¤ StoO_sqlstatus INTEGER;
¤ > ¤
¤ > ¤ BEGIN
¤ > ¤
¤ > ¤ OPEN RC1 FOR
¤ > ¤
¤ > ¤ SELECT *
¤ > ¤
¤ > ¤ FROM GridColWidths;
¤ > ¤
¤ > ¤ END PROC_GETCOLWIDTHS;
¤ > ¤
¤ > ¤ #######################################################
¤ >
¤ >
¤ > I thing you need to define your output parameter as a REF_CURSOR in your package or stored
¤ > procedure. The following article uses a package:
¤ >
¤ > HOW TO: Return an Oracle Ref Cursor to a .NET DataReader Object by Using the .NET Managed Provider
¤ > for Oracle
¤ > http://support.microsoft.com/default.aspx?scid=kb;en-us;322160&Product=ado
¤ >
¤ >
¤ > Paul ~~~ pclement@ameritech.net
¤ > Microsoft MVP (Visual Basic)
¤ Hi Paul,
¤ I am using Oracle 8.1.7.
¤ Using toad I tried to alter parameter to REF_CURSOR and also REF CURSOR.
¤ Both won't compile. Former says 'identifier REF_CURSOR must be declared'
¤ Latter says identifier CURSOR must be identified even though it colours
¤ REF and CURSOR blue indicating two key words.
¤
¤ The examples that I have seen seem to use predefined types.
¤ eg 'Using .NET FrameWork Data Provider for Oracle to Improve .NET
¤ Application Performance ' by Gregory Leake July 2002.
¤ He is using 'P_Cursor OUT NILES_TYPES.NILE_CURSOR'
¤
¤ What I did was convert my SQL SERVER database to Oracle using the Oracle
¤ Migration Workbench.
¤
¤ I have just chased through the Oracle model generated and Type RCT1 is a
¤ global predefined type of REF CURSOR.
¤ "TYPE RCT1 IS REF CURSOR;/*new weak cursor definition*/"
¤ I then created a new package in the current instance with a type of RCT2
¤ because I thought maybe that global type wasn't in scope somehow for the
¤ dot net call.
¤ But it has made no difference.
¤
¤ I am truly stuffed here. I can't believe it is this difficult to use a
¤ .NET Client to simply retrieve a Datareader.
¤ I have 73 stored procs to get working.
¤ thanks anyway
¤ regards
¤ bob

I would highly recommend using a package and the example in the MS KB article. I'm using the same
version of Oracle as you and it works just fine for me.

Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)

Relevant Pages

  • Re: Error in calling stored procedure via DB link
    ... "Paul Clement" wrote: ... Below is an Oracle KB article that documents the issue. ... Calling Stored Procedure over Dblink Using Oracle OleDb Provider Fails ... > symptom: Using command type adCmdStoredProc ...
    (microsoft.public.vb.database.ado)
  • .NET & Oracle Ref Cursor
    ... I'm creating a .NET service using an Oracle backend. ... stored procedure. ... ref cursor must be declared as part of a package. ...
    (microsoft.public.dotnet.faqs)
  • Re: File or assembly name Oracle.DataAccess not found.
    ... Did you set security for the Oracle dll's as Paul has outlined? ... > Exception Details: System.IO.FileNotFoundException: File or assembly ... Attempting download of new URL ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: SQL Passthrough to Oracle..
    ... and can not handle a ref cursor being returned to VFP... ... DSN to use an Oracle driver and things are functioning better.. ... The package exist in my development schema.. ...
    (microsoft.public.fox.programmer.exchange)
  • Re: Oracle Innobase Purchase Impacts MySQL.
    ... > Paul wrote: ... Oracle is no slouch when it comes to using resources. ... manufacturer's gas guzzling vapourware. ... criticise their opponents for many things, but resource hogging is not ...
    (comp.databases.oracle.server)