Archive for category Oracle

ORA-00918: column ambiguously defined with CASE column

I had an Oracle query that was returning the error “ORA-00918: column ambiguously defined” when I tried to run it:

SELECT * FROM ( 
  SELECT ID, CASE
    WHEN something THEN something else
    ELSE yet more
  END CASE
FROM ScottsTable
UNION ALL
  SELECT ID, CASE 
    WHEN something THEN something else
    ELSE yet more
  END CASE
FROM ScottsTableHistory
) ORDER BY 1 ASC, 2;

The problem was originating in the first query’s CASE statement. I needed to put an alias on the first CASE statement to avoid the naming duplication that was causing Oracle to hate me. Here is the final, modified query:

SELECT * FROM ( 
  SELECT ID, CASE
    WHEN something THEN something else
    ELSE yet more
  END "MY COLUMN"
FROM ScottsTable
UNION ALL
    SELECT ID, CASE 
    WHEN something THEN something else
  ELSE yet more
END CASE
FROM ScottsTableHistory
) ORDER BY 1 ASC, 2;
Advertisements

Leave a comment

Entity Framework error: ‘The underlying provider failed on Open’

I was using the Oracle.ManagedDataAccess DLL to reference an Oracle database  using Entity Framework. However, attempting this was returning the error “The underlying provider failed on Open” when my object list was being populated:

List<COASTER> coasters = context.COASTER.Where(c => c.PARK== "Cedar Point").ToList();

The issue came down to my connection string format. When trying to use the normal format that we’ve come to know and love, I’d encounter the problem. The format I’m talking about is:

metadata=res://*/Models.Model1.csdl|res://*/Models.Model1.ssdl|res://*/Models.Model1.msl;provider=Oracle.ManagedDataAccess.Client;provider connection string='DATA SOURCE=ScottServer;PASSWORD=mypw;USER ID=scott'

However, when I switched to the format that you see in the tnsnames.ora file, I had better results. So good, in fact, that it actually worked:

metadata=res://*/Models.Model1.csdl|res://*/Models.Model1.ssdl|res://*/Models.Model1.msl;provider=Oracle.ManagedDataAccess.Client;provider connection string='Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=my-server)(PORT = 1521))(CONNECT_DATA=(SERVICE_NAME=testdb.www.wunderhund.com)));User Id=scott;Password=mypw;'

Leave a comment

Updating an Oracle LONG column

Want to update an Oracle LONG value? Having problems? Well, you’re in luck. Do update the field, run this bit of code. Yes, it’s not an lovely as a one-line UPDATE statement, but sometimes, you have to do it the ugly way:

DECLARE
 coasterDesc varchar2(5000); 
BEGIN
 coasterDesc := 'Some really, really, REALLY long text goes here.';
 UPDATE coasters SET coaster_description = coasterDesc WHERE coaster_id = '12345'; 
END;

Leave a comment

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