Help with stored procedures

From: BJones8888 <bjones8888_at_aol.com>
Date: 28 Aug 1999 15:27:12 GMT
Message-ID: <19990828112712.28206.00000442@ng-xa1.aol.com>

Help! I'm a developer out onsite for a new client's installation of our software. To date we have only supported SQL Server and Sybase SQL Anywhere, but now we're implementing support for Oracle as our back end. I have 6 working stored procedures for SQL Anywhere, but need the syntax for Oracle. Any assistance you can provide will be greatly appreciated, since this has to be working by Monday morning. (I'm in a hotel for the weekend, with Oracle Personal Edition on my laptop - and am assured by Oracle that there is no incompatibility between them -- though I've already seen some.) This message is rather long. I apologize. If anyone would like to have a crack at any one or two of them, that would be a great help.

Actually, the SQL is quite simple. The problem I have is that I don't know the syntax for the stored procedures as Oracle requires. I took the first one and changed it, mostly groping in the dark, but I got it to compile. I haven't actually been able to test it yet, but I will later.

There are six procedures that we use. Simple ones, as I mentioned, but important.

PROC_AUTOID. The purpose of this one is to take the value found in the AUTOID table's AUTO_ID field, increment it, save that incremented number, and return the new number. Here's the code from SQLAnywhere:

alter procedure PROC_AUTOID(@AUTOID_VALUE integer output) as declare @AUTO integer
begin transaction
select @AUTO=AUTO_ID from AUTOID holdlock update AUTOID set AUTO_ID=@AUTO+1
select @AUTOID_VALUE=AUTO_ID from AUTOID commit transaction
return

When I tried to do this in Oracle, it didn't seem to like the idea of a variable parameter (which we signify in SQLAnywhere by putting the '@' in front of the parameter name), so what I did with Oracle was try making it a Function - to facilitate the returning of a value. Here's what I came up with (it compiles ok):

FUNCTION PROC_AUTOID RETURN INTEGER IS
    AUTO INTEGER;
BEGIN
  set transaction use rollback segment AUTOID;   select AUTO_ID INTO AUTO from AUTOID holdlock;   update AUTOID set AUTO_ID=AUTO+1;
  select AUTO_ID INTO AUTO from AUTOID;   commit;
END; 2. PROC_CHECKLOCK This one checks to see if there is a record in the LOCKS table which contains the tablename, username and recordno which are passed in. If there are no records in LOCKS which match the tablename, username and recordno, it returns false.

alter procedure PROC_CHECKLOCK(@TBLINNAME char(32),@RNO

	integer,@TBLOUTNAME char(32) output,@USROUTNAME char(10) 
	output,@BSTAT integer output) as 
select @TBLOUTNAME=TABLENAME, @USROUTNAME=USERID, 	@BSTAT=RECNO from LOCKS

	where TABLENAME=@TBLINNAME and RECNO=@RNO
if @@ROWCOUNT=0 begin 
	select @BSTAT=0

end
return

3. PROC_DELMEMO Rather than implement large strings or BLOBs in our tables, we take memo fields and store them in a MEMOS table, in chunks of 254 characters. When we delete a record in a table, and that table has a memo associated with it, we must also delete the pertinent records from the MEMOS table. Here's the simple SQLAnywhere code for it:

alter procedure PROC_DELMEMO(@TableName char(24),@Name

char(24),@RecNo integer)
begin
delete from Memos where TableName=@TableName and "Name"=@Name and

RecNo=@RecNo
end

4. PROC_GETMEMO Here's the flipside of the other. Here we're returning a set of records from MEMOS. There are 3 parameters passed in: TABLENAME, NAME, & RECNO. TABLENAME is the name of the associated table for which this memo is stored. NAME is the field in the associated table. (Since 'Name' is a reserved word in SQLAnywhere, it had to be in quotes.) And RECNO is the unique record identifier from the associated table. The task is to find all the notes records from MEMOS and put them in order - hence the SEQUENCE field in MEMOS. Here's the SQLAnywhere code.

alter procedure PROC_GETMEMO(in @TableName char(24),in @Name

	char(24),in @RecNo integer)
	result(Notes varchar(254)) begin
select Notes from Memos where TableName=@TableName and "Name"=@Name
	and RecNo=@RecNo order by Sequence asc

end

5. PROC_LOCK The purpose of this procedure is to enter a record into the LOCKS table for a given tablename, username, and recordno, if there isn't already one there, returning true if successful.

alter procedure PROC_LOCK(@TBLINNAME char(32), @USRINNAME

	char(10), @RNO integer, @TBLOUTNAME char(32) output, @USROUTNAME 
	char(10) output, @BSTAT integer output) as 
select @TBLOUTNAME=TABLENAME, @USROUTNAME=USERID, 
	@BSTAT=RECNO from LOCKS 
	where TABLENAME=@TBLINNAME and RECNO=@RNO
if @@ROWCOUNT=0 begin 
	insert into LOCKS(TABLENAME,USERID,RECNO) 
		values(@TBLINNAME,@USRINNAME,@RNO)
	select @BSTAT=0

end
return

6. PROC_UNLOCK This one simply removes the record from the LOCKS table matching the specified tablename and recordno. Since we're only dealing with locks on the record level rather than field, we can ignore the fieldname in this procedure. We remove all locks based on the tablename and recordno.

alter procedure PROC_UNLOCK(@TBLINNAME char(32),@RNO integer) as

delete from LOCKS where TABLENAME=@TBLINNAME and RECNO=@RNO return

Thanks for your help!!!!!!!

Bob Jones
Omni Developments, Inc. --- (Custom Software Development) Received on Sat Aug 28 1999 - 10:27:12 CDT