Oracle woes

by Matthew Noonan 6. March 2006 02:58

Now, I admit up front I'm not an Oracle guy. I've spent the majority of my career working with SQL Server, but I have had occasion to work with other databases like Sybase, DB2 and of course Oracle. To me, Oracle provides the worst developer experience of all those I mentioned (my completely biased opinion).

I'm not saying Oracle is not a great database, I just think they could go a long way to helping developers do their jobs. Maybe it's just a Microsoft/Oracle thing I keep running into. Maybe not. Error messages like "Invalid number or type of parameters" don't really narrow it down for me when the stored procedure has 30 parameters. Which is it, number or type? And which one? It seems to me Oracle should be able to narrow it down for me a bit further than they do.

Anyway, ranting aside, here's my problem. Oracle also does not have a feature that directly mimics SQL Server's IDENTITY column, where if you insert a row into a table with an IDENTITY column, SQL Server will automatically generate the column value for you (this is most frequently used as a primary key for a table with no natural key).

As a workaround, EasyObjects uses the Oracle SEQUENCE objects to generate the next number in the sequence. This approach works great in the Oracle stored procedures, but is proving to be quite the challenge when generating inline SQL commands. After several iterations and confusing error messages, this is where I'm at with it and it still does not execute successfully:

DECLARE AK_CUSTOMERID NUMBER;
BEGIN
    SELECT MNOONAN.CUSTOMERSEQ.NEXTVAL INTO AK_CUSTOMERID FROM DUAL;
    INSERT INTO MNOONAN.CUSTOMERS (CUSTOMERID, CITY) VALUES (AK_CUSTOMERID, :CITY);
END;

This results in the informative "ORA-01036 Invalid variable name" error. I am left to guess that the problem is with the :CITY variable, since it's the only one. If there is a solution to this, it has avoided me so far.

Any Oracle gurus out there that can provide some insight?

Tags:

General

Submit to DotNetKicks...
Permalink | Comments (4)

Comments

3/7/2006 2:00:00 AM #

Thanks, Dave. I have visited that page before, but the solution was not forthcoming. It turns out that Mike Griffin had already solved this problem, and he was kind enough to share the fix.

BEGIN
    SELECT MNOONAN.CUSTOMERSEQ.NEXTVAL INTO :CUSTOMERID FROM DUAL;
    INSERT INTO MNOONAN.CUSTOMERS (CUSTOMERID, CITY) VALUES (:CUSTOMERID, :CITY);
END;

That's the correct PL/SQL, but the key to the solution is that the :CUSTOMERID parameter must be defined in EasyObjects as InputOutput. In SQL Server it would only be defined as Output, which is the way I had it.

Thanks, Mike!

mgnoonan United States

3/7/2006 2:00:00 AM #

Hi Mat, I don't know how you are generating your dynamic sql, but maybe this link could help ?

www.oracle.com/.../o55odpnet.html

Keep up the good work

Dave

DaveyjClark United States

3/18/2006 2:00:00 AM #

An alternative to the query is to use the returning clause.  I personally prefix my parameters with "P_" to distinguish between parameter and field names.  

BEGIN
INSERT INTO MNOONAN.CUSTOMERS (CUSTOMERID, CITY) VALUES (MNOONAN.CUSTOMERSEQ.NEXTVAL , P_CITY) RETURNING CUSTOMERID INTO P_CUSTOMERID;
END;

In this case, you can declare P_CUSTOMERID as OUT and P_CITY as IN.
Let me know if this is what you were looking for.

Thanks,

Julio

Julio Izquierdo United States

3/18/2006 2:00:00 AM #

Interesting, Julio. I will have to try that out. Thanks!

mgnoonan United States

Comments are closed