Solved

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

Posted on 2004-03-30
7
309 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Adding to a VBA? 6 82
VB 6 error 5 in windows 10 but not in XP 7 67
Set email body to html using vbscript 6 70
transition to visual .net from vb6 5 57
Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

680 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