Solved

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

Posted on 2004-03-30
7
304 Views
Last Modified: 2013-12-25
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
0
Comment
Question by:chsmit
  • 4
  • 3
7 Comments
 
LVL 39

Expert Comment

by:appari
Comment Utility
try

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

Author Comment

by:chsmit
Comment Utility
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
 
LVL 39

Expert Comment

by:appari
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 39

Accepted Solution

by:
appari earned 250 total points
Comment Utility
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
 

Author Comment

by:chsmit
Comment Utility
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
 
LVL 39

Expert Comment

by:appari
Comment Utility

is it is working?
0
 

Author Comment

by:chsmit
Comment Utility
Works great.  thanks again for the advice.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now