Hi

I'm hoping one of you guys can help me out with something.

I am on a project using Oracle and have NO oracle experience .. so please forgive me if I sound a bit MS SQL ..

I have a stored proc to either add a new record or update an existing record ..

the code is below


I can call this code fine from TOAD so it seems the PL/SQL is ok .. BUT I can't get it running when called from an ASP app I am working on ..

the snippet below is where its called

each time I try this I am getting the following OLEDB Return

I am truly stumped unfortunately .. I am presuming its some sort of implicit cast being made somewhere .. but dont know tbh ,,

any pearls of wisdom are most welcome

cheers
Antony

I don't know ASP very well, so can't say much about your error - except that I would expect it to be the value of prm_id being passed in that is causing the problem (since it's the only NUMBER parameter).

Since you are new to Oracle, though, I will give you some feedback on your code from an Oracle perspective:

1) Rather than select COUNT(*) to see if record already exists, it would be more efficient to just do the UPDATE, then if SQL%ROWCOUNT = 0 (i.e. no rows updated), do an INSERT.
(Alternatively, if you think most calls will be INSERTs, do the INSERT first then if it fails due to PK violation, do the UPDATE).

2) This is a really BAD idea (in Oracle at least):
/*get a the next available id value*/
SELECT MAX(MPS_ID) + 1 INTO prm_id
FROM MAP_SITES;

Why? It will only work properly on a single-user application. If 2 users call the procedure at the same time, they will get the same ID and one of them will fail with a PK violation.
You could lock the table, but then you are serialising inserts: if 2 users call the procedure, user 2 will hang until user 1 commits.
You should use a SEQUENCE:
CREATE SEQUENCE prm_id_seq;
then user prm_id_seq.NEXTVAL to get a new number
(Note that this will not guarantee gap-free numbering, but who needs that?)

3) Trivial point: prefix stored procedures with "SP_" is very SQL-Server-ish, and not usual in Oracle. A procedure name should generally be of the form VERB_NOUN such as RECORD_MAPSITE_STATE.

4) More important point: you should ALWAYS use packages rather than stand-alone stored procedures. Group related procedures together into packages. This makes the job of managing user privileges much easier.

I have taken all the above points and modified your code as an example, below. I hope you don't mind me making these comments!

CREATE OR REPLACE PACKAGE mapsite_pkg IS
PROCEDURE RECORD_MAPSITE_STATE
(
prm_id IN OUT NUMERIC,
prm_name IN VARCHAR2,
prm_folderpath IN VARCHAR2,
prm_author IN VARCHAR2,
prm_lastpublishedby IN VARCHAR2,
prm_version IN VARCHAR2

);
-- Other MAPSITE procedure specifications here
END mapsite_pkg;
/

CREATE OR REPLACE PACKAGE BODY mapsite_pkg IS
PROCEDURE RECORD_MAPSITE_STATE
(
prm_id IN OUT NUMERIC,
prm_name IN VARCHAR2,
prm_folderpath IN VARCHAR2,
prm_author IN VARCHAR2,
prm_lastpublishedby IN VARCHAR2,
prm_version IN VARCHAR2

)
IS
/ **************************************************
*********
Name: RECORD_MAPSITE_STATE
Auhtor: Antony Gorman
Date: 10th Jan 2003
Desc: If the mapsite doesnt currently exist - add a new one
- else update the current one

History:
**************************************************
*********/

BEGIN
UPDATE MAP_SITES
SET MPS_NAME = prm_name,
MPS_FOLDERPATH = prm_folderpath,
MPS_AUTHOR = prm_author,
MPS_LASTUPDATED = SYSDATE,
MPS_LASTPUBLISHEDBY = prm_lastpublishedby,
MPS_VERSION = prm_version
WHERE MPS_ID = prm_id;

IF SQL%ROWCOUNT = 0 THEN --need to add a new mapsite record

INSERT INTO MAP_SITES
( MPS_ID,
MPS_NAME,
MPS_FOLDERPATH,
MPS_AUTHOR,
MPS_LASTUPDATED,
MPS_LASTPUBLISHEDBY,
MPS_VERSION
)
VALUES ( mps_id_seq.NEXTVAL,
prm_name,
prm_folderpath,
prm_author,
SYSDATE,
prm_lastpublishedby,
prm_version
);
END IF;
END;
-- Other MAPSITE procedure bodies here
END mapsite_pkg;
/

(BTW, if someone could explain to me how to preserve indentation when posting code examples, I'd be much obliged!)