On a VB.NET project I have switched the db provider from the MS OLEDB for Oracle to the ODP.net and I'm getting an error executing a non query command for a stored procedure when I try to pass the param value as part of the commandtext.
I know I can make it work using cmd.parameters but on my project I don't want to parse the commandtext that historically have been passed to me with params embedded on it, and I just executed.
Is this possible? Can I keep the call of the stored procedure with params on a single string to be passed as commandtext.
Thanks.
gg
---------------------------------------------
Here is a sample VB.NET Method:
Private Sub Method()
Dim conn As New OracleConnection("Server=X;Uid=X;Pwd=X")
con.Open()
Dim cmd As OracleCommand = con.CreateCommand()
Dim myParam As String = "100"
cmd.CommandText = "test_pkg.my_func(" & myParam & ")"
cmd.CommandType = CommandType.StoredProcedure
cmd.ExecuteNonQuery()
cmd.ExecuteNonQuery()
conn.Close()
End Sub
--- The error I receive ------------------------
ORA-06550: line 1, column 7:
PLS-00801: internal error [22503]
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
--- My Stored Procedure ------------------
create or replace package test_pkg
as
myfuncparam number(10,0);
procedure my_func(myparam in number);
end;
CREATE OR REPLACE package body test_pkg
as
procedure my_func(myparam in number) is
begin
myfuncparam := myparam;
end;
end test_pkg;