How do I access an Oracle Package from VB.Net and return value to VB

I've been fighting with this for a couple of days.  I have an oracle package on my database that I need to send an input variable to and get back a 3 output vairables using VB.Net.  Does anyone have any advice?  I've tried a number of examples but none of them seem to work.   When I hit the reader it does not return any values but the package runs perfect in SQL PLUS and returns a value.  

Here is my most reason attempt and an outline of the procedure?
**************************
    Sub UseProcedure()
 
        Dim conn As New OracleConnection("data source=<Name>; user id=<Userid>;password=<pass>")
        Dim cmd As New OracleCommand("package1.procedure2", conn)

        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("input_variable", "ddddddd")
        cmd.Parameters(0).Direction = ParameterDirection.Input

        cmd.Parameters.Add("perr", OracleDbType.Int32)
        cmd.Parameters(1).Direction = ParameterDirection.Output

        cmd.Parameters.Add("output_var1", OracleDbType.Varchar2)
        cmd.Parameters(2).Size = 20
        cmd.Parameters(2).Direction = ParameterDirection.Output

        cmd.Parameters.Add("output_var2", OracleDbType.Varchar2)
        cmd.Parameters(3).Size = 20
        cmd.Parameters(3).Direction = ParameterDirection.Output
       
        Dim rdr As OracleDataReader

        Try
            conn.Open()
            rdr = cmd.ExecuteReader()


            Do While rdr.Read()

                Console.WriteLine(vbTab & "{0}" & vbTab & "{1}" & vbTab & "{2}", rdr.GetString(0), rdr.GetString(1), rdr.GetString(2))
            Loop

        Catch e As Exception
            Console.WriteLine(e.Message)
        Finally
            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If
            cmd.Dispose()
            conn.Dispose()
        End Try
    End Sub
************************************

 Here is an outline of what my package looks likes:

CREATE or REPLACE PACKAGE package1 AS
   TYPE kn_cur IS REF CURSOR;

   PROCEDURE procedure1(
      input_var IN varchar,
      perr OUT NUMBER,
      pcur OUT kn_cur);
     
   PROCEDURE procedure2(
      input_var IN varchar,
      perr OUT NUMBER,
      out_var1 OUT varchar,
      out_var2 OUT varchar);

END package1;

CREATE OR REPLACE PACKAGE BODY package1 AS

 PROCEDURE procedure2(
      i_kanji_name IN varchar,
      perr OUT NUMBER,
      out_var1 OUT varchar,
      out_var2 OUT varchar)IS

-- Holding variables for counts returned from SQL

-- A number of different variables here

   BEGIN


LOOP
 
 --- Run a number of query and assign the outputs
     
END LOOP;    

   EXCEPTION
      WHEN OTHERS THEN
         perr:= SQLCODE;

END procedure2;

PROCEDURE procedure1(
      input_var IN varchar,
      perr OUT NUMBER,
      pcur OUT kn_cur) IS

   BEGIN
      perr := 0;
      OPEN pcur FOR
         SELECT * FROM table
         WHERE field_name = field_name;

   EXCEPTION
      WHEN OTHERS THEN
         perr:= SQLCODE;

   END procedure1;
 END package1;

In VB.NET
chsmitAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

appariCommented:
try

Console.WriteLine(vbTab & "{0}" & vbTab & "{1}" & vbTab & "{2}", cmd.Parameters(0).value, cmd.Parameters(1).value, cmd.Parameters(2).value)
0
chsmitAuthor Commented:
I gave it a try but no luck...  It appears that the  "Do While rdr.Read()" statement is returning as "FALSE".  I think I have my parameters defined correctly but I will double check.  I know this works on the Oracle side but it seems that VB is not getting any information back.

Here are the references I am importing:
Imports System
Imports System.Data
Imports Oracle.DataAccess.Client
0
appariCommented:
ok then try execute method like

cmd.Execute()
Console.WriteLine(vbTab & "{0}" & vbTab & "{1}" & vbTab & "{2}", cmd.Parameters(0).value, cmd.Parameters(1).value, cmd.Parameters(2).value)
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

appariCommented:
i think in vb.net there is no execute method, use the equivalent i think ExecuteNonQuery. i dont have vb.net on my PC right now.

cmd.ExecuteNonQuery()
Console.WriteLine(vbTab & "{0}" & vbTab & "{1}" & vbTab & "{2}", cmd.Parameters(0).value, cmd.Parameters(1).value, cmd.Parameters(2).value)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chsmitAuthor Commented:
I am not sure if we are heading the right direction or not but at least there is a different result.  I am now getting an error message from the procedure.  Do I have my parameters setup correctly?  

Here is a copy of the message I am getting and SQL I use to get the correct answer from SQL-Plus.  Any more advice?


*********************************
ORA-06550: line 1, column 15:
PLS-00103: Encountered the symbol "S" when expecting one of the following:

   := . ( @ % ;
The symbol ":=" was substituted for "S" to continue.

*********************************

DECLARE
  PERR NUMBER;
  out_var1 VARCHAR2(200);
  out_var2 VARCHAR2(200);

BEGIN
  PERR := NULL;
  out_var1 := '';
  out_var2 := '';

  OWNER.PACKAGE1.PROCEDURE2 ( 'ddddddd', PERR, out_var1, out_var2 );
END;
0
appariCommented:

is it is working?
0
chsmitAuthor Commented:
Works great.  thanks again for the advice.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.