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.pr ocedure2", conn)
cmd.CommandType = CommandType.StoredProcedur e
cmd.Parameters.Add("input_ variable", "ddddddd")
cmd.Parameters(0).Directio n = ParameterDirection.Input
cmd.Parameters.Add("perr", OracleDbType.Int32)
cmd.Parameters(1).Directio n = ParameterDirection.Output
cmd.Parameters.Add("output _var1", OracleDbType.Varchar2)
cmd.Parameters(2).Size = 20
cmd.Parameters(2).Directio n = ParameterDirection.Output
cmd.Parameters.Add("output _var2", OracleDbType.Varchar2)
cmd.Parameters(3).Size = 20
cmd.Parameters(3).Directio n = 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.Messag e)
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
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.pr
cmd.CommandType = CommandType.StoredProcedur
cmd.Parameters.Add("input_
cmd.Parameters(0).Directio
cmd.Parameters.Add("perr",
cmd.Parameters(1).Directio
cmd.Parameters.Add("output
cmd.Parameters(2).Size = 20
cmd.Parameters(2).Directio
cmd.Parameters.Add("output
cmd.Parameters(3).Size = 20
cmd.Parameters(3).Directio
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.Messag
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
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
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)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;
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?
ASKER
Works great. thanks again for the advice.
Console.WriteLine(vbTab & "{0}" & vbTab & "{1}" & vbTab & "{2}", cmd.Parameters(0).value, cmd.Parameters(1).value, cmd.Parameters(2).value)