Oracle Virgin



  • Despite what I've heard about abstinence being the best policy, I have to use Oracle for this project I'm working on.  Here are the things that I need to do:

    1. Connect to an Oracle database from VB .NET (using System.Data.OracleClient from the .NET 2.0 framework, if that matters)
    2. Call a function on the particular database I'm connecting to and get the a boolean result from it
    3. Eventually deploy this (web) application to an environment run by an operations group that likes non-Microsoft products like puritans like uninhibited fornication

    Because of #3, I've decided to go with the InstantClient, as it's free and I figure it'll be easier to get the operations group to throw some .dll files in a folder than to get them to install some Oracle client software.  What I really like about the InstantClient is all the documentation it comes with.  When I opened the .zip file containing a bunch of .dll files and no instructions, I was like, "Whoa, shut up!  What do you think I am, an idiot?!  Quit holding my hand!"

    What I've gathered from my various googlings is that I need to throw the contents of the InstantClient .zip file into a folder, add that folder's path to my PATH environment variable, create a TNSNAMES.ora file in InstantClient\network\admin, and then point a newly created TNS_ADMIN environment variable to that folder.

    In my TNSNAMES.ora file, I have the following:

    TEST =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = TheServer)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SID = TheDatabase)
        )
      )

    In my web.config file, I have the following in the connectionStrings section:

    <add name="OracleConnectionString" connectionString="Data Source=TEST;User Id=Me;Password=HaYoureNotGettingMyPassword;Integrated Security=no;"/>


    Unfortunately, when I try to connect using that connection string, I get the fun-fun "ORA-12154: TNS:could not resolve the connect identifier specified" exception.  So I figure the InstantClient can't find my TNSNAMES.ora file.  That's fine.  Replacing "TEST" in the connection string above with the contents of my TNSNAMES.ora file seemed to do the trick:

    <add name="OracleConnectionString" connectionString="Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=TheServer)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=TheDatabase)));User Id=Me;Password=HaYoureNotGettingMyPassword;Integrated Security=no;"/>

    I'd rather not do that though, because it probably means that I've got something screwed up if the InstantClient can't find my TNSNAMES.ora file.  Any ideas where I'm going wrong in that regard?

    Either way, now that I can connect, I'm not quite sure how to go about actually calling a function in an Oracle database.  I've seen plenty of examples of calling an Oracle SP from .NET code, but not a function.  Any advice there would be appreciated as well.

    Complicating matters is the fact that I have very little information about the Oracle database to which I'm trying to connect;  Hell, I don't even know what version of Oracle it is.  I'm trying to get as much info from the owners as I can, but they all seem terribly busy.

    I would greatly appreciate any assistance you Oracle veterans could give me.



  • If it's anything like SQL Server, then you should be able to just SELECT it like any built-in function:

    SELECT my_function(parameters...);

    Then you'll get the value as a single-column record. Here's a thread I spotted with a bit of info:

    http://www.orafaq.com/forum/?t=msg&th=8916/0/ 

    It looks like you don't have to bother with the owner/schema qualification like you would in SQL Server (dbo.my_function or what have you). I haven't played with using parameterized queries when the SqlCommand.CommandType is Text, but I vaguely remember it being pretty simple. Or there's always the string concatenation approach, I suppose. ;-)



  • [quote user="db2"]

    If it's anything like SQL Server, then you should be able to just SELECT it like any built-in function:

    SELECT my_function(parameters...);

    Then you'll get the value as a single-column record. Here's a thread I spotted with a bit of info:

    http://www.orafaq.com/forum/?t=msg&th=8916/0/ 

    It looks like you don't have to bother with the owner/schema qualification like you would in SQL Server (dbo.my_function or what have you). I haven't played with using parameterized queries when the SqlCommand.CommandType is Text, but I vaguely remember it being pretty simple. Or there's always the string concatenation approach, I suppose. ;-)

    [/quote]

    Here's what I'm doing, and the error(s) I am getting:

                Dim OraConnString As String = ConnectionStrings("OracleConnectionString").ConnectionString
                Dim OraConn As New OracleConnection(OraConnString)

                Try
                    Dim OraComm As New OracleCommand()
                    Dim Result As String = ""

                    OraComm.CommandType = CommandType.Text
                    OraComm.CommandText = "SELECT Blah.TheFucntion(:Param1, :Param2, :Param3, :Param4) FROM DUAL"
                    OraComm.Connection = OraConn

                    OraComm.Parameters.Add(New OracleParameter("Param1", OracleType.VarChar))
                    OraComm.Parameters("Param1").Value = Something
                    OraComm.Parameters.Add(New OracleParameter("Param2", OracleType.VarChar))
                    OraComm.Parameters("Param2").Value = SomethingElse
                    OraComm.Parameters.Add(New OracleParameter("Param3", OracleType.DateTime))
                    OraComm.Parameters("Param3").Value = AnotherThing
                    OraComm.Parameters.Add(New OracleParameter("Param4", OracleType.VarChar))
                    OraComm.Parameters("Param3").Value = TheLastThing

                    OraConn.Open()
                    Result = OraComm.ExecuteScalar  'Not surprisingly, the error happens here

                    Return True
                Catch ex As OracleException
                    Return False
                Finally
                    If Not OraConn Is Nothing Then
                        If OraConn.State = ConnectionState.Open Then
                            OraConn.Close()
                        End If
                    End If
                End Try

    The exception  

    ORA-06552: PL/SQL: Statement ignored
    ORA-06553: PLS-382: expression is of wrong type

    Googling those errors, I found this page: http://www.delphi32.com/info_facts/faq/faq_877.asp which seems to indicate that perhaps the function is return a type incompatible with a SELECT statement.  Is this correct, or is there something else going on here?

     



  • What version of Oracle are you using?  It is my understanding that up to 9i, there was no native boolean column type in Oracle so you would always see int(1) and char(1) fields with 0/1 or Y/N emulating boolean values.  Therefore, if the function does indeed output a boolean value, that could be the issue since it doesn't know how to convert it to a native SQL data type.

     



  • I'm not entirely certain what version I'm using, but since I'm having the problem you're describing, I'm guessing it's 8i or below.  Getting (correct) information from the group who owns this database isn't as easy as it should be.

    Ok, so:

    SELECT MyBooleanFunction('Blah') FROM DUAL;

    won't work because of the aforementioned reason.  Any ideas on how I can get the value returned from this MyBooleanFunction into my VB.Net code?

    I've tried a SELECT CASE, but that doesn't work.

    I tried:

    DECLARE Result BOOLEAN;

    BEGIN

        Result := MyBooleanFunction('Blah');

        IF Result
        THEN
           SELECT 1 FROM DUAL;
        ELSE
           SELECT 0 FROM DUAL;
    END;

    (I'm not sure if the syntax is exactly correct up there, as I am at home now and don't have Toad in front of me...and yes, I'm aware that it's a bit WTF-Y)

    but Toad complained about my SELECT statements not having an INTO clause.  "But Toad," I whined, "I don't *want* to SELECT anything into another variable, I want to output a 1 or a 0...like, you know, as a result set."

    I know I'm a helpless Oracle noob at this point, but I appreciate any help you all can give.  Lest you think I'm slacking off, please know that I'll be reading my wife's Guide to Oracle book tonight in the hope of making sense of this strange new RDBMS world.  But it'd be great if you all could tell me something that'd save me hours of reading.



  • Ok, I've got it working, but I almost too ashamed of what I've done to post it here.  Nonetheless, I post it here in the hopes of learning precisely how wrong and how stupid it is, and why.  Without further ado:

    Dim OraConnString As String = ConnectionStrings("OracleConnectionString").ConnectionString
    Dim OraConn As New OracleConnection(OraConnString)
    Dim OraComm As New OracleCommand()
    Dim OraReader As OracleDataReader

    Try
        OraComm.CommandType = CommandType.Text

        'Set up the PL/SQL
        OraComm.CommandText = ""  'Look below for the PL/SQL I stuck here, in a non-concatenated format that'll hopefully be easier on the eyes.

        OraComm.Connection = OraConn
        OraConn.Open()

        'Set up OUT cursor parameter and other parameters
        OraComm.Parameters.Add(New OracleParameter("ADamnCursor", OracleType.Cursor, 2000, ParameterDirection.Output, True, 0, 0, "", DataRowVersion.Default, DBNull.Value))

        OraComm.Parameters.Add(New OracleParameter("Something", OracleType.VarChar))
        OraComm.Parameters("LastName").Value = Something                       

        OraComm.Parameters.Add(New OracleParameter("SomethingElse", OracleType.VarChar))
        OraComm.Parameters("FirstName").Value = SomethingElse                       

        OraComm.Parameters.Add(New OracleParameter("ADate", OracleType.DateTime))
        OraComm.Parameters("ClassDate").Value = ADate

        OraComm.Parameters.Add(New OracleParameter("AnotherDate", OracleType.DateTime))
        OraComm.Parameters("BirthDate").Value = AnotherDate

        'execute that orable mess!
        OraReader = OraComm.ExecuteReader

        'read the single damn value returned from the Oracle Function.

     

    *PL/SQL for the CommandText of the command above

    --------------------------------------- 

    DECLARE

        TYPE MyDamnCursorType IS REF CURSOR;

        PROCEDURE GetFunctionResult (MyDamnOUTCursor OUT MyDamnCursorType)
        IS
        BEGIN
            IF Blah.TheFunctionIBeenWantinToCall(:Something, :SomethingElse, :ADate, :AnotherDate) "
            THEN
                OPEN MyDamnOUTCursor FOR SELECT 1 FROM DUAL;
            ELSE
                OPEN MyDamnOUTCursor FOR SELECT 0 FROM DUAL;
            END IF;
        END;

    BEGIN
        GetFunctionResult(:ADamnCursor);
    END;

     

    Is there some other way I could have called a boolean PL/SQL function from .NET?  I'm hoping so, because the above seems to be a humongous WTF to me.



  • To my knowledge, that's the only way around it with that version of Oracle and the .Net/ODP drivers.  You can clean up the SQL wrapper though as I don't think you need a ref cursor.  I'd replace it with something similar to the following and see if that works.

    BEGIN :return_val := case when stupid_func(:blah, :yada, :someMore, :evenMore) then 1 else 0 end; end;

    That should be enough to do what you need. 

     



  • [quote user="lpope187"]

    To my knowledge, that's the only way around it with that version of Oracle and the .Net/ODP drivers.  You can clean up the SQL wrapper though as I don't think you need a ref cursor.  I'd replace it with something similar to the following and see if that works.

    BEGIN :return_val := case when stupid_func(:blah, :yada, :someMore, :evenMore) then 1 else 0 end; end;

    That should be enough to do what you need.

    [/quote]

    I tried that but it didn't seem to like it.  So I put:

    DECLARE
           return_val NUMBER;
    BEGIN
           return_val := case when stupid_func(:blah, :yada, :someMore, :evenMore) then 1 else 0 end;
    END;

    into Toad and it didn't like "case" being on the right side of the ":=".

    I ended up using:

    'Set up the PL/SQL
    OraComm.CommandText = "BEGIN IF stupid_func(:blah, :yada, :someMore, :evenMore) THEN :ReturnValue := 1; ELSE :ReturnValue := 0; END IF; END;"

    'Set up ReturnValue parameter and other parameters
    OraComm.Parameters.Add(New OracleParameter("ReturnValue", OracleType.Number, 1, ParameterDirection.ReturnValue, True, 0, 0, "", DataRowVersion.Default, DBNull.Value))

    'blah blah blah and so on and so forth

    That's pretty much like what you suggested, using an IF instead of a CASE.

    The way I had it before, this report was taking about a minute to complete.  With this/your way, it takes around 20 seconds.  So, quite an improvement.  Thanks!

     

    Now, do you know of any sort of whiz-bang feature of Oracle/.NET/SQL Server 2000 that'd make this even faster?  Let me explain what I'm doing using some pseudo-code:

    ----------------------------------

    Get all the Enrollees of a Class from the SQL Server 2000 database

    For each Enrollee, call the function in Oracle, passing it info about the Enrollee, to see if that Enrollee is certified to do X

    Output a report indicating which Enrollees are certified to do X and which aren't.

    ----------------------------------

    The slowness is coming in when I have to call the function in the Oracle database once for each and every Enrollee I get from my SQL Server 2000 database.  I'm opening the connection to the Oracle database once in the beginning, calling the function for each Enrollee, and then closing the connection, because I assume that's better than opening and closing the connection to the Oracle database once for each Enrollee.  I'd thought about creating a big ol' block of PL/SQL that would call the Oracle function for each enrollee in a loop and then executing that PL/SQL block once on the Oracle database, but that seems like it would be a huge opening for some kind of SQL injection attack (because I'd be concatenating a bunch of information to make the PL/SQL block).

    Any ideas?



  • I'm glad that I helped you out with the function.  I wasn't actually looking for a performance gain just clearer code but that's a nice bonus.

    As far as your other question, that is a better approach.  Are you using connection pooling?  That might help performance out a little.  Here's an article about ODP.Net that might help you tweak performance. 

    http://www.oracle.com/technology/oramag/oracle/06-jul/o46odp.html 

    The only other option I can think of is as you suggest, use dynamic sql and execute the statement in one block.  In this case I'd use an IN statement if you can like the following

    SELECT EnrolleeID, MyFunction(:EnrolleeID) as Certified FROM Enrollees WHERE EnrolleeID IN (1,2, ... X)

    You do run the risk of SQL injection, but if you loop over a SQL Dataset (specifically a numeric ID column) to build the dynamic SQL, the risk should be minimal.  Without knowing the exact nature of the function and what systems store what data, it's kind of hard to tell if the above approach is applicable to your environment.
     



  • [quote user="UncleMidriff"]

    <add name="OracleConnectionString" connectionString="Data Source=TEST;User Id=Me;Password=HaYoureNotGettingMyPassword;Integrated Security=no;"/>

    Unfortunately, when I try to connect using that connection string, I get the fun-fun "ORA-12154: TNS:could not resolve the connect identifier specified" exception.  So I figure the InstantClient can't find my TNSNAMES.ora file.  That's fine.  Replacing "TEST" in the connection string above with the contents of my TNSNAMES.ora file seemed to do the trick:

    [/quote]

    Try TEST.world or TEST.local instead of TEST. Chances are one of those will work. 



  • [quote user="ammoQ"][quote user="UncleMidriff"]

    <add name="OracleConnectionString" connectionString="Data Source=TEST;User Id=Me;Password=HaYoureNotGettingMyPassword;Integrated Security=no;"/>

    Unfortunately, when I try to connect using that connection string, I get the fun-fun "ORA-12154: TNS:could not resolve the connect identifier specified" exception.  So I figure the InstantClient can't find my TNSNAMES.ora file.  That's fine.  Replacing "TEST" in the connection string above with the contents of my TNSNAMES.ora file seemed to do the trick:

    [/quote]

    Try TEST.world or TEST.local instead of TEST. Chances are one of those will work. 

    [/quote]

    Actually, whereever you are setting this project up (local debug, then your machine) you need to setup the project to set the environement variable TNS_ADMIN before loading or make sure that the TNS_ADMIN is set in the system default env. vars...then it should work as you attempted before.

    Second...if you need to see the version of DB you are in, and you said you are using Toad:
    SELECT * FROM v$version;

    i.e.:

    SYS:TEST> select * from v$version;

    BANNER
    ----------------------------------------------------------------
    Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
    PL/SQL Release 9.2.0.4.0 - Production
    CORE    9.2.0.3.0       Production
    TNS for Solaris: Version 9.2.0.4.0 - Production
    NLSRTL Version 9.2.0.4.0 - Production

    Elapsed: 00:00:00.01

     



  • Thanks to everyone for their help. As it turns out, this part of the project just got "LOW PRIORITY'D!" so all my fussing around seems to be for naught, at least for the time being.  *sigh*

     

    @ammo:  Thanks for the suggestion, but I found out what my problem was.  I had the TNS_ADMIN environment variable set every-so-subtly incorrectly.  I swear, I checked it eleventy-billion times, against several different examples I'd found on Google, but I still managed to get it wrong.

     @everyone else:  Thanks for the info and assistance.  I'm sure it will come in useful to me at some point in the future.
     


Log in to reply