VB.NET and ODP.NET How to compile a function

  • Thread starter Thread starter Klaus Klöser
  • Start date Start date
K

Klaus Klöser

Hi,

I am using the Oracle Data Provider ODP.NET which extends the ADO.NET.

What is the best way to create a function within the Oracle Database ?

e.g.

"function test (f_text in varchar2)
return varchar2
is
begin
return f_text||' processed !';
end;"


If I send that string over oracle.dataaccess.oracleCommand I get an error.

simple select statements work fine.

what is the best way to get a funtion (procedure/package) compiled ?

thx

Klaus
 
From a script using SQL PLUS

Technically, you could use

create or replace function test (f_text in varchar2)
return varchar2
is
begin
return f_text||' processed !';
end;
/

as the string and executenonquery

But it is very bad practice to do the ddl directly in the app. Better would
be to shell text files
 
Hi Eric,

this is, waht I don't want to do, in fact I am writing an SQL Editor, which
has a few features I am missing with TOAD, SQL Station, and of course SQL
PLUS

So, writing DDL in the app should be a feature.

I tried to send the string via ExecuteNonQuery but I get an error like
(german version of .NET, so here's the translation)

due to current status of object, this action is not possible

I do not know, which object is meant, nor what the status would be...

regards

Klaus
 
Here is how to do it with the oledb provider. I bet ODP.net is something
similar. I can look later on today



Note: I've wrapped the connection in a class (DBObject) so you'll have to
tweak this



Dim sql As String = "create or replace procedure MYBOGUSPROC (something in
varchar2) as begin dbms_output.put_line('!!!'); end; "

Me.m_Parameters.ConnectionString = "Provider=OraOLEDB.Oracle;data
source=xxx;User ID=xxx;Password=xxx;"

Dim cmd As New System.Data.OleDb.OleDbCommand(sql, DBObject.conn)

cmd.CommandType = CommandType.Text

cmd.Prepare()

Try

cmd.ExecuteNonQuery()

Catch ex As Exception

MsgBox(ex.Message)

End Try
 
Eric said:
Here is how to do it with the oledb provider. I bet ODP.net is something
similar. I can look later on today



Note: I've wrapped the connection in a class (DBObject) so you'll have to
tweak this



Dim sql As String = "create or replace procedure MYBOGUSPROC (something in
varchar2) as begin dbms_output.put_line('!!!'); end; "

Me.m_Parameters.ConnectionString = "Provider=OraOLEDB.Oracle;data
source=xxx;User ID=xxx;Password=xxx;"

Dim cmd As New System.Data.OleDb.OleDbCommand(sql, DBObject.conn)

cmd.CommandType = CommandType.Text

cmd.Prepare()

Try

cmd.ExecuteNonQuery()

Catch ex As Exception

MsgBox(ex.Message)

End Try
Click to expand...

I do not see where you are opening the connection. That is the first place I
would look. My guess is that the error you are seeing is referring to the
fact that the connection object is in a state of Closed.
 
The code example works and for the benefit of the original post... I make
the connection in the DBObject class and raise and exception if it fails
 
Back
Top