Oracle stored procedures

[vbjustin]

Hi there, I have been working on a program that talks to an oracle database, Oracle Express 10g right now, and I was wondering exactly how I could use a stored procedure, using the ODP.NET package, to send two variables to it, a num and str. This procedure squares the number and tells you the length of the string, all stored within a table called "test".

If you guys could help me out, that would be great, I've been at this thing for way too long, and have just gotten nowhere on this part of the program.

thanks in advance, and if you need me to show you what I have so far, just ask and I'll post that too.

vbjustin!

 

[fpineda101]

SQL Server examples will work for Oracle. You only have to change the property to reflect which class you are using ie. OledbClient, OracleClient.

SQLCommand would be oledbcommand or OracleCommand, SQLConnection would be OledbConnection or OracleConnection

Dim sqlINS As SqlCommand
Dim sconn As New SqlConnection
sconn = New SqlConnection("Server=FROZEN;Database=master;User ID=sa;Password=today;Trusted_Connection=False")
sconn.Open()

sqlINS = New SqlCommand("INSERT_CREATIVE_BRIEF2", sconn)
---------------- Name of the stored procedure ^ ------------------------
sqlINS.Connection = sconn
sqlINS.CommandType = CommandType.StoredProcedure

<add your parameters here>

sqlINS.Parameters.Add(New SqlParameter("@ProjectName", SqlDbType.NVarChar, 50))
sqlINS.Parameters("@ProjectName").Value = txtProjectName.Text
sqlINS.Parameters.Add(New SqlParameter("@YourName", SqlDbType.NVarChar, 50))
sqlINS.Parameters("@YourName").Value = txtYourName.Text

<execute the command here>

sqlINS.ExecuteNonQuery()
sconn.Close()

 

[fpineda101]

You should always post what you have done so far

 

[vbjustin]

Here is my code, I have just one more question, how do I ask for a value to come back, because im squaring the number, and also getting the length of the string? Do I have to create a new comand to call for those valuse using the "select * from ..."

Also, does the type of the string have to match up with the type within the table for it to work right

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] i [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Button1_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] Button1.Click[/SIZE]
[SIZE=2][COLOR=#008000]'for connecting to Josh's server:[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] oradb [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = "Data Source=(DESCRIPTION=(ADDRESS_LIST=" _[/SIZE]
[SIZE=2]+ "(ADDRESS=(PROTOCOL=TCP)(HOST=...)(PORT=1521)))" _[/SIZE]
[SIZE=2]+ "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)));" _[/SIZE]
[SIZE=2]+ "User Id=vb;Password=...;"[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] oOracleConn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OracleConnection = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OracleConnection[/SIZE]
[SIZE=2][COLOR=#008000]'(GATEWAY=rsgis-summer2.erd.ds.usace.army.mil)[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] some_str [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] some_num [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Array[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] sys_time [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DateTime[/SIZE]
[SIZE=2]some_str = TextBox1.Text[/SIZE]
[SIZE=2]TextBox3.Text = sys_time[/SIZE]
[SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
[SIZE=2]oOracleConn.ConnectionString = oradb[/SIZE]
[SIZE=2]MsgBox("1.Connecting...")[/SIZE]
[SIZE=2]i = 0[/SIZE]
[SIZE=2]Timer1.Enabled = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
[SIZE=2]oOracleConn.Open()[/SIZE]
[SIZE=2]Timer1.Enabled = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
[SIZE=2]Label2.Text = i[/SIZE]
[SIZE=2]MsgBox("1.Connected!")[/SIZE]
[SIZE=2]MsgBox("connect for data...")[/SIZE]
[SIZE=2]i = 0[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cmd [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OracleCommand("ruby", oOracleConn)[/SIZE]
[SIZE=2]Timer1.Enabled = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'cmd.Connection = oOracleConn[/COLOR][/SIZE]
[SIZE=2]Timer1.Enabled = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
[SIZE=2]Label2.Text = i[/SIZE]
[SIZE=2]MsgBox("connection!")[/SIZE]
[SIZE=2]MsgBox("2.Getting command...")[/SIZE]
[SIZE=2]i = 0[/SIZE]
[SIZE=2]Timer1.Enabled = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'cmd.CommandText = "Select num from test" & "INSERT INTO test VALUES(@num, @str);"[/COLOR][/SIZE]
[SIZE=2]cmd.CommandType = CommandType.StoredProcedure[/SIZE]
[SIZE=2][COLOR=#008000]'cmd.CommandText = "EXECUTE :=ruby(@num,@str);"[/COLOR][/SIZE]
[SIZE=2]cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OracleParameter("@num", OracleDbType.Double, ParameterDirection.Input))[/SIZE]
[SIZE=2]cmd.Parameters("@num").Value = 32[/SIZE]
[SIZE=2]cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OracleParameter("@str", OracleDbType.NChar, 20))[/SIZE]
[SIZE=2]cmd.Parameters("@str").Value = "justin"[/SIZE]
[SIZE=2]cmd.ExecuteNonQuery()[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Ds [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet[/SIZE]
[SIZE=2]Timer1.Enabled = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
[SIZE=2]Label2.Text = i[/SIZE]
[SIZE=2]MsgBox("2.Got Command!")[/SIZE]
[SIZE=2][COLOR=#008000]'MsgBox("collect command...")[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'MsgBox("Collected!")[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dr [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OracleDataAdapter(cmd)[/SIZE]
[SIZE=2]MsgBox("3.Reciving command...")[/SIZE]
[SIZE=2]i = 0[/SIZE]
[SIZE=2]Timer1.Enabled = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
[SIZE=2]MsgBox("Executing procedure...")[/SIZE]
[SIZE=2]dr.Fill(Ds)[/SIZE]
[SIZE=2]MsgBox("Procedure executed")[/SIZE]
[SIZE=2][COLOR=#008000]'dr.Read()[/COLOR][/SIZE]
[SIZE=2]Timer1.Enabled = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
[SIZE=2]Label2.Text = i[/SIZE]
[SIZE=2]MsgBox("3.Recived Command!")[/SIZE]
[SIZE=2][COLOR=#008000]'MsgBox("outputing data...")[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'Label1.Text = dr.Item(0) ' or dr.Item(0)[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]' MsgBox("done!")[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OracleException [/SIZE][SIZE=2][COLOR=#008000]' catches only Oracle errors[/COLOR][/SIZE]
[SIZE=2]Timer1.Enabled = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
[SIZE=2]Label2.Text = "Error!"[/SIZE]
[SIZE=2][COLOR=#0000ff]Select[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][SIZE=2] ex.ToString[/SIZE]
[SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][SIZE=2] 1[/SIZE]
[SIZE=2]MsgBox("Error attempting to insert duplicate data.")[/SIZE]
[SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][SIZE=2] 12545[/SIZE]
[SIZE=2]MsgBox("The database is unavailable.")[/SIZE]
[SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Else[/COLOR][/SIZE]
[SIZE=2]MsgBox("Database error: " + ex.Message.ToString())[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Select[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] de [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataException[/SIZE]
[SIZE=2]MsgBox(de.Message.ToString())[/SIZE]
[SIZE=2][COLOR=#0000ff]Finally[/COLOR][/SIZE]
[SIZE=2]MsgBox("4.Disconnecting...")[/SIZE]
[SIZE=2]i = 0[/SIZE]
[SIZE=2]Timer1.Enabled = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
[SIZE=2]oOracleConn.Dispose()[/SIZE]
[SIZE=2]Timer1.Enabled = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
[SIZE=2]Label2.Text = i[/SIZE]
[SIZE=2]MsgBox("4.Disconnected!")[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Timer1_Tick([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] Timer1.Tick[/SIZE]
[SIZE=2]i = i + 1[/SIZE]
[SIZE=2]Label2.Text = i[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Button2_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] Button2.Click[/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Close()[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE]

 

[vbjustin]

I have found out that OracleDBType.Number does not exist, Why is this so? It makes it extremly difficult to have a number inserted into a table.

also, can anyone tell me how to commit a stored procedure, I have it running, but the data doesn't show up in the table, it adds to the row count though for some reason.

Help would be much aprecieated, thanks, Justin.

 

[cjard]

points of note:

use

 tags when posting code. info here: [URL]http://www.vbdotnetforums.com/misc.php?do=bbcode[/URL]
 
you dont need to use the ODP.NET provider. if you do, you have to distribute some additional DLLs with your app. If you use the System.Data.OracleClient hierarchy then it iwll use microsoft drivers which are bundled with .net FW
 
all the oracle stored procedure code i've ever written does not use the parameter specifier (it's a colon, not an @ symbol for oracle):
 
the command text is simply the name of the procedure:
[FONT=Courier New]CommandText = "owner.procName"[/FONT]
[FONT=Courier New][/FONT] 
you do not supply the names of the arguments in the commandtext. The names are precompiled and stored on the db. When you create the parameters, you simply use the same name for the parameter as exists in the database.
 
you do not put the colons in as the parameters names:
[SIZE=2][FONT=Courier New].Parameters.Add([/FONT][/SIZE][FONT=Courier New][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OracleParameter([/SIZE][SIZE=2][COLOR=#800000]"arg1"[/COLOR][/SIZE][SIZE=2], OracleType.Number ...
[/SIZE][/FONT] 
Maybe OracleDBType is an ODP.NET thing, but in the MS drivers for oracle we have OracleType.OracleNumber  and it does exist. Ensure you have added a reference to System.Data.OracleClient then press F2 and type OracleType for more information
 
stored procedures, in my experience, autocommit when called from client code. You may have specified a connection parameter that disabled this behaviour?

 

[cjard]

here is some code from a .net 1.1 project that calls an oracle procedure called InsertCreditOpinion, it inserts varchar and clob types, and returns a number (for further insertion of clob data, as the clob size is limited per call to 32K):

            command = New OracleCommand
            command.Connection = Me.Connection
            command.Transaction = Me.Transaction
            command.CommandText = cPackageName & ".InsertCreditOpinion"
            command.CommandType = CommandType.StoredProcedure

            command.Parameters.Add("customerId", OracleType.VarChar).Value = opinion.CustomerId
            command.Parameters.Add("productId", OracleType.VarChar).Value = opinion.ProductId

            If opinion.Data.Length < chunkSize Then
                chunk = opinion.Data
                index = 0
            Else
                chunk = opinion.Data.Substring(0, chunkSize)
                index = chunkSize
            End If

            command.Parameters.Add("creditData", OracleType.Clob).Value = chunk
            command.Parameters.Add("creditOpinionId", OracleType.Number).Direction = ParameterDirection.Output

            'insert the record and write the first chunk
            command.ExecuteNonQuery()

 

[vbjustin]

That helps a bit, but I still have the issue of it not going into the table, I can't seem to figure out if its Autocommiting or not. Heres my code again:

[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Button1_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] Button1.Click[/SIZE][SIZE=2][COLOR=#008000]
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] oradb [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = "Data Source=(DESCRIPTION=(ADDRESS_LIST=" _
+ "(ADDRESS=(PROTOCOL=TCP)(HOST=IPAdress)(PORT=1521)))" _
+ "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)));" _
+ "User Id=UID;Password=PASS;"
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] oradb2 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = "User Id=vbjustin;Password=mypassword2;"
[/SIZE][SIZE=2][COLOR=#008000]'for connecting to my server:
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strConn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = "Data Source=(DESCRIPTION=(ADDRESS_LIST=" _
+ "(ADDRESS=(PROTOCOL=TCP)(HOST=IPAddress)(PORT=1521)))" _
+ "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)));" _
+ "User Id=vbjustin;Password=mypassword2;"
[/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] oOracleConn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OracleConnection = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OracleConnection
[/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]oOracleConn.ConnectionString = oradb
MsgBox("1.Connecting...")
oOracleConn.Open()
MsgBox("1.Connected!")
MsgBox("connect for data...")
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cmd [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OracleCommand("vb", oOracleConn)
MsgBox("connection!")
[/SIZE][SIZE=2][COLOR=#008000]'Dim sqlTransaction As OracleTransaction = oOracleConn.BeginTransaction()
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]'sqlTransaction = oOracleConn.BeginTransaction
[/COLOR][/SIZE][SIZE=2]MsgBox("2.Getting command...")
[/SIZE][SIZE=2][COLOR=#008000]'cmd.CommandText = "vb.vb"
[/COLOR][/SIZE][SIZE=2]cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OracleParameter(":NumSq", OracleDbType.Decimal, ParameterDirection.Output))
cmd.Parameters(":NumSq").Value = 0
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OracleParameter(":LenStr", OracleDbType.Decimal, ParameterDirection.Output))
cmd.Parameters(":LenStr").Value = 0
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OracleParameter(":num", OracleDbType.Decimal, ParameterDirection.Input))
cmd.Parameters(":num").Value = TextBox2.Text
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OracleParameter(":str", OracleDbType.Varchar2, ParameterDirection.Input))
cmd.Parameters(":str").Value = TextBox1.Text
cmd.ExecuteNonQuery()
[/SIZE][SIZE=2][COLOR=#008000]'sqlTransaction.Commit()
[/COLOR][/SIZE][SIZE=2]MsgBox("2.Got Command!")
MsgBox("Procedure Success!")
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OracleException [/SIZE][SIZE=2][COLOR=#008000]' catches only Oracle errors
[/COLOR][/SIZE][SIZE=2]Label2.Text = "Error!"
[/SIZE][SIZE=2][COLOR=#0000ff]Select[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][SIZE=2] ex.ToString
[/SIZE][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][SIZE=2] 1
MsgBox("Error attempting to insert duplicate data.")
[/SIZE][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][SIZE=2] 12545
MsgBox("The database is unavailable.")
[/SIZE][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Else
[/COLOR][/SIZE][SIZE=2]MsgBox("Database error: " + ex.Message.ToString())
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Select
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Finally
[/COLOR][/SIZE][SIZE=2]MsgBox("4.Disconnecting...")
oOracleConn.Close()
MsgBox("4.Disconnected!")
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]

[/COLOR][/SIZE]

 

[fpineda101]

Try renaming your stored procedure to something else other than "vb".

 

[cjard]

you use + for string concatenation in vb.net? interesting..

all that junk in your conenction string; howcome you didnt put that in your tnsnames.ora ?


i dont have much further in terms of advice, because youre sticking with ODP.net to link to oracle and I've never used it.. If you try your code with the MS driver for oracle, i'll be able to advise more on the client side of things..

On the server side, have you actually issued a COMMIT in the stored procedure pl sql?

 

[vbjustin]

i dont have much further in terms of advice, because youre sticking with ODP.net to link to oracle and I've never used it.. If you try your code with the MS driver for oracle, i'll be able to advise more on the client side of things..

On the server side, have you actually issued a COMMIT in the stored procedure pl sql?

One, I am kinda stuck using ODP.NET, Not my choice to make. And secondly yes the procedure works, my buddy has done PL/SQL statements to test it, and on that side it works perfectly fine, its on the client side that is going bonkers, or sending mixed signals. I don't have the tns file because im accesing a database though a lan connection.

Is there some reason why my data disapears before it actually gets into the table? Thats the only idea I come up with.
vbjustin

 

[cjard]

One, I am kinda stuck using ODP.NET, Not my choice to make.

I cant quite see how? The MS drivers are present in every .NET framework in the world. All you need to do to use them is write code that.. well, uses them!

And secondly yes the procedure works, my buddy has done PL/SQL statements to test it, and on that side it works perfectly fine,

he has used a query tool to ascertain that the procedure inserts values into the database?

its on the client side that is going bonkers, or sending mixed signals. I don't have the tns file because im accesing a database though a lan connection.

slightly more confusing; any computer that wishes to access an oracle database needs to have the oracle client installed. by default this is in c:\oracle\client
it is in this folder that you will find (amongst other things) the tnsnames.ora file.. for me it's C:\oracle\product\10.2.0\client\network\ADMIN\tnsnames.ora

Is there some reason why my data disapears before it actually gets into the table? Thats the only idea I come up with.
vbjustin

you say that you have run tests that indicate your code is calling the sproc, and it is returning 1 row being inserted, but you then cannot find the row that was inserted.. ?

 

[vbjustin]

Ok, Ive been thinking, and I think that I need to look it at a different perspective. The larger picture is that It isn't getting commited, VB.NET in this case does not commit by itself, therefore I need to find the code in which to make that happen.

This has come to my attention because of the information I have looked up, the websites I have read, and a couple of people who really do know what they are talking about, cjard and fpineda101 included.

This still leaves me a bit stuck since so far I haven't found much about this topic, which makes me wonder why... I can't be the only person that has this issue.

If anyone has any suggestions, it would be greatly apreciated.

Thanks for your consideration,
vbJustin

 

[fpineda101]

Have you tried issuing the command from the client using SQL*Plus or something similiar?

Are the ODP drivers the most up to date?

Check out this link...
http://download-east.oracle.com/docs/html/B28089_01/extenRest.htm

I saw this on there, very interesting...
Unsupported SQL Commandshttp://vbdotnetforums.com/http://vbdotnetforums.com/http://vbdotnetforums.com/

Transaction controls commands such as COMMIT, ROLLBACK, and SAVEPOINT are not supported in a .NET stored procedure.
Data definition commands such as CREATE and ALTER are not supported with an implicit database connection, but they are supported with an explicit user connection in a .NET stored procedure.

Your problem is strange because ODP works on my machine. I have Oracle 9i on my PC, not using a DB over a lan.

Do you have to use the TNSNAMES.ora entry in your connection string? I would suggest using a regular connection string

Don't know what more I can do to help

 

[vbjustin]

I have used the procedure within the SQL command prompt, and it works fine, also works when you use oci8 in Ruby from a remote connection. But For some reason when I use VB.NET it only leaves lots of hyphens, where there should be data.

the stored procedure is on the Oracle side, not the .NET side, so the article doen't help much, unless it goes both ways.

Is there a possible reason that VB.NET is deleting the data once its finished, and creates the hyphens in the row to show that there should be data within the table?

Justin.