Question Making Oracle SPs act like Sql Server SPs?

VentureFree

Well-known member
Joined
Jan 9, 2008
Messages
54
Programming Experience
5-10
I had been under the impression that Stored Procedures were essentially queries stored in the database for easier consumption and alteration without having to change consumer code. Clearly I was mistaken, since Oracle apparently doesn't use SPs in that way at all.

In particular, coming from Sql Server, I'm used to writing something like the following:
VB.NET:
CREATE PROCEDURE GetEmployeeData
@EmployeeID int
AS
BEGIN
    SELECT * FROM Employees WHERE EmpID = @EmployeeID
END
This lets me define a regular select query on the server so that any changes to the query require changing the stored procedure, and I don't have to track down every bit of code that would need to use that actual query.

Clearly this will not work in Oracle. In fact, I'm not sure how to actually do something similar in Oracle. Looking around on the internet, I find a lot of references to cursors, but I'm not really sure how to incorporate that into my existing code. Most of my code (VB.net) treats data retrieval as though it's using a regular select query, but of course it's just calling that SP.

So how do I define an Oracle SP (or possibly a function if necessary) in such a way that calling it appears the same as running an actual query, more like a Sql Server SP? Or do I have to change my code base to specifically handle Oracle SPs (i.e. handle cursors)?
 
Apparently this isn't possible. Everything in my code uses Odbc, so being forced to use Oracle.DataAccess.Client kind of sucks, but at least it works. In the end I decided to go with something like the following:
VB.NET:
--Disclaimer: the following code is untested.
--I wrote it in a few minutes just for demonstration and so it might not work, 
--but should give you an idea of what I was trying to do
CREATE TABLE "DOOHICKEYS"
  (
    "NAME" VARCHAR2(7 BYTE),
    "DESCRIPTION"   VARCHAR2(25 BYTE)
  )

--This is the stored procedure that I created in oracle
create or replace
PROCEDURE EXAMPLE_GETDOOHICKEYBYNAME (DOOHICKEYNAME IN VARCHAR2, io_cursor OUT sys_refcursor) AS
v_cursor sys_refcursor;

begin
    open v_cursor for
    select * FROM DOOHICKEYS
    where DOOHICKEYS.NAME = DOOHICKEYNAME;

    io_cursor := v_cursor;
end;
VB.NET:
Imports Oracle.DataAccess.Client

Public Function GetDoohickey() As Doohickey
    Dim NewDoohickey As Doohickey
    Dim ConnectionString As String = ConfigurationManager.ConnectionStrings("OracleConnectionString")
    Dim connString As New String(ConnectionString)
    Dim ds As New DataSet()
    Using conn As New OracleConnection(connString)
        Dim cmd As New OracleCommand()

        cmd.Connection = conn
        cmd.CommandText = "EXAMPLE_GETDOOHICKEYBYNAME"
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add(New OracleParameter("DOOHICKEYNAME", OracleDbType.Varchar2, 7, NetLogin, ParameterDirection.Input))
        cmd.Parameters.Add(New OracleParameter("io_cursor", OracleDbType.RefCursor)).Direction = ParameterDirection.Output

        Dim da As New OracleDataAdapter(cmd)
        da.Fill(ds)
        
        If (ds.Tables("Table").Rows.Count > 0) Then
            Dim dr As DataRow = ds.Tables("Table").Rows(0)
            Doohickey = New Doohickey()
            Doohickey.Name = dr("NAME").ToString()
            Doohickey.Description = dr("DESCRIPTION").ToString()
        Else
            NewDoohickey = Nothing
        EndIf
    End Using
    
    Return NewDoohickey
End Sub
 
In oracle, procedures that return values are called FUNCTIONS

VB.NET:
CREATE FUNCTION myFunc(inputs) AS sys_refcursor
  tmp_cur SYS_REFCURSOR;
BEGIN
  OPEN tmp_cur FOR SELECT * FROM DUAL;
  RETURN tmp_cur
END;

Allowing you to declare multiple cursors as OUT parameters in a PROCEDURE (stored db logic that DOES NOT RETURN A VALUE) or FUNCTION will lalow you to open and return multiple cursors/result sets

SQL Server makes things "easy" but that does not necessarily make it "good". All SQLS is doing is implicitly creating a cursor and returning it from the proc. In oracle you have to explicitly do that for functions. Remember that functions may not execute data modifying statements

Also remembe rthat sprocs are not really for simply wrapping queries up so you can change them without changing the front end. Thats what viewsare for, but in any case, a change to the structure of a the data returned by a query requires a change to the front end anyway. Sprocs are for putting business logic into te DB
 
Back
Top