Link to home
Start Free TrialLog in
Avatar of chsmit
chsmit

asked on

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
Avatar of appari
appari
Flag of India image

try

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

ASKER

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
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)
ASKER CERTIFIED SOLUTION
Avatar of appari
appari
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of chsmit

ASKER

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;

is it is working?
Avatar of chsmit

ASKER

Works great.  thanks again for the advice.