Archive for category Oracle

Passing a list of numbers into an Oracle procedure

I wanted to pass in a list of numbers to an Oracle procedure for use in an “IN” statement, called from C# code. First, the C# code:

  string list = "1,2,3";
  using (OracleCommand comm= new OracleCommand("MyProcedure", connection)) {
     comm.CommandType = CommandType.StoredProcedure;
     comm.Parameters.Add(":pList", OracleDbType.Varchar2, 50).Value = list;
     comm.Parameters.Add(":p_cursor", OracleDbType.RefCursor).Direction = ParameterDirection.Output;

My Oracle procedure was equally awesome:

    PROCEDURE MyProcedure(pList IN VARCHAR2, p_cursor OUT sys_refcursor) IS
        
    BEGIN
        OPEN p_cursor FOR
            SELECT name 
            FROM coasters
            WHERE id IN (SELECT TO_NUMBER(column_value) AS IDs FROM XMLTABLE(pList));
    END;

You can see the “FROM XMLTABLE” statement is doing all the good stuff of splitting the list into the appropriate format. Good times.

Leave a comment

Call Oracle stored procedure from Toad

I wanted to test an oracle stored procedure using Toad – I wanted to set the variables in SQL, call the SP, and show the results. Here is what I came up with. Note that the “pTotal” variable is declared as an “OUT” variable in the SP (pTotal OUT NUMBER):

DECLARE
pCoaster VARCHAR2(25) := ‘Mine Ride’;
pTotal NUMBER := 0;
BEGIN
coaster_package.GetCoasterRideCounts(pCoaster, pTotal);
dbms_output.put_line(pTotal);
END;

Leave a comment

Empty string returned from long Oracle text field

I was returning data from an Oracle procedure using an OracleDataAdapter. I noticed that one text field that can hold large values was returning an empty string. So in the following, the “description” value was empty, when I knew the “ParkDescription” field in the database had a value:

string description = ds.Tables[0].Rows[0]["ParkDescription"].ToString();

Running the query in Toad, I could see the data was there. To resolve this, I needed to set the InitialLONGFetchSize  propety of the data adapter’s SelectCommand object to -1:

OracleDataAdapter adp = new OracleDataAdapter(comm);
adp.SelectCommand.InitialLONGFetchSize = -1;

Leave a comment

Using a LIKE query in an Oracle procedure

I had two parameters that could be passed to a procedure. Only one was being passed into the procedure – business layer code makes sure of that – so if the pId parameter is present AND it is a match return the record. But I also needed to return the results if the pName parameter was filled out, and do a LIKE query on that field. And since half the reason for this blog is so when I forget how to do it, I can find it again:

PROCEDURE Search(pName IN VARCHAR, pId IN NUMBER, p_cursor OUT sys_refcursor) IS

   BEGIN
       OPEN p_cursor FOR
           SELECT ID, Name
           FROM users 
           WHERE ((pId IS NOT NULL) AND (ID = pId))
           OR ((pName IS NOT NULL) AND (Name LIKE '%' || UPPER(pName) || '%'));
END;

Leave a comment

Calling an Oracle function from .NET

At some point in every man’s life, he asks himself “How do I call an Oracle function from C# code?” Well, time to answer that question.

I have an Oracle package, and in the package header, I have declared my function:

FUNCTION ValidateId(pId IN VARCHAR) RETURN VARCHAR;

In the package body, I have my function implementation:

    FUNCTION ValidateId(pIdIN VARCHAR) RETURN VARCHAR IS
    
        pMessage VARCHAR(100);
        mId VARCHAR(10);
    
    BEGIN
        pMessage := 'OK';
        
        SELECT COUNT(*)
        INTO mId
        FROM MyTable
        WHERE id = pId;
        
        IF mId = 0 THEN
            pMessage := 'The ID supplied is not valid';
        END IF;

        RETURN pMessage;
    END;

It’s a simple function that verifies that the ID in question exists in the database, and returns a status based on that.

In my C# code, I use some fun and exciting ADO.NET code to call said function:

     public static string ValidateId(string id) {

         string message = String.Empty;

         using (OracleConnection conn = new OracleConnection(connString)) {
             using (OracleCommand comm = new OracleCommand("pplant.cool_package.ValidateId", conn)) {
                 comm.CommandType = CommandType.StoredProcedure;
                 comm.Parameters.Add(new OracleParameter() {
                   Direction = ParameterDirection.ReturnValue, OracleDbType = OracleDbType.Varchar2,
                     Size = 150
                 });
                 comm.Parameters.Add(":pId", OracleDbType.Varchar2, 10).Value = id;

                 conn.Open();
                 comm.ExecuteNonQuery();
                 message = comm.Parameters[0].Value.ToString();
             }
         }

         return message;
     }

A couple of things to note about returning a value from the function. The return value is declared as the first parameter, and has a direction of “ReturnValue”. Also notice that the return parameter does not have a name.

Leave a comment

Getting the current sequence value in Oracle

Who wants to know how to get the current sequence value in Oracle? Yes, I can hear the multitudes across the Internet saying “Me me me!”

SELECT last_number
FROM all_sequences
WHERE sequence_name = 'MY_SEQUENCE';

Leave a comment

Matching a password in the Oracle database

I was writing a utility to unlock a user account when the user knew their password. The unlocking part was easy (assuming the ID your are connecting as has permissions to unlock an account):

ALTER USER Scott ACCOUNT UNLOCK;

My problem was trying to match the password the user entered with the one that is stored in the database – I only wanted to unlock a password if the user was really the user (i.e. knew their user ID and knew their password). But I found a fantastic script at http://www.petefinnigan.com/testpwd.sql that did just what I needed.

Once I ran the script to load the function, I just needed to call the function like so:

 select testpwd('Scott', 'bla') from dual;

This will return “Y” for the right password and “N” for an incorrect password. Super slick.

Leave a comment