Solved

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

Posted on 2004-03-30
7
308 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
ID: 10712329
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
ID: 10720562
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
ID: 10720837
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 Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 39

Accepted Solution

by:
appari earned 250 total points
ID: 10720846
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
ID: 10721297
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
ID: 10721595

is it is working?
0
 

Author Comment

by:chsmit
ID: 10754502
Works great.  thanks again for the advice.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

791 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