stored proc /w Oracle and Ado.Net

[coltrane]

Can someone help me with a very simple task

I am trying to execute a stored procedure in an Oracle database using
Ado.Net. The following is a code snippet


conn = New OracleConnection(mOracleConnectionString)
cmd = New OracleCommand
cmd.Connection = conn
conn.Open()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "TW_USER_TEST3"
cmd.ExecuteReader(CommandBehavior.Default)

The stored proc does not have any input or output parameters. I created
it for testing purposes. I had a stored proc that did have input and
output but I was getting errors so I thought to create the simplest
store proc. The proc doesn't even do anything.

I am getting the following error:
An unhandled exception of type
'System.Data.OracleClient.OracleException' occurred in
system.data.oracleclient.dll

Additional information: ORA-06550: line 1, column 7:
PLS-00201: identifier 'BRAVO_CL21.TW_USER_TEST3' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
The following is the stored proc:
CREATE OR REPLACE procedure tw_users_test3 as

begin

null;

end;
/

I would appreciate any help

thank you

John

 

[rbutch]

as i understand it, the problem isnt the Visual Studio side but, the oracle side.
you cant just call a simple select, you have to declare a reference cursor. i ran into this a long time ago and it took quite a bit of research to find this.
here's one of the websites that has the explantion.

http://www.angrycoder.com/article.aspx?cid=5&y=2003&m=5&d=1

as well as here.

http://support.microsoft.com/default.aspx?scid=kb;en-us;322160&Product=NETFrame


here's part of the package that i used.

PROCEDURE get_user_orgs (
i_lgon_id IN ats_adm.org_security.lgon_id%TYPE,
o_cursor OUT t_cursor)
IS
BEGIN
OPEN o_cursor
FOR
SELECT org_lvl_1, org_lvl_2, org_lvl_3, org_lvl_4
FROM org_security
WHERE lgon_id = i_lgon_id;
EXCEPTION
WHEN OTHERS THEN
RETURN;
END get_user_orgs;

AND THEN THE CALL FROM VISUAL STUDIO.


Private Sub get_user_orgs()
aUser = txtUser.Text
cboOrgs.Items.Clear()
DsSecurity1.Clear()
DsSecurity1.EnforceConstraints = False
Try
daSecurity.SelectCommand.CommandText = "pkg_pay_field_discrepancies.get_user_orgs"
daSecurity.SelectCommand.CommandType = CommandType.StoredProcedure
daSecurity.SelectCommand.Parameters.Add(New OracleParameter("i_lgon_id", OracleClient.OracleType.VarChar)).Value = aUser
daSecurity.SelectCommand.Parameters.Add(New OracleParameter("o_cursor", OracleClient.OracleType.Cursor)).Direction = ParameterDirection.Output
daSecurity.Fill(DsSecurity1)
If DsSecurity1.ORG_SECURITY.Count = 0 Then
lblMessage.Text = "eTA User Not Found"
Else
Dim i As Integer
Dim info As String
For i = 0 To (DsSecurity1.ORG_SECURITY.Count - 1)
info = (DsSecurity1.ORG_SECURITY(i).ORG_LVL_1 & " " & DsSecurity1.ORG_SECURITY(i).ORG_LVL_2 & " " _
& DsSecurity1.ORG_SECURITY(i).ORG_LVL_3 & " " & DsSecurity1.ORG_SECURITY(i).ORG_LVL_4)
cboOrgs.Items.Add(info)
Next i
End If
Catch ex As Exception
lblMessage.Text = ex.Message
End Try
daSecurity.SelectCommand.Parameters.Clear()

End Sub

of course i assume that you've already loaded the oracle reference.

Imports System.data.oracleclient

it's just darn different using oracle and if most businesses didnt demand that you use sp's or tiers, i'd rather just use an ad hoc sql query right from vs. but, real life is different.
hopes this helps you. i had to find this the hard way.
thanks
rik

**********************************************************************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

 

[Eric]

You should create your procedures and functions inside packages to
improve organization, but this isn't absolutely needed.

Are you familiar with schemas, synonyms, and grants? You need to pay
attention to which schema will hold the object (normally determined by
how you logged in when you created the procedure), and you may have to
create a synonym so other schemas will see it. A schema in Oracle is
similar to a namespace, but it is used to control ownership,
permissions, and visibility.

You also have to grant execute permission to the right users (or public).

Please don't test with empty procedures .. put some simple code in there
so you can see if it works.

Do some google searches for "OracleConnection" and
"CommandType.StoredProcedure".

This isn't a bad reference:
http://www.oracle.com/technology/books/pdfs/dotnet_ch5_7.pdf

Eric

 

[Eric]

as i understand it, the problem isnt the Visual Studio side but, the oracle side.
you cant just call a simple select, you have to declare a reference cursor.

He wasn't trying to return a set of rows, so he didn't need a ref cursor.

If you only want to so a SELECT then you don't need a stored procedure,
and you don't need a ref cursor. You can do it just like SQL Server with
a text command type that has the SQL SELECT statement.

Eric

 

[coltrane]

thanks for the help and the reference

 

[coltrane]

thank you for the help