Archive for category Database

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

ORA-01008: not all variables bound

This was one of those “duh” error. I was attempting to retrieve data from Oracle using some simple ADO.NET code, like the code below, but I was receiving the error “ORA-01008: not all variables bound“.

string value = String.Empty;
using (OracleConnection conn = new OracleConnection(connString)) {
    using (OracleCommand comm = new OracleCommand("SELECT Description FROM ScottsTable WHERE ID = :id", conn)) {
        comm.CommandType = CommandType.StoredProcedure;
        comm.Parameters.Add(":id", OracleDbType.Varchar2, 40).Value = "scott";
        conn.Open();
        value = comm.ExecuteScalar().ToString();
    }
}

The problem was I was setting the OracleCommand’s CommandType property to StoredProcedure when it was actually straight text. Removing this line resolved the error.

 

Leave a comment

“Grant option does not exist for view” error in Oracle

I had an MVC project that was using Entity Framework (or THE Entity Framework, if you prefer, Ohio State fans) against an Oracle database. I was jumping into the project and had just created a new Oracle user to limit access the only the tables I needed instead of using a more powerful account that could do whatever it wanted. So I was granting SELECT access to those tables, and also some views. I hit one particular view that gave me this error:

 ORA-01720: grant option does not exist for 'Parks.Coaster_View'

Trying to grant permissions first to the tables that the view referenced, then granting them to the view, didn’t work either. What I had to do, as explained in this article, was to grant select access to the schema owner of the view to the table itself:

grant select on OtherSchema.TableThatViewReferences to Parks with grant option;

After that, my grant statement on the view worked.

Leave a comment

ORA-12154: TNS:could not resolve the connect identifier specified

I had been happily using my application locally for some time, but it was time to deploy it for user testing, and we know how things go wrong when that happened. After publishing my MVC application, I starting getting this error:

 ORA-12154: TNS:could not resolve the connect identifier specified

Research suggested a connection string problem. In my web.config, I had a connection string similar to this:

 <add name="ConnString" connectionString="DATA SOURCE=ScottDb;PASSWORD=blah;USER ID=scott" />

What I needed to do was configure it to look like an entry in the tnsnames.ora file, like so:

 <add name="ConnString" connectionString="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Some-Host)(PORT=6000))(CONNECT_DATA=(SERVICE_NAME=scottprod.scott.com)));User Id=scott;Password=blah;" />

If your not sure where your tnsnames.ora file is located, you can try using the tnsping command to locate it. When I ran the command “tnsping ScottDb”, the file location and connection string was returned, so that may help you.

Leave a comment