Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Function Question

Function Question

From: Ken Rachynski <krachyn_at_evansconsoles.com>
Date: 2000/07/17
Message-ID: <8F747E03Ckrachynec@204.50.1.43>#1/1

Good day,

I'm having difficulty trying to figure out how to create a function that inserts a record and returns a record id to the caller. This function at least compiles:

create or replace function addcontact (afirstname in varchar2, alastname in varchar2) return integer is
  Result integer;
begin
  insert into contact ("Contact ID", "First Name", "Last Name", "Modified Date", "Modified By")
  values (seq_contact_key.nextval, afirstname, alastname, sysdate, user)   returning "Contact ID" into Result;   

  return(Result);
end addcontact;

if I try to call it using the following, I get error "ORA-14551: cannot perform a DML operation inside a query":

select addcontact('Binky', 'Betsy')
  from dual;

Okay, fine. Am I calling this wrong or do I need to do something different.

My main goal is to have the server add the record and return the new sequence number to the caller.

Thanks in advance.

-- 
Ken Rachynski
Database Analyst
EVANS
http://evansonline.com/
Received on Mon Jul 17 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US